Hello Tom,
Thank you for the answer. Now it is more clear for me.
According to this information I can cause another kind of error:
--drop table tbl cascade;
--drop table rule_stat cascade;
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';
Result:
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".
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).
Best regards,
Hanna
PG Bug reporting form <noreply@postgresql.org> writes:
> create table tbl (id int);
> create table rule_stat (msg text, id int);
> create rule rule_tbl as on insert to tbl do insert into rule_stat
> values('Rule triggered for ',new.id);
> insert into tbl
> select 1
> except
> select id from tbl;
> table rule_stat; -- no rows
This is not a bug. The DO ALSO command executes after the original
INSERT command, and what it executes looks basically like
insert into rule_stat
select 'Rule triggered for ', id from
(select 1
except
select id from tbl);
But at this point we've already completed the original INSERT,
so now there is a row with id 1 in "tbl", and thus the EXCEPT
produces nothing.
While there are use-cases for this sort of behavior, most people
find that propagating data to another table is better done with
an AFTER trigger. It's far easier to understand what will happen.
regards, tom lane
This e-mail and any files transmitted with it contain confidential and/or privileged information.
Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
By messaging with Spotware you consent to the foregoing.