pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17827: Rule on insert into table doesn't work when excepting select from the table itself
4+ messages / 3 participants
[nested] [flat]
* BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself
@ 2023-03-09 10:27 PG Bug reporting form <noreply@postgresql.org>
2023-03-09 15:01 ` Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2023-03-09 10:27 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: hyanchurevich@spotware.com
The following bug has been logged on the website:
Bug reference: 17827
Logged by: Hanna Yanchurevich
Email address: hyanchurevich@spotware.com
PostgreSQL version: 13.2
Operating system: Centos 7
Description:
To reproduce this bug you may use following script:
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
And using WHERE true clause for target table rule not working too:
insert into tbl
select 2
except
select id from tbl where true;
table rule_stat; -- no rows
But using WHERE false clause for target table rule starts working:
insert into tbl
select 1
except
select id from tbl where false;
table rule_stat; -- new row appears
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself
2023-03-09 10:27 BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself PG Bug reporting form <noreply@postgresql.org>
@ 2023-03-09 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-10 08:18 ` Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself Hanna Yanchurevich <hyanchurevich@spotware.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2023-03-09 15:01 UTC (permalink / raw)
To: hyanchurevich@spotware.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself
2023-03-09 10:27 BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself PG Bug reporting form <noreply@postgresql.org>
2023-03-09 15:01 ` Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-03-10 08:18 ` Hanna Yanchurevich <hyanchurevich@spotware.com>
2023-03-10 14:42 ` Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Hanna Yanchurevich @ 2023-03-10 08:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
On Thu, Mar 9, 2023 at 5:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself
2023-03-09 10:27 BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself PG Bug reporting form <noreply@postgresql.org>
2023-03-09 15:01 ` Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself Tom Lane <tgl@sss.pgh.pa.us>
2023-03-10 08:18 ` Re: BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself Hanna Yanchurevich <hyanchurevich@spotware.com>
@ 2023-03-10 14:42 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-03-10 14:42 UTC (permalink / raw)
To: Hanna Yanchurevich <hyanchurevich@spotware.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-03-10 14:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-09 10:27 BUG #17827: Rule on insert into table doesn't work when excepting select from the table itself PG Bug reporting form <noreply@postgresql.org>
2023-03-09 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-10 08:18 ` Hanna Yanchurevich <hyanchurevich@spotware.com>
2023-03-10 14:42 ` Tom Lane <tgl@sss.pgh.pa.us>
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