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

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