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

From: Peter Geoghegan <pg(at)heroku(dot)com>
To: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
Date: 2015-05-19 20:12:39
Message-ID: CAM3SWZQqwB7gLZTMh2c0X5g_w0k9uE==kU2VbsYnfAMgEgC0QQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2015-05-19 20:20:25 Re: Per row status during INSERT .. ON CONFLICT UPDATE?
Previous Message Robins Tharakan 2015-05-19 20:07:04 Re: Per row status during INSERT .. ON CONFLICT UPDATE?