Re: Promise index tuples for UPSERT

On 10/06/2014 03:05 PM, Simon Riggs wrote:
> On 3 October 2014 11:54, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> wrote:
>
>> Simon's approach would actually pass that test case just fine. It inserts
>> the (promise) index tuple first, and heap tuple only after that. It will
>> fail the test case with more than one unique index, however.
>
> Please explain what you mean by "fail" here?

I meant that the test case will sometimes deadlock, and some
transactions will therefore be rolled back.

> My understanding of what you're saying is that if
>
> * we have a table with >1 unique index
> * and we update the values of the uniquely index columns (e.g. PK update)
> * on both of the uniquely indexed column sets
> then we get occaisonal deadlocks, just as we would do using current
> UPDATE/INSERT.

Right. To be precise: you don't need to update both of the columns in
the same transaction, it's enough that some of the concurrent
transactions update one column, while other transactions update the
other column.

> Is their a business use case that requires that?

I don't know. Conceivably any use case where you have two unique
constraints to begin with.

- Heikki