On 03.01.2011 18:49, Simon Riggs wrote:
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 minute
or RAISE error }
Otherwise we will silently drop rows. Throwing an error every time isn't
useful behaviour.
An ELSE clause would be nice, but it's not related to the question at
hand. Only some serialization anomalities result in a row that matches
neither WHEN MATCHED nor WHEN NOT MATCHED. Others result in a duplicate
key exception, for example.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers