Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

On Tue, May 19, 2015 at 12:57 PM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains
> that a conflict_target clause is required but doesn't explain why.

Yes, for ON CONFLICT DO UPDATE, it is mandatory.

> 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.

True.

> 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.

It's trivial to modify Postgres to not require that a specific unique
index be inferred, so that you can omit the inference specification
for DO UPDATE just as you can for DO NOTHING. That would make it work
in a similar way to MySQL; whatever actually conflict was detected
would be assumed to be cause to take the alternative update path.

The only reason I can see for wanting to do this is where you're
running a migration or something, and two unique indexes are
equivalent anyway. Like maybe you have a partial index and a
non-partial index, and you're just about to drop one of them. But the
inference specification will do the right thing here anyway --
multiple unique indexes can be inferred for edge cases like this.

I have a hard time imagining why you'd ever not want to be explicit
about what to take the alternative path on for the DO UPDATE variant.
Unless perhaps you have a different UPDATE targetlist and so on
corresponding to that case, which is currently not possible -- but
then what if multiple constraints have would-be violations at the same
time? It gets awfully messy very quickly.

What do you have in mind?

--
Peter Geoghegan