Re: UPSERT strange behavior

On 08/25/2016 10:08 PM, Peter Geoghegan wrote:
> On Thu, Aug 25, 2016 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think the point is that given the way he's set up the test case,
>> there should be no duplicate violation in the plain unique index
>> unless there is one in the arbiter index. So assuming that INSERT
>> tests the arbiter indexes first, there shouldn't be an error.
>> Maybe it doesn't do that, but it seems like it would be a good idea
>> if it did.
> Oh, yeah. This is arguably an example of inference failing to infer
> multiple unique indexes as arbiters. Inference could, in principle,
> recognize that the second unique index is equivalent to the first, but
> doesn't. (I don't think that it matters which order anything is tested
> in, though, because not finding a dup value in the arbiter index does
> not guarantee that there won't be one in the other index. There is no
> locking when no conflict is initially found, and so no guarantees
> here.)
>
> Anyway, I don't have a lot of sympathy for this point of view, because
> the scenario is completely contrived. You have to draw the line
> somewhere.
>
I do not think that this scenario is completely contrived: the cases when a table has more than one primary key are quite common.
For example, "user" may have unique e-mail address, phone number and login.
Also, as far as I know, this is not an artificial example, but real case taken from customers application...

I am not sure weather it's really bug or feature, but the user's intention was obvious: locate record by one of the unique keys and if such record already exists,
then increment counter (do update instead of insert). But there are also good arguments why upsert may report conflict in this case...

If such UPSERT behavior is assumed to be correct, what is the best workaround for the problem if we really need to have to separate indexes and want to enforce unique constraint for both keys?

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company