Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cascade rules on INSERT wrong behaviour on 16.0

Cascade rules on INSERT wrong behaviour on 16.0

From
Oleksandr Voytsekhovskyy
Date:
Greetings to all

Here is the Structure:

———————————
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);
———————————

I am running:
———————————

test=# select version();

                                                              version                                                              

-----------------------------------------------------------------------------------------------------------------------------------

 PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit


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.

If I do insert directly to second table everything is fine. The issue is happen only when I have a chains from 3+ tables. 

If I change 1st rule to trigger the issue will be still there.

Thanks a lot. 

Re: Cascade rules on INSERT wrong behaviour on 16.0

From
Laurenz Albe
Date:
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