How to `SET CONSTRAINTS … DEFERRED` in SQLALchemy

2020-07-11 10:04发布

问题:

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.