Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint - Mailing list pgsql-hackers

From Geoff Winkless
Subject Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
Date
Msg-id CAEzk6fdYScp8EanLPv2Nr94HnaEuVoe7Fwk9qqtdNH2uZk=biA@mail.gmail.com
Whole thread Raw
In response to Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint  (Simon Riggs <simon@2ndQuadrant.com>)
Responses Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint  (Peter Geoghegan <pg@heroku.com>)
List pgsql-hackers
On 19 May 2015 at 20:11, Simon Riggs <simon@2ndquadrant.com> wrote:
I'm sure we'll be asked these questions many times. 

Can you comment on whether the docs are sufficiently detailed to explain this answer?
Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains that a conflict_target clause is required but doesn't explain why.

It _does_ make clear that multiple UPDATEs to the same row are not allowed, but that in itself doesn't automatically restrict the use of multiple constraint targets; I could easily INSERT a set of values that would trigger that failure with just one constraint target.

http://www.postgresql.org/docs/devel/static/sql-insert.html talks about how MySQL's ON DUPLICATE can only act against the first matching row where multiple constraints match against multiple rows. I suppose if that were the case here (ie the first excluding row would stop other rows firing against the UPDATE) would break the deterministic feature, but it's not clear if that's true or not. I don't see why multiple target rows couldn't be updated based on multiple constraints, that would not in-and-of-itself break determinism.

If I'm missing the obvious, accept my apologies.

Geoff

pgsql-hackers by date:

Previous
From: Gavin Flower
Date:
Subject: Re: Problems with question marks in operators (JDBC, ECPG, ...)
Next
From: Robins Tharakan
Date:
Subject: Re: Per row status during INSERT .. ON CONFLICT UPDATE?