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

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