PostgreSQL Rule does not work with deferred constraint.

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 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? 

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