On 03/10/2016 09:52 PM, 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?
well sort of, more like a bug sandwich. the thing you're doing here
is odd and not expected, and is then causing this DROP you don't want
there, but if you didn't do the thing you're doing, then the DROP
wouldn't happen at all unless you did a different thing.
Basically, here are the two patterns SQLAlchemy expects:
Pattern one. Python side default:
class Station(...):
# ...
col = Column(Integer, Sequence('some_seq'))
Pattern two: server side default:
my_seq = Sequence('some_seq', metadata=Base.metadata)
class Station(...):
# ...
col = Column(Integer, server_default=my_seq)
In pattern one, a DROP is emitted for the Python side default object of
the table first. This is fine because there's no database dependency.
In pattern two, the DROP is not emitted at all within the table, it's
emitted later when all the Sequence objects associated with the MetaData
are dropped. But the Sequence here has to be associated with the
MetaData explicitly otherwise it wouldn't get created or dropped.
Your thing is putting the Sequence as both the python-side and the
server-side default, which is...odd, and sort of not necessary. The
Python side default trumps the server-side default and SQLAlchemy
explicitly runs the sequence in any case. It only helps in the case
that someone runs an INSERT directly on the table without SQLAlchemy.
Having the sequence be just the server side default, you get that as
well and SQLAlchemy doesn't worry about it. E.g. you want to use
pattern two above, there's no need to have the same sequence in both places.
The immediate issue can be resolved if we just move the drop of the
python side default to be after the drop of the table, but we're still
letting this odd use just pass silently and maybe it shouldn't do that.
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
