On 10.11.2015 07:23, Craig Ringer wrote:
On 10 November 2015 at 01:38, Jeff Janes <jeff.ja...@gmail.com> wrote:

this would be handy in conjunction with LIMIT
(which also doesn't exist for UPDATE right now).

... and, in turn, UPDATE ... ORDER BY ..., since LIMIT without ORDER
BY is usually not a great choice.

I'd quite like to see UPDATE ... ORDER BY for deadlock avoidance
anyway. Right now doing it really reliably seems to require a SELECT
... FOR UPDATE, then an UPDATE on the SELECTed tuples only. If you're
in READ COMMITTED you can't assume the UPDATE won't see new tuples
since the SELECT so you need to supply a key-list to the UPDATE
directly or via a wCTE.

I'm constantly surprised that people don't seem to hit deadlocks
between updates more often. I guess the number of cases where
multi-row updates on overlapping but non-identical sets of rows occur
concurrently must be fairly limited in practice.

From my experience most databases are just to small. There operation finished before there could be a deadlock. Same for race conditions - most developer don't know them, because the never stumbled about them. I am matching regularly discussions if a database is already to big when holding 10.000 records in the whole cluster...

Most time it is relatively predictable if an application will hit such a problem or not. But of course you should make it right.

Using SKIP LOCKED in a wCTE with an UPDATE is clunkier but not that
bad. So I don't think it's drastically important, but it would be

This is my opinion too.


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to