On 26/09/14 08:21, Simon Riggs wrote:
On 25 September 2014 20:11, Robert Haas <robertmh...@gmail.com> wrote:
My approach would be to insert an index tuple for that value into the
index, but with the leaf ituple marked with an xid rather than a ctid.
If someone tries to insert into the index they would see this and wait
for the inserting transaction to end. The inserting transaction would
then resolve what happens in the heap (insert/update) and later
repoint the index tuple to the inserted/updated row version. I don't
see the need for page level locking since it would definitely result
in deadlocks (e.g. SQLServer).
I think that something like this might work, but the devil is in the
details. Suppose two people try to upsert into the same table at the
same time. There's one index. If the transactions search that index
for conflicts first, neither sees any conflicting tuples, and both
proceed. That's no good. OK, so suppose each transaction inserts the
special index tuple which you mention, to lock out concurrent inserts
of that value, and then searches for already-existing conflicts. Each
sees the other's tuple, and they deadlock. That's no good, either.
The test index is unique, so our to-be-inserted value exists on only
one page, hence page locking applies while we insert it. The next
person to insert waits for the page lock and then sees the test tuple.
The page lock lasts only for the duration of the insertion of the
ituple, not for the whole operation.
Also, I think there are other cases where we think we're going to
insert, so we put the special index tuple in there, but then we decide
to update, so we don't need the promise tuple any more, but other
sessions are potentially still waiting for our XID to terminate even
though there's no conflict any more. I'm having a hard time bringing
the details of those cases to mind ATM, though.
We make the decision to INSERT or UPDATE based upon what we find in
the test index. If a value if there already, we assume its an UPDATE
and go to update the row this points to. If it has been deleted we
loop back and try again/error. If the value is not present, we insert
the test tuple and progress as an INSERT, then loop back later to set
the ctid. There is no case of "don't need promise id anymore". We
would use the PK, identity or first unique index as the test index.
There is a case where an UPSERT conflicts with an INSERT causing the
latter to abort.
Anyway, this is why we need the design more clearly exposed, so you
can tell me I'm wrong by showing me the URL of it done right.
What happens if the new value(s) of the INERT/UPDATE require the page to
be split?
I assume the mechanics of this are catered for, but how does it affect
locking & potential deadlocks?
Cheers,
Gavin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
