pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
9+ messages / 7 participants
[nested] [flat]
* BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
@ 2022-11-24 15:58 PG Bug reporting form <noreply@postgresql.org>
2022-11-24 16:50 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
2022-11-26 00:44 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 2 replies; 9+ messages in thread
From: PG Bug reporting form @ 2022-11-24 15:58 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: yzerno@gmail.com
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
@ 2022-11-24 16:50 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:12 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Roman Garcia <yzerno@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2022-11-24 16:50 UTC (permalink / raw)
To: yzerno@gmail.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
2022-11-24 16:50 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
@ 2022-11-24 17:12 ` Roman Garcia <yzerno@gmail.com>
2022-11-24 17:22 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Roman Garcia @ 2022-11-24 17:12 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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 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.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
2022-11-24 16:50 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:12 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Roman Garcia <yzerno@gmail.com>
@ 2022-11-24 17:22 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 22:23 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2022-11-24 17:22 UTC (permalink / raw)
To: Roman Garcia <yzerno@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
2022-11-24 16:50 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:12 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Roman Garcia <yzerno@gmail.com>
2022-11-24 17:22 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
@ 2022-11-24 22:23 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-11-25 02:55 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2022-11-24 22:23 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Roman Garcia <yzerno@gmail.com>; pgsql-bugs@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
2022-11-24 16:50 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:12 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Roman Garcia <yzerno@gmail.com>
2022-11-24 17:22 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 22:23 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-11-25 02:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-26 00:13 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Vik Fearing <vik@postgresfriends.org>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2022-11-25 02:55 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Roman Garcia <yzerno@gmail.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
2022-11-24 16:50 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:12 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Roman Garcia <yzerno@gmail.com>
2022-11-24 17:22 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 22:23 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-11-25 02:55 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-26 00:13 ` Vik Fearing <vik@postgresfriends.org>
0 siblings, 0 replies; 9+ messages in thread
From: Vik Fearing @ 2022-11-26 00:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Roman Garcia <yzerno@gmail.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
@ 2022-11-26 00:44 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2022-11-26 00:53 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Jaime Casanova <jcasanov@systemguards.com.ec>
1 sibling, 1 reply; 9+ messages in thread
From: Jaime Casanova @ 2022-11-26 00:44 UTC (permalink / raw)
To: yzerno@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
2022-11-26 00:44 ` Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2022-11-26 00:53 ` Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 0 replies; 9+ messages in thread
From: Jaime Casanova @ 2022-11-26 00:53 UTC (permalink / raw)
To: yzerno@gmail.com; pgsql-bugs@lists.postgresql.org
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
--
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2022-11-26 00:53 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-24 15:58 BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit PG Bug reporting form <noreply@postgresql.org>
2022-11-24 16:50 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:12 ` Roman Garcia <yzerno@gmail.com>
2022-11-24 17:22 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 22:23 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-11-25 02:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-26 00:13 ` Vik Fearing <vik@postgresfriends.org>
2022-11-26 00:44 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2022-11-26 00:53 ` Jaime Casanova <jcasanov@systemguards.com.ec>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox