Обсуждение: PostgreSQL Rule does not work with deferred constraint.
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 keydefault gen_random_uuid()references parent("id")deferrableinitially 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 rulecreate 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: 23503insert 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
On Wed, 2022-09-21 at 01:19 +0000, Louis Tian wrote: > Encountered a possible bug today. In short, it appears that the rule system is not deferring the constraint checking asspecified. 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 Ithink 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? I just tried your commands, and it works as you expect on my PostgreSQL v15 database. Yours, Laurenz Albe
Laurenz Albe <laurenz.albe@cybertec.at> writes: > I just tried your commands, and it works as you expect on my PostgreSQL v15 database. It does fail for me, but I think it's a well-known trap rather than a bug (or at least, it's not something that anyone wishes to redesign the rule system to change). The problem is that *a rule is a macro* and therefore it's subject to multiple-evaluation hazards. Your volatile default expression does not play nice with that. Initially you have: insert into "children" values (default); Replacement of the "default" produces: insert into "children" values (gen_random_uuid()); Now the DO ALSO rule produces: insert into "parent" (id) values (gen_random_uuid()); The two insertions will compute different random UUIDs, and kaboom. We tend to recommend using triggers not rules to implement this sort of behavior; they are less prone to surprises. regards, tom lane