Skip site navigation (1) Skip section navigation (2)

Re: BUG #5081: ON INSERT rule does not work correctly

From: Stefan Bähring <Stefan(dot)Baehring(at)drbott(dot)de>
To: Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5081: ON INSERT rule does not work correctly
Date: 2009-09-28 08:21:55
Message-ID: E8FCB7DD85B1E7E98A04E5DB@stefans-macbook-pro-2.local (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs

--On 27. September 2009 14:36:45 -0400 Robert Haas <robertmhaas(at)gmail(dot)com> 

> 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

*** Dr. Bott KG, D-07426 Oberhain, Germany, HRA Jena 201367

In response to

pgsql-bugs by date

Next:From: Bernt Marius JohnsenDate: 2009-09-28 09:04:06
Subject: BUG #5084: Query gives different number of rows depending on ORDER BY
Previous:From: Dave PageDate: 2009-09-28 07:53:13
Subject: to_char issue?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group