- marked as major
- changed milestone to 0.6.0
- changed component to engine
- changed title to engine/compiler use of sequence when inserting to table with pk & fk column (Postgresql)
engine/compiler use of sequence when inserting to table with pk & fk column (Postgresql)
Issue #1516
resolved
I'm not sure it should be considered a bug, but I noticed this behavior that I think is rather weird. Consider the following code:
from sqlalchemy import Table, Column, MetaData, Integer,ForeignKey, create_engine
from sqlalchemy.orm import mapper, relation, scoped_session, sessionmaker
meta = MetaData()
table_A = Table('a', meta
, Column('id', Integer, primary_key = True)
)
table_B = Table('b', meta
, Column('a_id', Integer, ForeignKey('a.id'), primary_key = True, nullable = False)
, Column('foo', Integer)
)
class A(object):
pass
class B(object):
pass
mapper(A, table_A)
mapper(B, table_B, properties = {'a' : relation(A, backref = 'bs')})
engine = engine = create_engine('postgres://user:@localhost:5432/test', echo = True)
Session = scoped_session(sessionmaker())
Session.configure(bind = engine)
meta.bind = engine
meta.drop_all()
meta.create_all()
Session.add(B())
Session.flush()
If we execute it, we'll get:
2009-08-25 12:47:21,230 INFO sqlalchemy.engine.base.Engine.0x...406c select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2009-08-25 12:47:21,244 INFO sqlalchemy.engine.base.Engine.0x...406c {'name': 'b'}
2009-08-25 12:47:21,258 INFO sqlalchemy.engine.base.Engine.0x...406c select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2009-08-25 12:47:21,260 INFO sqlalchemy.engine.base.Engine.0x...406c {'name': 'a'}
2009-08-25 12:47:21,267 INFO sqlalchemy.engine.base.Engine.0x...406c
DROP TABLE b
2009-08-25 12:47:21,268 INFO sqlalchemy.engine.base.Engine.0x...406c {}
2009-08-25 12:47:21,280 INFO sqlalchemy.engine.base.Engine.0x...406c COMMIT
2009-08-25 12:47:21,287 INFO sqlalchemy.engine.base.Engine.0x...406c
DROP TABLE a
2009-08-25 12:47:21,289 INFO sqlalchemy.engine.base.Engine.0x...406c {}
2009-08-25 12:47:21,298 INFO sqlalchemy.engine.base.Engine.0x...406c COMMIT
2009-08-25 12:47:21,313 INFO sqlalchemy.engine.base.Engine.0x...406c select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2009-08-25 12:47:21,314 INFO sqlalchemy.engine.base.Engine.0x...406c {'name': 'a'}
2009-08-25 12:47:21,321 INFO sqlalchemy.engine.base.Engine.0x...406c select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2009-08-25 12:47:21,323 INFO sqlalchemy.engine.base.Engine.0x...406c {'name': 'b'}
2009-08-25 12:47:21,330 INFO sqlalchemy.engine.base.Engine.0x...406c
CREATE TABLE a (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)
2009-08-25 12:47:21,331 INFO sqlalchemy.engine.base.Engine.0x...406c {}
2009-08-25 12:47:21,372 INFO sqlalchemy.engine.base.Engine.0x...406c COMMIT
2009-08-25 12:47:21,378 INFO sqlalchemy.engine.base.Engine.0x...406c
CREATE TABLE b (
a_id INTEGER NOT NULL,
foo INTEGER,
PRIMARY KEY (a_id),
FOREIGN KEY(a_id) REFERENCES a (id)
)
2009-08-25 12:47:21,380 INFO sqlalchemy.engine.base.Engine.0x...406c {}
2009-08-25 12:47:21,399 INFO sqlalchemy.engine.base.Engine.0x...406c COMMIT
2009-08-25 12:47:21,418 INFO sqlalchemy.engine.base.Engine.0x...406c BEGIN
2009-08-25 12:47:21,423 INFO sqlalchemy.engine.base.Engine.0x...406c select nextval('"b_a_id_seq"')
2009-08-25 12:47:21,424 INFO sqlalchemy.engine.base.Engine.0x...406c None
2009-08-25 12:47:21,429 INFO sqlalchemy.engine.base.Engine.0x...406c ROLLBACK
Traceback (most recent call last):
File "./test.py", line 31, in <module>
Session.flush()
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/scoping.py", line 121, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 1347, in flush
self._flush(objects)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 1417, in _flush
flush_context.execute()
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 244, in execute
UOWExecutor().execute(self, tasks)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 707, in execute
self.execute_save_steps(trans, task)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 722, in execute_save_steps
self.save_objects(trans, task)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 713, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/mapper.py", line 1352, in _save_obj
c = connection.execute(statement.values(value_params), params)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 824, in execute
return Connection.executors[c](c)(self, object, multiparams, params)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 872, in _execute_clauseelement
parameters=params
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 938, in __create_execution_context
return dialect.execution_ctx_cls(dialect, connection=self, **kwargs)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/default.py", line 171, in __init__
self.__process_defaults()
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/default.py", line 358, in __process_defaults
val = drunner.get_column_default(c)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/databases/postgres.py", line 852, in get_column_default
return self.execute_string(exc.encode(self.dialect.encoding))
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 1861, in execute_string
conn._cursor_execute(self.cursor, stmt, params)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File "/dev/env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "b_a_id_seq" does not exist
LINE 1: select nextval('"b_a_id_seq"')
That's because on the one hand meta.create_all() is not using SERIAL on b.a_id (which is what one would expect - it's fk after all) but on the other hand, session tries to use not existing sequence when adding instance of B which is not assigned to any instance of A.
Now, if somebody will create such database and after that will add sequence for b.a_id manually and execute Session.add(B()), this may lead to troubles, specifically when fk won't be violetad. Consider adding the following code to the example above just after meta.create_all(), but before Session.add(B()):
Session.add(A())
engine.execute('CREATE SEQUENCE b_a_id_seq START 1 owned by b.a_id')
Now the whole script will pass (assuming A instance got id == 1):
...
2009-08-25 13:07:48,590 INFO sqlalchemy.engine.base.Engine.0x...506c CREATE SEQUENCE b_a_id_seq START 1 owned by b.a_id
2009-08-25 13:07:48,592 INFO sqlalchemy.engine.base.Engine.0x...506c {}
2009-08-25 13:07:48,600 INFO sqlalchemy.engine.base.Engine.0x...506c COMMIT
2009-08-25 13:07:48,607 INFO sqlalchemy.engine.base.Engine.0x...506c BEGIN
2009-08-25 13:07:48,611 INFO sqlalchemy.engine.base.Engine.0x...506c select nextval('"a_id_seq"')
2009-08-25 13:07:48,612 INFO sqlalchemy.engine.base.Engine.0x...506c None
2009-08-25 13:07:48,617 INFO sqlalchemy.engine.base.Engine.0x...506c INSERT INTO a (id) VALUES (%(id)s)
2009-08-25 13:07:48,619 INFO sqlalchemy.engine.base.Engine.0x...506c {'id': 1L}
2009-08-25 13:07:48,627 INFO sqlalchemy.engine.base.Engine.0x...506c select nextval('"b_a_id_seq"')
2009-08-25 13:07:48,629 INFO sqlalchemy.engine.base.Engine.0x...506c None
2009-08-25 13:07:48,632 INFO sqlalchemy.engine.base.Engine.0x...506c INSERT INTO b (a_id, foo) VALUES (%(a_id)s, %(foo)s)
2009-08-25 13:07:48,634 INFO sqlalchemy.engine.base.Engine.0x...506c {'foo': None, 'a_id': 1L}
From the db point of view everything is fine. But it's propably not something the programmer wanted. Because of that, shouldn't use of sequence for fk column be blocked entirely?
Comments (4)
-
repo owner
-
repo owner
crap. trunk returns:
sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column "a_id" violates not-null constraint 'INSERT INTO b (foo) VALUES (%(foo)s) RETURNING b.a_id' {'foo': None}as expected (i.e. no assumption of anything, just a non-autoexec'ed column), but that's with implicit returning. with it off we get the same error. here's a patch:
Index: lib/sqlalchemy/sql/compiler.py =================================================================== --- lib/sqlalchemy/sql/compiler.py (revision 6289) +++ lib/sqlalchemy/sql/compiler.py (working copy) @@ -845,7 +845,7 @@ not isinstance(c.default, schema.Sequence) ) ) or \ - self.dialect.preexecute_autoincrement_sequences: + (self.dialect.preexecute_autoincrement_sequences and c is stmt.table._autoincrement_column): values.append((c, self._create_crud_bind_param(c, None))) self.prefetch.append(c) -
repo owner
- changed status to resolved
put the check into the
PGDefaultRunnerinstead, which also had some cruft removed. 3d38969fd4f88d996d438df63f7cbb1833b63429. -
repo owner
- removed milestone
Removing milestone: 0.6.0 (automated comment)
- Log in to comment
nothing to do with the ORM. The engine/compiler should not be preexecuting a PK seq on a foreign key column. have you tried with the trunk ? the behavior is likely different. it should just pass NULL along and raise an error.