patternsqlModerate
When exactly are IMMEDIATE constraints checked and what exactly is a "statement" in Postgres?
Viewed 0 times
constraintswhatstatementarepostgreswhenandcheckedexactlyimmediate
Problem
I'm currently confused on the exact timing of IMMEDIATE constraint checks. Hopefully, the following example captures my confusion:
In the second query, despite referencing t1,
This is PG version 14.3.
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.
Solution
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:
and the processing goes like this:
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:
- Insert stuff. Bail out if a primary key, unique, or check constraint is violated.
- Scan the
returningresult set.
- For each row execute a function. Bail out if it throws exception.
- Reach the end of the result set.
- Bail out if an
immediatereferential integrity constraint is violated.
- Go on until the end of transaction.
- Bail out if a
deferredreferential integrity constraint is violated.
- Commit.
Code Snippets
with t1 as (insert into b values (100, 200) returning id, a_id)
select * from t1 where expensive_exception_thrower(t1.a_id) = true;Context
StackExchange Database Administrators Q#314340, answer score: 11
Revisions (0)
No revisions yet.