Re: Concurrent MERGE

Kevin,

Overall, you're missing the point: there are workarounds for all of
these things now. However, they are *workarounds*, which means that
they are awkward, expensive, and/or hard to administrate; having
predicate locks would make things much easier.

> I don't see how that can be resolved without killing something, do
> you? You would just have to replace the current deadlock with some
> other form of serialization failure. (And no, I will never give up
> the position that a deadlock *is* one of many forms of serialization
> failure.)

If you're in lock nowait mode, you could get back a "can't lock" error
message immediately rather than waiting for the procedure to time out.
There's certainly going to be an error regardless; it's a question of
how expensive it is for the application and the database server.
Deadlocks are *very* expensive, especially since our deadlock detector
doesn't always figure them out successfully (which means the deadlock
has to be resolved by the DBA). So any other type of serialization
failure or error is better than deadlocking.

> I must be missing something. Please explain how this would work
> *without* serialization failures. As far as I can see, you can
> replace deadlocks with some other form, but I don't see the point.

See above.

>> (4) Blackouts: records of type "x" aren't supposed to be created
>> during period "y to y1" or while procedure "z" is running.
>> Predicate locking can be used to prevent this more easily than
>> adding and removing a trigger.
>
> I would have thought that advisory locks covered this. In what way
> do they fall short for this use case?

Currently, I do use advisory locks for this case. However, they require
a fair amount of administrative design and monitoring overhead.

> Hmmmm.... Assuming fine enough granularity (like from an index for
> which a range could be locked to detect the conflict) adding a
> listener to the SIRead lock handling would be good for this. Well,
> as long as the transactions were serializable.

Yeah, it's that last caveat which makes SIRead locks not as flexible as
the theoretical predicate lock. Of course, any eventual actual
implemenation of predicate locks might be equally inflexible.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com