Postgresql insert update duplicate key

So what I've done, aside from being, as you say below, close to optimal, is in a sense defined in terms of existing, well-established abstractions. I feel it's easier to reason about the implications of holding value locks whatever the implementation for longer and across multiple operations than it is to do all this instead.

What I've done with locking is scary, but not as scary as the worst case of alternative implementations. Then do the insertion to that position. I'm certainly not opposed to making something like this work for exclusion constraints.

Certainly, I want this to be as general as possible. But I don't think that it needs to be a blocker, and I don't think we gain anything in code footprint by addressing that by being as general as possible in our approach to the basic concurrency issue. After all, we're going to have to repeat the basic pattern in multiple modules.

With exclusion constraints, we'd have to worry about a single slot proposed for insertion violating and therefore presumably obliging us to lock every row in the table. Are we going to have a mechanism for spilling a tid array potentially sized in gigabytes to disk relating to just one slot proposed for insertion? Is it principled to have that one slot project out rejects consisting of say the entire table?

Is it even useful to lock multiple rows if we can't really update them, because they'll overlap each other when all updated with the one value? These are complicated questions, and frankly I don't have the bandwidth to answer them too soon. I just want to implement a feature that there is obviously huge pent up demand for, that has in the past put Postgres at a strategic disadvantage. That's how we represent uniques They'd just do it by key, because they'd already have a booking number PK value or whatever.

Making this perform as well as possible is an important consideration. All alternative approaches that involve bloat concern me, and for reasons that I'm not sure were fully appreciated during earlier discussion on this thread: I'm worried about the worst case, not the average case.

I am worried about a so-called "thundering herd" scenario. You need something like LockTuple to arbitrate ordering, which seems complex, and like a further modularity violation. Add a comment. Active Oldest Votes. Laurenz Albe Laurenz Albe k 15 15 gold badges silver badges bronze badges. TejasTank The example is in the question. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.

Email Required, but never shown. The Overflow Blog. I'm trying to be as fair as possible, and yet the difference is huge. It's going to be really hard to beat something where the decision to try to see if we should insert or update comes so late: the decision is made as late as possible, is based on strong indicators of likely outcome, while the cost of making the wrong choice is very low.

I've addressed all of those now. Progress here is entirely blocked on waiting for review feedback. It's as if the first phase, the locking operation is an index scan with an identity crisis. It can decide to continue to be an "index scan" albeit an oddball one with an insertion scankey that using shared buffer locks prevents concurrent duplicate insertion, for very efficient uniqueness checks , or it can decide to actually insert, at the last possible moment.

The second phase is picked up with much of the work already complete from the first, so the amount of wasted work is very close to zero in all cases.