On Sat, Oct 23, 2010 at 9:12 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> "PostgreSQL doesn't have a good way to lock access to a key value that >> doesn't exist yet--what other databases call key range >> locking...Improvements to the index implementation are needed to allow >> this feature." > > This seems to be presuming there's a consensus that that's the way to > implement it, which is news to me. Why wouldn't we try the INSERT > first, and if we get a unique-key failure, do UPDATE instead? I am not > at all in agreement that we ought to build key range locking, nor that > it'd be particularly helpful for this problem even if we had it.
Except we *do* have range locking for the special case of inserting equal values into a unique btree index. In that case the btree insert locks the leaf page containing the conflicting key value -- effectively locking out anyone else from inserting the same key value. That lock is held until the index entry pointing to the newly inserted value is finished. That's what prevents two inserters from inserting the same key value. So the trick for MERGE on equality would be to refactor the btree api so that you could find the matching leaf page and *keep* that page locked. Then do an update against the conflicting row found there if any without ever releasing that page lock. Someone else can come along and delete the row (or have already deleted it) before the update locks it but if they do you can insert your row without worrying about anyone else inserting a conflicting entry since you're still holding a lock on the page they'll need to insert the btree entry on and have been holding it continuously for the whole operation. I'm not sure how the new exclusion constraints stuff affects this. I think they would work the same way except instead of holding a page lock on the leaf page they would store the value being edited in the in-memory array -- effectively another form of key range locking. I think the blocker with MERGE has always been that the standard is *so* general that it could apply to conditions that *aren't* covered by a unique constraint or exclusion constriant. Personally, I'm fine saying that those cases will perform poorly, locking the table, or aren't allowed and print a warning explaining exactly what exclusion constraint or btree unique index would be necessary to allow it. I think virtually every case where people will want to use it will be on a primary key anyways. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
