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
Date: Fri, 10 Mar 2023 09:42:01 -0500
Message-ID: <804432.1678459321@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGcHEgJkHDr875wD2eAb4j2JFGqGXb-Wff6TG3QLZrLd0TvxTQ@mail.gmail.com>
References: <17827-1dfd5ad9af280484@postgresql.org>
<377748.1678374108@sss.pgh.pa.us>
<CAGcHEgJkHDr875wD2eAb4j2JFGqGXb-Wff6TG3QLZrLd0TvxTQ@mail.gmail.com>
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)=(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=0.00..0.01 rows=0 width=0)
-> Result (cost=0.00..0.01 rows=1 width=36)
Output: nextval('tbl_id_seq'::regclass), 'I`m an insert'::text
Insert on public.rule_stat (cost=0.00..0.01 rows=0 width=0)
-> Result (cost=0.00..0.01 rows=1 width=36)
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 get a
> recently inserted row, but not the result of some query running the second
> 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
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: tgl@sss.pgh.pa.us, hyanchurevich@spotware.com, 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: <804432.1678459321@sss.pgh.pa.us>
* 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