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

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.

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.g.johnston@gmail.com>
a écrit :

On Thu, Nov 24, 2022 at 10:12 AM Roman Garcia <yzerno@gmail.com> wrote:

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.

I don't see any benefit for the effort making it work for DDL. How exactly
were you expecting to benefit from having the constraint deferred during
the transaction in which you created it?

But maybe you have a suggestion for the documentation to make this
distinction more clear?

David J.

On 2022-Nov-24, David G. Johnston wrote:

On Thu, Nov 24, 2022 at 10:12 AM Roman Garcia <yzerno@gmail.com> wrote:

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.

I don't see any benefit for the effort making it work for DDL.

Yeah ... I agree that this scenario seems rather pointless, but I don't
see any hard argument for making it not work, other than development
effort. If Roman or somebody would like to submit a patch to implement
it, I don't see why we wouldn't accept it (pending review process, ofc).

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Crear es tan difícil como ser libre" (Elsa Triolet)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

On 2022-Nov-24, David G. Johnston wrote:

I don't see any benefit for the effort making it work for DDL.

Yeah ... I agree that this scenario seems rather pointless, but I don't
see any hard argument for making it not work, other than development
effort.

There would need to be a side-eye on whether it breaks pg_dump scripts
being run in single-transaction mode. Verifications of deferred fkey
constraints would all be delayed till the final COMMIT, which at the
very least is not what pg_dump is expecting.

I think it'd pose a problem for parallel restores, in which restoration
of such constraints could no longer be parallelized. I don't think we
have any cases in which a subsequent view creation would actually fail,
but I can believe that a subsequent matview-population query would run
much more slowly than expected because the fkey relationship couldn't
be used yet for plan optimization.

On the whole I share David's guess that the cost/benefit ratio of
making this happen is unattractive. I might feel impelled to do
it anyway if somebody could show that the SQL spec requires it ...
but I bet you'll search for that in vain, because I doubt that the
SQL spec requires transactional DDL at all. (There are too many
influential implementations that would have a problem with that.)

regards, tom lane

On 11/25/22 03:55, Tom Lane wrote:

On the whole I share David's guess that the cost/benefit ratio of
making this happen is unattractive. I might feel impelled to do
it anyway if somebody could show that the SQL spec requires it ...
but I bet you'll search for that in vain, because I doubt that the
SQL spec requires transactional DDL at all. (There are too many
influential implementations that would have a problem with that.)

The spec does require transactional DDL (SQL:2016 4.41.1 General
description of SQL-transactions) but does not allow mixing of DDL and
DML in the same transaction. What happens when you do that is
implementation-defined.

So in this case it would not really matter when the new constraint is
verified because the user would have no opportunity to fix the data anyway.
--
Vik Fearing

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;

--
Jaime Casanova
Director de Servicios Profesionales
SYSTEMGUARDS

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

--