When exactly are IMMEDIATE constraints checked and what exactly is a "statement" in Postgres?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply