From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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: Thu, 09 Mar 2023 10:01:48 -0500
Message-ID: <377748.1678374108@sss.pgh.pa.us> (raw)
In-Reply-To: <17827-1dfd5ad9af280484@postgresql.org>
References: <17827-1dfd5ad9af280484@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
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: <377748.1678374108@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