Set Constraints All Deferred Not Working as Expected

SET CONSTRAINTS ALL DEFERRED not working as expected

Only DEFERRABLE constraints can be deferred.

Let me suggest superior alternatives first:

1. INSERT in order

Reverse the sequence of the INSERT statements and nothing needs to be deferred. Simplest and fastest - if at all possible.

2. Single command

Do it in a single command. Then still nothing needs to be deferred, as non-deferrable constraints are checked after each command and CTEs are considered to be part of single command:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(2)
   )
INSERT INTO a(i) VALUES(2);

While being at it, you can reuse the values for the first INSERT: safer / more convenient for certain cases or multi-row inserts:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(3)
   RETURNING j
   )
INSERT INTO a(i)
SELECT j FROM ins1;

But I need deferred constraints! (Really?)

ALTER TABLE b ADD CONSTRAINT fkey_ij FOREIGN KEY (j)
   REFERENCES a (i) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;  -- !!!

Then your original code works (a bit slower, as deferred constraints add cost).

db<>fiddle here

Related:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

---

My original answer quoted the manual:

Referential actions other than the NO ACTION check cannot be deferred,
even if the constraint is declared deferrable.

But that was misleading as it only applies to "referential actions", i.e. what happens ON UPDATE or ON DELETE to rows in the referenced table. The case at hand is not one of those - as @zer0hedge pointed out.

Is it possible to restore deferred constraints after setting SET CONSTRAINTS ALL IMMEDIATE?

Have you tried

SET CONSTRAINTS ALL DEFERRED

after you call SET CONSTRAINTS ALL IMMEDIATE?

If that would defer more constraints than you want to, you'll have to name them individually:

SET CONSTRAINTS my_constraint [, ...] DEFERRED;

It does not revert any effect. It only defers further checks. If a constraint is violated, an EXCEPTION is raised. There is nothing that could be reverted here.

You could catch the exception in a plpgsql function if that is what you want.

If you ant to revert constraints to their initial state, you'll have to SET them individually and with the explicit state. Sadly - to my knowledge - there is no "RESET CONSTRAINTS" in PostgreSQL 9.1 that would bring them all to their initial state.

Manual page.

postgresql, deferred constraint not working as I expect

DEFERRABLE only means that the check if no row violates the constraint is postponed until the transaction is committed. It does not mean that the effect of the DML that you run, is postponed until the end of the transaction.

In your example at the end of the transaction no row in t2 violates the foreign key constraint. So the COMMIT is successful.

Additionally: the DELETE FROM t1 will set all ref columns to NULL in the table t2 that contained the deleted IDs. If you then insert a new row into t1, how should Postgres know which of the (possibly millions) of ref columns that are NULL to reset to the initial value?

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

I remember having raised an almost identical point when PG9 was in alpha state. Here was the answer from Tom Lane (high-profile PG core developer):

http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php

In short: won't fix.

Not to say that I agree with your suggestion that the current behavior is a bug. Look at it from the opposite angle: it's the behavior of NOT DEFERRABLE that is incorrect.

In fact, the constraint violation in this UPDATE should never happen in any case, since at the end of the UPDATE the constraint is satisfied. The state at the end of the command is what matters. The intermediate states during the execution of a single statement should not be exposed to the user.

It seems like the PostgreSQL implements the non deferrable constraint by checking for duplicates after every row updated and failing immediately upon the first duplicate, which is essentially flawed. But this is a known problem, probably as old as PostgreSQL.
Nowadays the workaround for this is precisely to use a DEFERRABLE constraint. And there is some irony in that you're looking at it as deficient because it fails to fail, while somehow it's supposed to be the solution to the failure in the first place!

Summary of the status quo since PostgreSQL 9.1

• NOT DEFERRABLE UNIQUE or PRIMARY KEY constraints are checked after each row.

• DEFERRABLE constraints set to IMMEDIATE (INITIALLY IMMEDIATE or via SET CONSTRAINTS) are checked after each statement.

• DEFERRABLE constraints set to DEFERRED (INITIALLY DEFERRED or via SET CONSTRAINTS) are checked after each transaction.

Note the special treatment of UNIQUE / PRIMARY KEY constraints.
Quoting the manual page for CREATE TABLE:

A constraint that is not deferrable will be checked immediately after every command.

While it states further down in the Compatibility section under Non-deferred uniqueness constraints:

When a UNIQUE or PRIMARY KEY constraint is not deferrable,
PostgreSQL checks for uniqueness immediately whenever a row is
inserted or modified. The SQL standard says that uniqueness should be
enforced only at the end of the statement; this makes a difference
when, for example, a single command updates multiple key values. To
obtain standard-compliant behavior, declare the constraint as
DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware
that this can be significantly slower than immediate uniqueness checking.

Bold emphasis mine.

If you need any FOREIGN KEY constraints to reference the column(s), DEFERRABLE is not an option because (per documentation):

The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.

Find all Foreign Key errors with Oracle SET CONSTRAINTS ALL DEFERRED

First option, you can look into DML error logging. That way you leave your constraints active, then do the inserts, with the erroring rows going into error tables. You can find them there, fix them, re-insert the rows and delete them from the error table.

The second option is, rather than trying the commit, attempt to re-enable each deferred constraint dynamically, catching the errors. You'd be looking at a PL/SQL procedure that queries ALL_CONSTRAINTS for the deferrable ones, then does an EXECUTE IMMEDIATE to make that constraint immediate. That last bit would be in a block with an exception handler so you can catch the ones that fail, but continue on.

Personally, I'd go for option (1) as you do get the individual records that fail, plus you don't have to worry about deferrable constraints and remembering to make them immediate again after this script so it doesn't break something later.

I guess somewhere in memory Oracle must be maintaining a list, at least of constraints that failed if not the actual rows. I'm pretty confident it doesn't rescan the whole set of tables at commit time to check all the constraints.

oracle: deferring Foreign Key checks not working

You can't defer a constraint if it is NOT DEFERRABLE. A constraint can be either deferrable or not, but the default (if you don't explicitly specify either way) is NOT DEFERRABLE.

In your sample code, add the keyword deferrable right after the foreign key constraint definition, and then run everything again. It will work as expected.

That is: Edit the following line of code

constraint FK1 foreign key (id) references Foo (id));

to

constraint FK1 foreign key (id) references Foo (id) deferrable);

Postgres Sequelize transaction with deferred constraint doesn't work

The second query is not executed in a transaction context so adding a transaction should help:

...
await this.db.UpdatersGroups.bulkCreate(body.groupIds.map((groupId) => ({
    feature_id: groupId,
    updater_id: update.id,
}), { transaction })); // transaction here is applied.

SQLAlchemy ORM - Deferring constraint checking

Given that db is your session instance, run

db.execute('SET CONSTRAINTS ALL DEFERRED')

before your bulk operation in order to defer all deferrable constraints in the current transaction. Note that not all constraints are deferrable, even if they're declared as such. You could opt to defer the unique constraint only, if you know its name, such as unique_order:

def reorder(self, new_order, db):
    order = [{'id':i, 'question_order': index} for index, i in enumerate(new_order)]
    db.execute('SET CONSTRAINTS unique_order DEFERRED')
    db.bulk_update_mappings(Question, order)
    db.commit()

---

Related Topics