From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hanna Yanchurevich <hyanchurevich@spotware.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17827: Rule on insert into table doesn't work when excepting
 select from the table itself
In-reply-to: 
 <CAGcHEgJkHDr875wD2eAb4j2JFGqGXb-Wff6TG3QLZrLd0TvxTQ@mail.gmail.com>
References: <17827-1dfd5ad9af280484@postgresql.org>
 <377748.1678374108@sss.pgh.pa.us>
 <CAGcHEgJkHDr875wD2eAb4j2JFGqGXb-Wff6TG3QLZrLd0TvxTQ@mail.gmail.com>
Comments: In-reply-to Hanna Yanchurevich <hyanchurevich@spotware.com>
	message dated "Fri, 10 Mar 2023 10:18:54 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <804431.1678459321.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 10 Mar 2023 09:42:01 -0500
Message-ID: <804432.1678459321@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/804432.1678459321%40sss.pgh.pa.us>
Precedence: bulk

Hanna Yanchurevich <hyanchurevich@spotware.com> writes:
> According to this information I can cause another kind of error:
> create table tbl (id serial primary key, msg text);
> create table rule_stat (msg text, id int references tbl(id));
> create rule rule_tbl as on insert to tbl do insert into rule_stat values=
('Last
> inserted id was ',new.id);
> insert into tbl (msg)
> select 'I`m an insert';
> SQL Error [23503]: ERROR: insert or update on table "rule_stat" violates
> foreign key constraint "rule_stat_id_fkey"
>   Detail: Key (id)=3D(2) is not present in table "tbl".

Yup.  It's pretty obvious what's going on if you look at EXPLAIN:

explain verbose insert into tbl (msg)
select 'I`m an insert';
                               QUERY PLAN                               =

------------------------------------------------------------------------
 Insert on public.tbl  (cost=3D0.00..0.01 rows=3D0 width=3D0)
   ->  Result  (cost=3D0.00..0.01 rows=3D1 width=3D36)
         Output: nextval('tbl_id_seq'::regclass), 'I`m an insert'::text
 =

 Insert on public.rule_stat  (cost=3D0.00..0.01 rows=3D0 width=3D0)
   ->  Result  (cost=3D0.00..0.01 rows=3D1 width=3D36)
         Output: 'Last
 inserted id was '::text, nextval('tbl_id_seq'::regclass)
(8 rows)

> Such behaviour is a bit confusing. Because by using new.* I expect to ge=
t a
> recently inserted row, but not the result of some query running the seco=
nd
> time (which causes implicit incrementing of id serial).

If that's the mental model you want to work with, use a trigger.
When you work with rules, you are working with macros, and they
have the same sort of multiple-evaluation hazards as macros in,
say, C.

			regards, tom lane