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.
from sqlalchemy import create_engine, MetaData, Sequence, Integer, Column
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('postgresql://localhost/drop_sequence', echo=True)
metadata = MetaData(bind=engine)
Base = declarative_base(metadata=metadata)
id_sequence = Sequence('drop_seq_col')
class Station(Base):
__tablename__ = 'drop_seq_test'
col = Column(Integer, id_sequence, unique=True, nullable=False, server_default=id_sequence.next_value())
source_id = Column(Integer, primary_key=True)
metadata.create_all()
metadata.drop_all()
