Re: Cascade rules on INSERT wrong behaviour on 16.0

On Wed, 2024-01-31 at 14:44 +0100, Oleksandr Voytsekhovskyy wrote:
> CREATE TABLE first (
>     id uuid DEFAULT gen_random_uuid() PRIMARY KEY
> );
>
> CREATE TABLE second (
>     first_id uuid,
>     second_id uuid
> );
>
> CREATE TABLE third (
>     second_id uuid
> );
>
> CREATE RULE first_insert AS ON INSERT TO first DO ALSO
> INSERT INTO second VALUES (NEW.id, gen_random_uuid());
>
> CREATE RULE second_insert AS ON INSERT TO second DO ALSO
> INSERT INTO third VALUES (NEW.second_id);
>
> test=# insert into first values ('9ea8e702-421c-45d5-842f-a81dc3848022');
> INSERT 0 1
> test=# select * from second;
>                first_id               |              second_id               
> --------------------------------------+--------------------------------------
>  9ea8e702-421c-45d5-842f-a81dc3848022 | 8c601583-df70-47fd-b748-7dc4a5bc9f75
> (1 row)
>
> test=#  select* from third;
>               second_id               
> --------------------------------------
>  848919c7-de3d-4635-bf3a-07d200cb2792
> (1 row)
>
>
> I expect that values of second_id from the table second and third will be the same, but in fact they are different.

That is expected, and that's how rules work.

If you don't want that, you should use triggers, which are working
in a less surprising fashion.

Yours,
Laurenz Albe