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.
