From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Stefan <sb(at)drbott(dot)de>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5081: ON INSERT rule does not work correctly |
Date: | 2009-09-27 18:36:45 |
Message-ID: | 603c8f070909271136y6e417111l3b212d7e53f06015@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jesper | 2009-09-27 21:29:39 | BUG #5083: Problem create account. |
Previous Message | Tom Lane | 2009-09-27 15:36:45 | Re: BUG #5081: ON INSERT rule does not work correctly |