Re: configuring queries for concurrent updates

On 06/24/2012 03:42 PM, Robert Poor wrote:
> Craig:
>
> On Sun, Jun 24, 2012 at 12:06 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> That [implementation of UPSERT] is incorrect; it's subject to several nasty races.
>> The best article I've seen on this is here:
>>
>>   http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
>
> You're right -- that's a thorough and lucid note.
>
> Heeding depesz's warning that advisory locks are not a GENERAL
> solution, they're appropriate for my application: my code is the only
> place where data is added to this particular table.  So advisory locks
> sound like the way to go -- I'll give that a shot.

Yep, advisory locks sound like a good choice for that situation.

True predicate locking would solve this, allowing an app to SELECT ...
FOR UPDATE records that may not yet exist. Pg doesn't do full predicate
locking - it's slow, expensive in memory etc, hard to get right, causes
deadlocks all over the place, and usually isn't what users want. Pg's
SERIALIZABLE isolation does do predicate locking, but only lightweight
ones used to detect serialization failures, not to block work from
proceeding.


--
Craig Ringer