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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
Дата
Msg-id 17696-c1f5b5e52f3e5833@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit
Список pgsql-bugs
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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: "施博文"
Дата:
Сообщение: 回复: BUG #17695: Failed Assert in logical replication snapbuild.
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit