From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Oleksandr Voytsekhovskyy <young.inbox@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: Cascade rules on INSERT wrong behaviour on 16.0
Date: Wed, 31 Jan 2024 16:52:20 +0100
Message-ID: <2b698dcbd606bf8c5afec14ef159d16a1b7270ea.camel@cybertec.at> (raw)
In-Reply-To: <9753FA5E-5AC8-4446-B335-70A215447026@gmail.com>
References: <9753FA5E-5AC8-4446-B335-70A215447026@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, young.inbox@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Cascade rules on INSERT wrong behaviour on 16.0
In-Reply-To: <2b698dcbd606bf8c5afec14ef159d16a1b7270ea.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox