The second pattern of your mail (server side default) describes the use of
the metadata argument:
my_seq = Sequence('some_seq', metadata=Base.metadata)
It would be great to have that information in the documentation (I spent
too much time on that problem and I hope others won't).
Le vendredi 11 mars 2016 21:06:07 UTC+1, Mike Bayer a écrit :
>
> I've created
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3676/defaults-sequences-assigned-to-both-python
>
> in the hopes I can look at this at some point.
>
>
>
> On 03/11/2016 02:44 PM, Jay Camp wrote:
> > Thanks for the explanation.
> >
> > It makes sense now. To restate what you said this won't work because
> > `id_sequence` isn't attached to the metadata:
> >
> > id_sequence = Sequence('drop_seq_col')
> >
> > col =
> Column(Integer,unique=True,nullable=False,server_default=id_sequence.next_value())
>
>
> >
> >
> > I assume by making the sequence the Python default to `col` I implicitly
> > attached it to the metadata as a convenient side-effect. But having the
> > Python + server_default hits the edge case. But you're right, all I
> > really want is the server_default and attach sequence to metadata.
> >
> >
> > Thanks a bunch!
> >
> >
> > Jay
> >
> >
> >
> >
> > On Thursday, March 10, 2016 at 6:52:12 PM UTC-8, Jay Camp wrote:
> >
> > Postgres: 9.4
> > SQLAlchemy: 1.0.11
> >
> > When a sequence is created against a column, calling
> > `metadata.drop_all()` tries to drop the sequence before dropping the
> > table and fails because the table is still referencing the sequence.
> > Manually dropping the table then dropping the sequence works.
> >
> > The test script is attached. Here is the console output:
> >
> > 2016-03-10 18:44:27,101 INFO sqlalchemy.engine.base.Engine select
> > version()
> > 2016-03-10 18:44:27,101 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,104 INFO sqlalchemy.engine.base.Engine select
> > current_schema()
> > 2016-03-10 18:44:27,104 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,106 INFO sqlalchemy.engine.base.Engine SELECT
> > CAST('test plain returns' AS VARCHAR(60)) AS anon_1
> > 2016-03-10 18:44:27,106 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,109 INFO sqlalchemy.engine.base.Engine SELECT
> > CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
> > 2016-03-10 18:44:27,109 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,110 INFO sqlalchemy.engine.base.Engine show
> > standard_conforming_strings
> > 2016-03-10 18:44:27,110 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,111 INFO sqlalchemy.engine.base.Engine select
> > relname from pg_class c join pg_namespace n on n.oid=c.relnamespace
> > where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
> > 2016-03-10 18:44:27,112 INFO sqlalchemy.engine.base.Engine {'name':
> > u'drop_seq_test'}
> > 2016-03-10 18:44:27,115 INFO sqlalchemy.engine.base.Engine SELECT
> > relname FROM pg_class c join pg_namespace n on n.oid=c.relnamespace
> > where relkind='S' and n.nspname=current_schema() and
> relname=%(name)s
> > 2016-03-10 18:44:27,115 INFO sqlalchemy.engine.base.Engine {'name':
> > u'drop_seq_col'}
> > 2016-03-10 18:44:27,117 INFO sqlalchemy.engine.base.Engine CREATE
> > SEQUENCE drop_seq_col
> > 2016-03-10 18:44:27,117 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,127 INFO sqlalchemy.engine.base.Engine COMMIT
> > 2016-03-10 18:44:27,129 INFO sqlalchemy.engine.base.Engine
> > CREATE TABLE drop_seq_test (
> > col INTEGER DEFAULT nextval('drop_seq_col') NOT NULL,
> > source_id SERIAL NOT NULL,
> > PRIMARY KEY (source_id),
> > UNIQUE (col)
> > )
> >
> >
> > 2016-03-10 18:44:27,129 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,147 INFO sqlalchemy.engine.base.Engine COMMIT
> > 2016-03-10 18:44:27,148 INFO sqlalchemy.engine.base.Engine select
> > relname from pg_class c join pg_namespace n on n.oid=c.relnamespace
> > where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
> > 2016-03-10 18:44:27,148 INFO sqlalchemy.engine.base.Engine {'name':
> > u'drop_seq_test'}
> > 2016-03-10 18:44:27,150 INFO sqlalchemy.engine.base.Engine SELECT
> > relname FROM pg_class c join pg_namespace n on n.oid=c.relnamespace
> > where relkind='S' and n.nspname=current_schema() and
> relname=%(name)s
> > 2016-03-10 18:44:27,150 INFO sqlalchemy.engine.base.Engine {'name':
> > u'drop_seq_col'}
> > 2016-03-10 18:44:27,151 INFO sqlalchemy.engine.base.Engine DROP
> > SEQUENCE drop_seq_col
> > 2016-03-10 18:44:27,151 INFO sqlalchemy.engine.base.Engine {}
> > 2016-03-10 18:44:27,152 INFO sqlalchemy.engine.base.Engine ROLLBACK
> > Traceback (most recent call last):
> > File "drop-sequence.py", line 17, in <module>
> > metadata.drop_all()
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
>
>
> > line 3722, in drop_all
> > tables=tables)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
>
> > line 1856, in _run_visitor
> > conn._run_visitor(visitorcallable, element, **kwargs)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
>
> > line 1481, in _run_visitor
> > **kwargs).traverse_single(element)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
>
>
> > line 121, in traverse_single
> > return meth(obj, **kw)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
>
>
> > line 871, in visit_metadata
> > table, drop_ok=True, _is_metadata_operation=True)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
>
>
> > line 121, in traverse_single
> > return meth(obj, **kw)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
>
>
> > line 916, in visit_table
> > self.traverse_single(column.default)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
>
>
> > line 121, in traverse_single
> > return meth(obj, **kw)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
>
>
> > line 934, in visit_sequence
> > self.connection.execute(DropSequence(sequence))
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
>
> > line 914, in execute
> > return meth(self, multiparams, params)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
>
>
> > line 68, in _execute_on_connection
> > return connection._execute_ddl(self, multiparams, params)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
>
> > line 968, in _execute_ddl
> > compiled
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
>
> > line 1146, in _execute_context
> > context)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
>
> > line 1341, in _handle_dbapi_exception
> > exc_info
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
>
>
> > line 200, in raise_from_cause
> > reraise(type(exception), exception, tb=exc_tb)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>
>
> > line 1139, in _execute_context
> > context)
> > File
> >
> "/Users/jay/.virtualenvs/pinto/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
>
>
> > line 450, in do_execute
> > cursor.execute(statement, parameters)
> > sqlalchemy.exc.InternalError: (psycopg2.InternalError) cannot drop
> > sequence drop_seq_col because other objects depend on it
> > DETAIL: default for table drop_seq_test column col depends on
> > sequence drop_seq_col
> > HINT: Use DROP ... CASCADE to drop the dependent objects too.
> > [SQL: 'DROP SEQUENCE drop_seq_col']
> >
> > Is this a bug?
> >
> > Thanks
> >
> > Jay
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
