On Mon, Sep 28, 2009 at 10:12 AM, Jacques Caron <jc@oxado.com> wrote:
> Hi,
>
> You can use a trigger before insert and a pl/pgsql function that goes:
>
> BEGIN
> =A0UPDATE table SET ... WHERE pk=3DNEW.pk
> =A0IF FOUND THEN
> =A0RETURN NULL;
> =A0ELSE
> =A0RETURN NEW;
> =A0END 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