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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
