Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit

On Thu, Nov 24, 2022 at 9:36 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17696
> Logged by: Roman Garcia
> Email address: yzerno(at)gmail(dot)com
> PostgreSQL version: 13.2
> Operating system: linux ubuntu
> Description:
>
> Executing the following simple script:
>
> BEGIN;
> CREATE table foo (id integer primary key);
> CREATE TABLE bar(id integer, foo_id integer);
> insert into foo (id) values (1);
> insert into bar(id,foo_id) values (1, 2);
> alter table bar add constraint foo_fkey foreign key (foo_id) references
> foo(id) deferrable initially deferred;
>
> results in a constraint violation error at the constraint creation line:
> " ERROR: insert or update on table "bar" violates foreign key constraint
> "foo_fkey" DETAIL: Key (foo_id)=(2) is not present in table "foo". "
>
> I would have expected to get this error message later, at transaction
> commit
> (if no foo with id 2 have been inserted before then) instead of getting it
> at constraint creation, since the point of having an deferrable initially
> deferred constraint is to move the constraint check when the transaction is
> commited.
>
> I found no indication of this behaviour in the documentation, only an
> example of the opposite case: if there exists an already defined deferrable
> initially deferred constraint, but we set it to immediate during
> transaction
> with SET CONSTRAINTS, then it is checked immediately, which should be
> expected.
>

Not sure about the documentation but when you add a constraint to a table
(DDL) it is immediately validated. The deferrable behavior only applies
when executing DML (insert/update/delete).

You cannot add that constraint to the table until you've ensured that all
existing data already conforms to said constraint.

David J.