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

On Thu, Nov 24, 2022 at 11: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.
>

BTW, you can make this work as you expect if you mark the FK as NOT VALID:

alter table bar add constraint foo_fkey foreign key (foo_id) references
foo(id) deferrable initially deferred NOT VALID;

--
Jaime Casanova
Director de Servicios Profesionales
SYSTEMGUARDS