Thread: BUG #5081: ON INSERT rule does not work correctly
The following bug has been logged online:
Bug reference: 5081
Logged by: Stefan
Email address: sb@drbott.de
PostgreSQL version: 8.3.7
Operating system: FreeBSD 7.2
Description: ON INSERT rule does not work correctly
Details:
I'm trying to implement an "insert_or_update" rule which should check
whether a record with the same id already exists and if so, a UPDATE command
should be issued instead.
The problem is that if it is no record in the table, it seems that first the
INSERT command is issued and after that the UPDATE command is issued, too.
Here is the SQL code to reproduce:
create table t_test (
count bigint,
uid character varying(20)
);
ALTER TABLE ONLY t_test ADD CONSTRAINT t_test_pkey PRIMARY KEY (uid);
CREATE OR REPLACE RULE insert_or_update AS ON INSERT TO t_test WHERE (EXISTS
(SELECT true AS bool FROM t_test WHERE t_test.uid = new.uid)) DO INSTEAD
UPDATE t_test SET "count" = t_test."count" + new."count" WHERE t_test.uid =
new.uid;
insert into t_test VALUES (1, 'sb');
select * from t_test;
In this case, the SELECT should show a value of 1 for column "count", but it
shows 2.
Best Regards,
Stefan Baehring
"Stefan" <sb@drbott.de> writes:
> The problem is that if it is no record in the table, it seems that first the
> INSERT command is issued and after that the UPDATE command is issued, too.
Well, yeah. That's exactly how it's documented to work: an ON INSERT
rule is executed after the INSERT proper.
You could maybe make this work with a BEFORE INSERT trigger.
regards, tom lane
On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Stefan" <sb@drbott.de> writes: >> The problem is that if it is no record in the table, it seems that first= the >> INSERT command is issued and after that the UPDATE command is issued, to= o. > > Well, yeah. =A0That's exactly how it's documented to work: an ON INSERT > rule is executed after the INSERT proper. I'm confused. DO INSTEAD doesn't mean DO INSTEAD? > You could maybe make this work with a BEFORE INSERT trigger. I'm not sure you can make it reliable though. ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, yeah. That's exactly how it's documented to work: an ON INSERT
>> rule is executed after the INSERT proper.
> I'm confused. DO INSTEAD doesn't mean DO INSTEAD?
It does. What it doesn't mean is "IF ... THEN ... ELSE ...".
The OP's rule actually works more like
if (!(EXISTS ...))
INSERT ...
if ((EXISTS ...))
UPDATE ...
>> You could maybe make this work with a BEFORE INSERT trigger.
> I'm not sure you can make it reliable though.
Concurrent inserts make things even more interesting, yes; but the rule
had no hope of handling that anyway.
regards, tom lane
On Sun, Sep 27, 2009 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Well, yeah. =A0That's exactly how it's documented to work: an ON INSERT >>> rule is executed after the INSERT proper. > >> I'm confused. =A0DO INSTEAD doesn't mean DO INSTEAD? > > It does. =A0What it doesn't mean is "IF ... THEN ... ELSE ...". > The OP's rule actually works more like > > =A0 =A0 =A0 =A0if (!(EXISTS ...)) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0INSERT ... > > =A0 =A0 =A0 =A0if ((EXISTS ...)) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0UPDATE ... <reads section 36.3 of the fine manual> OK, I get it now. >>> You could maybe make this work with a BEFORE INSERT trigger. > >> I'm not sure you can make it reliable though. > > Concurrent inserts make things even more interesting, yes; but the rule > had no hope of handling that anyway. OK. Sometimes when I've needed to do this I've written a PL/pgsql function that tries the insert and then fails over to an UPDATE if the INSERT fails due to a unique-violation. I'm not sure that's 100% robust either, though, unless using serializable mode. ...Robert
--On 27. September 2009 14:36:45 -0400 Robert Haas <robertmhaas@gmail.com> wrote: > On Sun, Sep 27, 2009 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >>> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>>> Well, yeah. That's exactly how it's documented to work: an ON INSERT >>>> rule is executed after the INSERT proper. >> >>> I'm confused. DO INSTEAD doesn't mean DO INSTEAD? >> >> It does. What it doesn't mean is "IF ... THEN ... ELSE ...". >> The OP's rule actually works more like >> >> if (!(EXISTS ...)) >> INSERT ... >> >> if ((EXISTS ...)) >> UPDATE ... > > <reads section 36.3 of the fine manual> > > OK, I get it now. I think the manual is a bit confusing at this point: "For ON INSERT rules, the original query (if not suppressed by INSTEAD) is done before any actions added by rules." I read this like "...if it suppressed, the INSERT in not done..." But no problem, will try to work around this with a procedure. > >>>> You could maybe make this work with a BEFORE INSERT trigger. >> >>> I'm not sure you can make it reliable though. >> >> Concurrent inserts make things even more interesting, yes; but the rule >> had no hope of handling that anyway. > > OK. > > Sometimes when I've needed to do this I've written a PL/pgsql function > that tries the insert and then fails over to an UPDATE if the INSERT > fails due to a unique-violation. I'm not sure that's 100% robust > either, though, unless using serializable mode. > > ...Robert *** www.drbott.info. Dr. Bott KG, D-07426 Oberhain, Germany, HRA Jena 201367
Hi, You can use a trigger before insert and a pl/pgsql function that goes: BEGIN UPDATE table SET ... WHERE pk=NEW.pk IF FOUND THEN RETURN NULL; ELSE RETURN NEW; END IF; END; Jacques. At 19:36 27/09/2009, Robert Haas wrote: >Sometimes when I've needed to do this I've written a PL/pgsql function >that tries the insert and then fails over to an UPDATE if the INSERT >fails due to a unique-violation. I'm not sure that's 100% robust >either, though, unless using serializable mode. > >...Robert > >-- >Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-bugs
On Mon, Sep 28, 2009 at 10:12 AM, Jacques Caron <jc@oxado.com> wrote: > Hi, > > You can use a trigger before insert and a pl/pgsql function that goes: > > BEGIN > =A0UPDATE table SET ... WHERE pk=3DNEW.pk > =A0IF FOUND THEN > =A0RETURN NULL; > =A0ELSE > =A0RETURN NEW; > =A0END IF; > END; > > Jacques. That seems about right. It's possible that the UPDATE could fail to find any rows but the INSERT could still fail due to a duplicate key violation (consider, for example, doing inserts for the same not-previously-exstant PK value in two different transactions, and then trying to commit each one). But I don't believe there's any way to completely prevent that sort of problem in a concurrent environment short of serializing all work behind a table lock, so the best we can do is try to make errors rare and avoid silent failures, which this should do. ...Robert