Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rVAtM-00FJIy-Nt for pgsql-bugs@arkaria.postgresql.org; Wed, 31 Jan 2024 13:44:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rVAtL-00E6FR-PV for pgsql-bugs@arkaria.postgresql.org; Wed, 31 Jan 2024 13:44:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rVAtL-00E6FJ-5p for pgsql-bugs@lists.postgresql.org; Wed, 31 Jan 2024 13:44:39 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rVAtI-004KkT-L5 for pgsql-bugs@lists.postgresql.org; Wed, 31 Jan 2024 13:44:37 +0000 Received: by mail-ej1-x634.google.com with SMTP id a640c23a62f3a-a3651c87ebbso189962966b.1 for ; Wed, 31 Jan 2024 05:44:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1706708674; x=1707313474; darn=lists.postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=yA35YIpiq+vUo0WN/D0ZBzA3AezQxsvYLavSQzm+v50=; b=KVMlp2+bs90f910aFQl3OHX4GI/JFmW0e8Q1DtGt/K7S5AAvy8ayaTxBq3Qy7k3e1q GtmDfWpYltFWib4BSoJQfhIwoxDqnWvXL45+EmMbVuOHChehpsPLxfyJkT0ASt5XLoaj zAEzGV/gXtSXeoRgYUsZxONdOv88qFAcOoWXm+VFteOEFAyoGJl/G5V4momCITDDo9EV bFj3kzBzwVgZPHeWM32lzRlgMQxQGXoP+Mfl9ZNATC78eqXtcA3F0NLukcv3DXoBMVit gLp2ALO6W7w+JtDgq2T61JDp+oESyijd1muyQ0D6Bam834vHyXZTwqolaTdNjUUUohgA iHYA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1706708674; x=1707313474; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=yA35YIpiq+vUo0WN/D0ZBzA3AezQxsvYLavSQzm+v50=; b=p/uoxXUsc7yoLlDKzPsXrJOigFcbViqypo26BwIO7/0A8WZngnZCreCv9/YSx+6OPz J123C8wIUuuNdIXFebJg73VBDWtzxFW7XsJfMepbX6PG28oRK/1Kyaz/jrGdvZmhBEd4 FT+z6niJTPL5UiKWrs7LD63iEKUKY30+U8569pO+8HEkbQpH+rRSeo4lMgDJBltF2cPm UGMHokHzdWTjHOWOHNsuNn87z5W+6KQvxSVLKHpYSoZ8nfdZFLtYXR+tn8EaVIvalA6V hT6wwc6XU8ScDM9QGAuv5MbASUfDenyc3C2zcw8DOS6o0d+mtXf8/8APnAFxJQXug/j3 yXnQ== X-Gm-Message-State: AOJu0Yzq+IcG8jpfHs+mRQUjknKiZSv3jC/dw0rRKP558l7d3uESbQRU pByBiIrazRee1JJzq4VDpagVmXEyqFQkajgxspnZJPLUtTt/qGDOG0M2JMYb X-Google-Smtp-Source: AGHT+IFgg0tGEIm3NkM6mCtRuQMnmPpdINQYoW/pNzaOXUJj6U1fBmQyny/kU78jVl6iZbEDFeTWDQ== X-Received: by 2002:a17:906:1688:b0:a35:7f93:36f2 with SMTP id s8-20020a170906168800b00a357f9336f2mr1183197ejd.3.1706708674039; Wed, 31 Jan 2024 05:44:34 -0800 (PST) Received: from smtpclient.apple (81-223-91-136.static.upcbusiness.at. [81.223.91.136]) by smtp.gmail.com with ESMTPSA id s15-20020a170906354f00b00a3186c2c254sm6167898eja.213.2024.01.31.05.44.33 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 31 Jan 2024 05:44:33 -0800 (PST) From: Oleksandr Voytsekhovskyy Content-Type: multipart/alternative; boundary="Apple-Mail=_DBA91566-E9A6-47BF-8A4C-1F2E8F432B44" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Cascade rules on INSERT wrong behaviour on 16.0 Message-Id: <9753FA5E-5AC8-4446-B335-70A215447026@gmail.com> Date: Wed, 31 Jan 2024 14:44:22 +0100 To: pgsql-bugs@lists.postgresql.org X-Mailer: Apple Mail (2.3774.300.61.1.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_DBA91566-E9A6-47BF-8A4C-1F2E8F432B44 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Greetings to all Here is the Structure: =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94 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); =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94 I am running: =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94 test=3D# select version(); version = =20 = --------------------------------------------------------------------------= --------------------------------------------------------- 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=3D# insert into first values = ('9ea8e702-421c-45d5-842f-a81dc3848022'); INSERT 0 1 test=3D# select * from second; first_id | second_id = =20 = --------------------------------------+-----------------------------------= --- 9ea8e702-421c-45d5-842f-a81dc3848022 | = 8c601583-df70-47fd-b748-7dc4a5bc9f75 (1 row) test=3D# select* from third; second_id =20 -------------------------------------- 848919c7-de3d-4635-bf3a-07d200cb2792 (1 row) =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94 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.=20 If I change 1st rule to trigger the issue will be still there. Thanks a lot.=20= --Apple-Mail=_DBA91566-E9A6-47BF-8A4C-1F2E8F432B44 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Greetings to = all

Here is the = Structure:

=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94
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);
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94

I am = running:
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94

test=3D# 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=3D# insert into first values = ('9ea8e702-421c-45d5-842f-a81dc3848022');

INSERT 0 1

test=3D# select * from second;

             =   first_id               |  =             second_id      =          

--------------------------------------+--------------= ------------------------

 9ea8e702-421c-45d5-842f-a81dc3848022 | = 8c601583-df70-47fd-b748-7dc4a5bc9f75

(1 row)


test=3D#  select* from third;

              = second_id              =  

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

 848919c7-de3d-4635-bf3a-07d200cb2792

=

(1 row)

=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94=E2=80=94



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. 
= --Apple-Mail=_DBA91566-E9A6-47BF-8A4C-1F2E8F432B44--