Re: 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(at)drbott(dot)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(at)drbott(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Stefan" <sb(at)drbott(dot)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.

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(at)gmail(dot)com> writes:
> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

>>> 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(at)gmail(dot)com>
wrote:

> On Sun, Sep 27, 2009 at 11:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)postgresql(dot)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(at)oxado(dot)com> wrote:
> 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.

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