Re: Promise index tuples for UPSERT

On 10/01/2014 02:34 PM, Simon Riggs wrote:
> Summary of algorithm to use "promise tuples" for concurrency control
> during UPSERT
>
> 1. Perform btree search to location of key, if it exists.
> a) If an unkilled index tuple exists, we decide this is an UPDATE and
> drop straight thru to step 2
> b) If it does not exist, insert a "promise" tuple into unique index(s)
> - marked with the xid of the inserting transaction, but using the key.
> This happens while the page is locked, so it is not possible to insert
> a second promise tuple concurrently.
> Record the btree blockid on the index scan and move to step 3
> When later insert scans see the promise tuple they perform
> XactLockTableWait() and when they get control they look again for the
> key. If they find a promise tuple with an aborted xid they replace
> that value with their own xid and continue as a). Otherwise b).

XactLockTableWait() waits until the end of transaction, that's not you
want here. If the backend that inserted the promise tuple decides to not
proceed with the insertion, and removes the promise tuple, the backend
waiting on it needs to be woken up more or less immediately, not when
the transaction completes.

I had this exact same issue in my earlier patch versions, fixed it with
a new kind of heavy-weight lock, and a new field in PGPROC
(http://www.postgresql.org/message-id/52D00D2D.6030307@vmware.com). That
wasn't very pretty, but it got the job done. Some other design might be
better.

- Heikki