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@systemguards.com.ec> wrote:
>
> On Thu, Nov 24, 2022 at 11:36 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference:      17696
> > Logged by:          Roman Garcia
> > Email address:      yzerno@gmail.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


--