--On 27. September 2009 14:36:45 -0400 Robert Haas <robertmh...@gmail.com>
wrote:
On Sun, Sep 27, 2009 at 11:36 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
Robert Haas <robertmh...@gmail.com> writes:
On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <t...@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
