Re: ask for review of MERGE

Robert Haas  wrote:
> What we're talking about is what happens when there are concurrent
> table modifications in progress; and the answer is that you might
> get serialization anomalies. But we have serialization anomalies
> without MERGE, too - see the discussions around Kevin Grittner's
> SSI patch which, come to think of it, might be useful for this
> case, too.
I've been thinking about that as I read the discussion.  If both
transactions are serializable, there would tend to be a default
behavior of preventing anomalies.  ("Tend to be" because it might
actually require the addition of a few calls to predicate locking
functions from new MERGE code to be consistent with other code under
the patch.)
On the other hand, where there is a more targeted way of protecting
integrity, I've tried to keep SSI out of the way and let it function
as it has.  For example, foreign key enforcement already manages
this, so the SSI implementation intentionally ignores those reads and
writes.  From the discussion on MERGE I've been thinking that where
there is an appropriate unique index the SSI code might want to stay
out of the way, similar to foreign keys; but it might be used to
handle the cases where there is no appropriate index.  Or possibly
the predicate locking portion of it could be used in a novel way by
MERGE code to implement the MERGE logic.  The API for SSI basically
involves three types of functions:- Acquire a predicate lock on an object.- Check whether a given object is predicate
locked.-Check for rw-conflict.
 
To be useful for MERGE, that second category would probably need to
be expanded, and we might need to upgrade btree index locking to
support range locking rather than stopping at index page locks.  Dan
is planning to try this once he has sufficient benchmarks as a base
to confirm the performance impact.
> I posted an example upthread which I think demonstrates very
> clearly that MERGE will result in unavoidable serialization
> failures - so if the standard is that we mustn't have any
> serialization failures then the standard can never be met. The best
> we can hope for is that we'll detect them and roll back if they
> occur, rather than going on to commit but perhaps with some
> anomaly. And I'm pretty sure that's what KG's SSI patch is going to
> give us. So I'm not sure there's really anything to get worked up
> about here in terms of concurrency issues.
Given the standard's emphasis on data integrity and the known
concurrency issues with relational theory, I find it hard to believe
that the standard requires "no serialization failures duing merge" --
but I haven't had time to dig into the standard's specifications yet.
-Kevin