4

I'm currently confused on the exact timing of IMMEDIATE constraint checks. Hopefully, the following example captures my confusion:

create table a (
  id int primary key
);

create table b (
  id int primary key,
  a_id int not null references a
);

/* violates foreign key constraint "b_a_id_fkey" */
with t1 as (insert into b values (100, 200) returning id, a_id)
  select * from t1;

/* ERROR: expensive_exception_thrower */
with t1 as (insert into b values (100, 200) returning id, a_id)
  select * from t1 where expensive_exception_thrower(t1.a_id) = true;

In the second query, despite referencing t1, expensive_exception_thrower will throw its exception first, which result in the fkey exception being swallowed. Of course, there are workarounds, but I'd like to understand the exact definition of "statement" when the Postgres manual says IMMEDIATE constraints are checked immediately after each statement. The manual uses the term "statement" in a way that would indicate that the with clause is a statement, or at least "sub-statement."

This is PG version 14.3.

2
  • 2
    No, statement is the whole, well, statement. What is between two statement terminators ;. `A CTE is not a statement. Foreign key constraints that are IMMEDIATE are checked after each statement. The difference with DEFERRED constraints is that those are checked at the end of the transaction. Jul 12, 2022 at 23:40
  • You can try a statement with two CTEs where the first inserts into b and the 2nd CTE inserts into a. There is no FK violation: dbfiddle.uk/… Jul 12, 2022 at 23:53

1 Answer 1

11

As mentioned in a comment, a statement is everything between the previous statement terminator (usually a semicolon) and the next one, so this is one statement:

with t1 as (insert into b values (100, 200) returning id, a_id)
  select * from t1 where expensive_exception_thrower(t1.a_id) = true;

and the processing goes like this:

  1. Insert stuff. Bail out if a primary key, unique, or check constraint is violated.
  2. Scan the returning result set.
  3. For each row execute a function. Bail out if it throws exception.
  4. Reach the end of the result set.
  5. Bail out if an immediate referential integrity constraint is violated.
  6. Go on until the end of transaction.
  7. Bail out if a deferred referential integrity constraint is violated.
  8. Commit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.