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

On Fri, Nov 25, 2022 at 7:44 PM Jaime Casanova
<jcasanov(at)systemguards(dot)com(dot)ec> wrote:
>
> 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;
>

well, not exactly as you wish because you need to VALIDATE the
constraint but you can choose to do it just before the COMMIT

--