Re: 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