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

Maybe I should have created this report as a possible evolution then ? As I
understand it, the whole point of deferrable initially deferred is for it
to be checked at transaction commit, not just for insert/update/delete, but
as a general concept for the constraint.

Roman

Le jeu. 24 nov. 2022 à 17:51, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
a écrit :

> 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.
>
>