Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself

Hanna Yanchurevich <hyanchurevich(at)spotware(dot)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