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.

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)

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