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+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.
