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