On Mon, 2011-01-03 at 18:35 +0200, Heikki Linnakangas wrote:
> On 03.01.2011 18:29, Simon Riggs wrote:
> > On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
> >
> >> It works in read committed mode, because you acquire a new snapshot
> >> after the LOCK TABLE, and anyone else who modified the table must commit
> >> before the lock is granted. In serializable mode you get a serialization
> >> error.
> >
> > If its not safe without this
> >
> > LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE
> >
> > then we should do that automatically, and document that.
>
> No we should not. The SQL standard doesn't require that, and it would
> unnecessarily restrict concurrent updates on unrelated rows in the table.
If we do that, then we definitely need a catch-all WHEN statement, so
that we can say
WHEN NOT MATCHED INSERT
WHEN MATCHED UPDATE
ELSE { INSERT into another table so we can try again in a minuteor RAISE error }
Otherwise we will silently drop rows. Throwing an error every time isn't
useful behaviour.
Of course, that then breaks the standard, just as all existing
implementations do.
-- Simon Riggs http://www.2ndQuadrant.com/books/PostgreSQL Development, 24x7 Support, Training and Services
