Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}
От | Gavin Flower |
---|---|
Тема | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} |
Дата | |
Msg-id | 54249C29.3050908@archidevsys.co.nz обсуждение исходный текст |
Ответ на | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} (Simon Riggs <simon@2ndquadrant.com>) |
Список | pgsql-hackers |
On 26/09/14 08:21, Simon Riggs wrote: > On 25 September 2014 20:11, Robert Haas <robertmhaas@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
В списке pgsql-hackers по дате отправления: