From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Jacques Caron <jc(at)oxado(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 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-28 15:15:38 |
Message-ID: | 603c8f070909280815q2ad6afdbh69fc076a2ef1a0f4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-09-28 15:31:04 | Re: to_char issue? |
Previous Message | Tom Lane | 2009-09-28 15:11:13 | Re: to_char issue? |