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

On 19 May 2015 at 19:59, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> On Tue, May 19, 2015 at 2:28 PM, Simon Riggs <simon(at)2ndquadrant(dot)com>
> wrote:
> > On 19 May 2015 at 17:10, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> >>
> >> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs <simon(at)2ndquadrant(dot)com>
> >> wrote:
> >> > We should allow DO UPDATE to exclude a constraint and apply a
> >> > deterministic
> >> > order to the constraints. 1. PK if it exists. 2. Replica Identity,
> when
> >> > not
> >> > PK, 3. UNIQUE constraints in name order, like triggers, so users can
> >> > define
> >> > a default evaluation order, just like they do with triggers.
> >>
> >> That seems like something way worse than just allowing it for all
> >> constraints.
> >
> >
> > I'm talking about the evaluation order; it would still match all
> > constraints, otherwise they wouldn't be constraints.
>
> But it doesn't match all constraints when a would-be conflict is
> detected.

No not all, but we can evaluate the constraints one at a time in a
consistent order.

My point is this: We do not need to explicitly specify the constraint we
wish to test to ensure that we get deterministic behaviour. So it is
possible to avoid specifying a constraint/conflict target and still get
deterministic behaviour (which is essential).

With Postgres, we want to make sure that the user has
> put thought into the condition they take that update path on, and so
> it is mandatory (it can infer multiple unique indexes, but only when
> they're basically equivalent for this purpose).
>

If I have two constraints and I think about it, I would want to be able to
specify this...

INSERT
ON CONFLICT (col1) DO UPDATE... (handle it one way)
ON CONFLICT (col2) DO UPDATE... (handle it 2nd way)

but I cannot with the current syntax.

It seems strange to force the user to think about constraint handling and
then not offer them any choices once they have done the thinking.

If the update is the same no matter which constraint is violated, why would
I need to specify the constraint? We're forcing the developer to make an
arbitrary choice between two constraints.

I actually do not feel strongly that it would be terrible to allow the
> user to omit an inference clause for the DO UPDATE variant (on the
> grounds of that being closer to MySQL). After all, we don't mandate
> that the user specifies an explicit targetlist for INSERT, and that
> seems like a footgun to me. If you want to make the case for doing
> things that way, I probably will not oppose it. FWIW, I don't think
> it's unreasonable to have a little discussion on fine points of
> semantics like that post feature-freeze.
>

We will see many people ask why they have to specify constraints explicitly.

As I've pointed out, if the underlying model changes then you now have to
explicitly recode all the SQL as well AND time that exactly so you roll out
the new code at the same time you add/change constraints. That makes it
much harder to use this feature than I would like.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services