On Thu, Sep 25, 2014 at 12:11 PM, Robert Haas <robertmh...@gmail.com> wrote: > 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.
I'm very glad that you share my concern about deadlocks like this. > 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. Well, you might have a promise tuple in a unique index on attributes not appearing in the UPDATE's targetlist, for one. You have the other session waiting (doesn't have to be an upserter) just because we *thought about* inserting a value as part of an upsert. That's pretty bad. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
