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).
Le vendredi 11 mars 2016 21:06:07 UTC+1, Mike Bayer a écrit :
I've created
https://bitbucket.org/zzzeek/sqlalchemy/issues/3676/defaults-sequences-assigned-to-both-python
<https://bitbucket.org/zzzeek/sqlalchemy/issues/3676/defaults-sequences-assigned-to-both-python>
in the hopes I can look at this at some point.
On 03/11/2016 02:44 PM, Jay Camp wrote:
> 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+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@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+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.
