Hi all,
Encountered a possible bug today. In short, it appears that the rule system is not deferring the constraint checking as specified. Here is an example.
create table "parent" (
"id" uuid primary key
);
create table "children" (
"id" uuid primary key
default gen_random_uuid()
references parent("id")
deferrable
initially deferred
);
-- this works as expected.
begin;
insert into "children" values ('74c247be-2fc3-43e5-94f8-d5e1af147a9b');
insert into "parent" values ('74c247be-2fc3-43e5-94f8-d5e1af147a9b');
commit;
-- doing the same with a rule
create rule on_insert as on insert to "children" do also (
insert into "parent" (id) values (new."id");
);
-- this fails with:
-- ERROR: insert or update on table "children" violates foreign key constraint "children_id_fkey"
-- DETAIL: Key (id)=(40513132-f9fa-4e5a-aa46-20c8185b3e58) is not present in table "parent".
-- SQL state: 23503
insert into "children" values (default);
The rules system supposedly transforms the insert statement and executed the rule action in the same transaction. So I think it should work.
But I got the same error on both pg13 and pg14.
Is there something I missed here? or is my understanding of the rule system just simply wrong?
Regards,
Louis Tian
louis.tian@aquamonix.com.au