How to `SET CONSTRAINTS ... DEFERRED` in SQLALchemy Core

Question

I am using SQLAlchemy and PostgreSQL. Postgres supports doing deferred constraints, which allows us to postpone checking the constraints on a table until the end of the transaction.

For example, in SQLAlchemy, I might define a table like this:

t_group_categories = Table('group_categories', metadata,
    Column('id', Integer, primary_key=True),
    Column('group_id', Integer, ForeignKey('groups.id', deferrable=True))
)

SQLAlchemy would generate a CREATE TABLE statement that would look something like:

CREATE TABLE group_categories
(
  id serial NOT NULL,
  group_id integer,
  CONSTRAINT group_categories_pkey PRIMARY KEY (id),
  CONSTRAINT group_categories_group_id_fkey FOREIGN KEY (group_id)
      REFERENCES groups (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)

To my understanding, the DEFERRABLE INITIALLY IMMEDIATE means that the FOREIGN KEY constraint will act as if it is a non-deferrable constraint, unless it is explicitly told otherwise, which is exactly what I want.

The problem is that I can't seem to find any information on how to get the SQLAlchemy core to actually issue a SET CONSTRAINTS ... DEFERRED command while inside of a transaction. For example, say that I have the following code:

connection = engine.connect()
...
with connection.begin() as transaction:
    # Create a group
    r = connection.execute(
        t_groups.insert().values(...)
    )
    group_id = r.inserted_primary_key

    # Assign a category to the group (ERROR!)
    r2 = connection.execute(
        t_group_categories.insert().values(group_id=group_id, ...)
    )

The first block simply creates a new group. The second block then tries to assign the group that we just created a category. The problem is that without the special SET CONSTRAINTS ... DEFERRED, we can't actually create the group_categories entry without violating the foreign key constraints on the table, since the transaction hasn't committed yet.

What I want to do in this instance is defer checking the constraint until after the transaction commits. How can I actually defer constraints checks until after the transaction completes?

---

Notes:

• The question at How to SET CONSTRAINTS DEFERRED in sqlalchemy expression language? is similar, but the OP was interested in using DEFERRABLE INITIALLY DEFERRED, which I would prefer not to do. Instead (if possible), I want to keep my constraint as DEFERRABLE INITIALLY IMMEDIATE and explicitly mark instances where the constraint needs to be deferred.
• SQLAlchemy is the one that generated the DEFERRABLE INITIALLY DEFERRED constraint, so I am hoping/assuming that it has an expressive way of actually using this constraint on the other side (namely an expression language way of issuing the SET CONSTRAINTS ... DEFERRED.

---

Update:

• Doing connection.execute("SET CONSTRAINTS ALL DEFERRED") also doesn't seem to have any effect; I am still getting an IntegrityError.
• Doing connection.execute("SET CONSTRAINTS group_categories_group_id_fkey DEFERRED") inside the transaction block also gives back an IntegrityError.

Cheri

so you can't just do connection.execute("SET CONSTRAINTS DEFERRED") inside the transaction block? – Veinule

SET CONSTRAINTS DEFERRED isn't actually valid syntax, so no: see postgresql.org/docs/9.4/static/sql-set-constraints.html since it requires either an ALL or name parameter. I'll update the question to try and reflect this. – Cheri

That exception is presumably a reflection of an SQL error from the database. Does the exception contain an error message from the database? Would be good for you to paste full content of the exception. You can also look in the postgresql log to see if there is a corresponding error there. – Veinule

Answer 1

I could defer the constraint by using a metadata naming convention to ensure that the constraint had a name which could be used in the SET CONSTRAINTS statement.

convention = {
    'ix': 'ix_%(column_0_label)s',
    'uq': 'uq_%(table_name)s_%(column_0_name)s',
    'ck': 'ck_%(table_name)s_%(constraint_name)s',
    'fk': 'fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s',
    'pk': 'pk_%(table_name)s',
}

metadata = sa.MetaData(naming_convention=convention)

...
# Specify "initially immediate" 
t_group_categories = sa.Table(
    'group_categories',
    metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column(
        'group_id',
        sa.Integer,
        sa.ForeignKey('groups.id', deferrable=True, initially='IMMEDIATE'),
    ),
)

engine = sa.create_engine('postgresql:///test', echo=True, future=True)
metadata.drop_all(engine)
metadata.create_all(engine)

# If the table had multiple FK constraints we would need to find the right one,
# but here we can just unpack the sole contraint.
constraint, = t_group_categories.foreign_key_constraints

with engine.begin() as conn:
    conn.execute(sa.text(f'SET CONSTRAINTS "{constraint.name}" DEFERRED'))
    # Passing id before the group is created will trigger an immediate constraint.
    conn.execute(t_group_categories.insert().values(group_id=1))
    conn.execute(t_groups.insert())

Weinstein