Hi there,

I'm using Python 3.6.2, SQLAlchemy 1.1.13, and PostgreSQL 9.6.3

I've got a non-trivial relationship involving a nullable composite foreign 
key and cannot get SQLAlchemy to correctly order its UPDATE statements. 
It's easier to explain with an MWE:

import sqlalchemy as sa
import sqlalchemy.ext.declarative
import sqlalchemy.orm

engine = sa.create_engine('postgresql://...', echo=True)

Base = sa.ext.declarative.declarative_base()

###############################
# define the models           #
###############################

class A(Base):
    __tablename__ = 'a'

    # valid values are NULL and False
    archived = sa.Column(sa.Boolean, nullable=True)

    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    token = sa.Column(sa.Integer, nullable=False, autoincrement=False)

    __table_args__ = (
        sa.UniqueConstraint('archived', 'token'),
        sa.UniqueConstraint('id', 'token'),
    )

class B(Base):
    __tablename__ = 'b'

    # valid values are NULL and False
    archived = sa.Column(sa.Boolean, nullable=True)

    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    token = sa.Column(sa.Integer, nullable=False, autoincrement=False)

    a_id = sa.Column(sa.Integer, nullable=False)
    a = sa.orm.relationship(
        A,
        primaryjoin=sa.and_(
            sa.orm.foreign(a_id) == A.id,
            sa.orm.foreign(token) == A.token,
        ),
        backref=sa.orm.backref('bs', cascade='all,delete-orphan'),
    )

    __table_args__ = (
        sa.ForeignKeyConstraint(
            ('archived', 'token'),
            ('a.archived', 'a.token'),
            ondelete='RESTRICT',
        ),
        sa.ForeignKeyConstraint(
            ('a_id', 'token'),
            ('a.id', 'a.token'),
            ondelete='RESTRICT',
        ),
    )

Base.metadata.create_all(engine)
Session = sa.orm.sessionmaker(bind=engine)

session = Session()

###############################
# insert some data            #
###############################

a = A(token=1, archived=False)
session.add(a)
session.flush()

b = B(a=a, archived=False)
session.add(b)
session.flush()


###############################
# try to archive both b and a #
###############################

b.archived = None
# uncomment this flush to explicitly order the `UPDATE`s and make things 
work
# session.flush()

a.archived = None
session.flush()

The problem comes from the last step where the two models are archived and 
then the session is flushed. SQLAlchemy insists on flushing the update on A 
before the B, which results in a foreign key constraint violation.

$ python sqla_ordering.py 
2017-08-12 22:38:11,283 INFO sqlalchemy.engine.base.Engine select version()
2017-08-12 22:38:11,283 INFO sqlalchemy.engine.base.Engine {}
2017-08-12 22:38:11,284 INFO sqlalchemy.engine.base.Engine select 
current_schema()
2017-08-12 22:38:11,284 INFO sqlalchemy.engine.base.Engine {}
2017-08-12 22:38:11,285 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-08-12 22:38:11,285 INFO sqlalchemy.engine.base.Engine {}
2017-08-12 22:38:11,285 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-08-12 22:38:11,285 INFO sqlalchemy.engine.base.Engine {}
2017-08-12 22:38:11,286 INFO sqlalchemy.engine.base.Engine show 
standard_conforming_strings
2017-08-12 22:38:11,286 INFO sqlalchemy.engine.base.Engine {}
2017-08-12 22:38:11,287 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
2017-08-12 22:38:11,287 INFO sqlalchemy.engine.base.Engine {'name': 'a'}
2017-08-12 22:38:11,287 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
2017-08-12 22:38:11,287 INFO sqlalchemy.engine.base.Engine {'name': 'b'}
2017-08-12 22:38:11,292 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-08-12 22:38:11,292 INFO sqlalchemy.engine.base.Engine INSERT INTO a 
(archived, token) VALUES (%(archived)s, %(token)s) RETURNING a.id
2017-08-12 22:38:11,292 INFO sqlalchemy.engine.base.Engine {'archived': 
False, 'token': 1}
2017-08-12 22:38:11,294 INFO sqlalchemy.engine.base.Engine INSERT INTO b 
(archived, token, a_id) VALUES (%(archived)s, %(token)s, %(a_id)s) 
RETURNING b.id
2017-08-12 22:38:11,294 INFO sqlalchemy.engine.base.Engine {'archived': 
False, 'token': 1, 'a_id': 11}
2017-08-12 22:38:11,296 INFO sqlalchemy.engine.base.Engine UPDATE a SET 
archived=%(archived)s WHERE a.id = %(a_id)s
2017-08-12 22:38:11,296 INFO sqlalchemy.engine.base.Engine {'archived': 
None, 'a_id': 11}
2017-08-12 22:38:11,297 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
 
line 1182, in _execute_context
    context)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
 
line 470, in do_execute
    cursor.execute(statement, parameters)
psycopg2.IntegrityError: update or delete on table "a" violates foreign key 
constraint "b_archived_fkey" on table "b"
DETAIL:  Key (archived, token)=(f, 1) is still referenced from table "b".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "sqla_ordering.py", line 73, in <module>
    session.flush()
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
 
line 2171, in flush
    self._flush(objects)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
 
line 2291, in _flush
    transaction.rollback(_capture_exception=True)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py",
 
line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
 
line 187, in reraise
    raise value
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
 
line 2255, in _flush
    flush_context.execute()
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
 
line 389, in execute
    rec.execute(self)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
 
line 548, in execute
    uow
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py",
 
line 177, in save_obj
    mapper, table, update)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py",
 
line 737, in _emit_update_statements
    execute(statement, multiparams)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
 
line 945, in execute
    return meth(self, multiparams, params)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/sql/elements.py",
 
line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
 
line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
 
line 1189, in _execute_context
    context)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
 
line 1402, in _handle_dbapi_exception
    exc_info
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
 
line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
 
line 186, in reraise
    raise value.with_traceback(tb)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
 
line 1182, in _execute_context
    context)
  File 
"/home/rraval/.local/share/virtualenvs/sqla_ordering/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
 
line 470, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete 
on table "a" violates foreign key constraint "b_archived_fkey" on table "b"
DETAIL:  Key (archived, token)=(f, 1) is still referenced from table "b".
 [SQL: 'UPDATE a SET archived=%(archived)s WHERE a.id = %(a_id)s'] 
[parameters: {'archived': None, 'a_id': 11}]

The idea behind the "archived" column is that its either NULL (to indicate 
"archived") or False (to indicate "unarchived"). The foreign keys are setup 
such that the DB enforces that an unarchived B has a corresponding 
unarchived A. However an archived B is free to have either an archived or 
unarchived A.

Each B also has an explicit a_id column and the B.a relationship has an 
explicit primaryjoin to use it. I believe this B.a relationship is the 
reason SQLAlchemy is reordering the UPDATEs. Removing the relationship and 
adjusting the add data step to set a_id and token directly causes the flush 
to correctly update the B then the A (respecting the temporal order in 
which those updates happened).

How can I make this work without explicitly calling session.flush on the b?

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

Reply via email to