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

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