Error dropping table with sequence on Postgres

[Jay Camp]

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

[Mike Bayer]

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

[Jay Camp]

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

[Mike Bayer]

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.

[Stéphane Raimbault]

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

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

the docs for the metadata argument are present in the constructor
documentation for Sequence:

http://docs.sqlalchemy.org/en/latest/core/defaults.html?highlight=sequence#sqlalchemy.schema.Sequence.params.metadata

Agree adding it to
http://docs.sqlalchemy.org/en/latest/core/defaults.html#associating-a-sequence-as-the-server-side-default
would be nice (PR's accepted).

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@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

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <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+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.