Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

On Thu, Jan 29, 2015 at 11:38 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> Simply removing IGNORE support until such time as we straighten
> that all out (9.6?) seems like the simplest solution. No need to block
> the progress of "UPSERT", since exclusion constraint support was
> only ever going to be useful for the less compelling IGNORE variant.
> What do other people think? Do you agree with my view that we should
> shelve IGNORE support for now, Heikki?

I appreciate the work you're doing and (as a user rather than a
pg-hacker) don't want to butt in but if it would be possible to allow
support for IGNORE for unique but not exclusion constraints that would
be really helpful for my own use cases, where being able to insert
from a dataset into a table containing unique constraints without
having to first check the dataset for uniqueness (within both itself
and the target table) is very useful.

It's possible that I've misunderstood anyway and that you mean purely
that exclusion constraint IGNORE should be shelved until 9.6, in which
case I apologise.

Of course if there's no way to allow unique constraint IGNORE without
exclusion constraints then just ignore me; I (along I'm sure with all
the others who are following this conversation from afar) will be
incredibly grateful for the work you've done either way.

I suppose there's no reason why we couldn't use a no-op ON CONFLICT
UPDATE anyway, but that does seem rather messy and (I imagine) would
involve rather more work (unless the optimizer were able to optimize
away the "update"? I don't know enough to be able to say if it would).

Thanks

Geoff

On Fri, Jan 30, 2015 at 6:59 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> I appreciate the work you're doing and (as a user rather than a
> pg-hacker) don't want to butt in but if it would be possible to allow
> support for IGNORE for unique but not exclusion constraints that would
> be really helpful for my own use cases, where being able to insert
> from a dataset into a table containing unique constraints without
> having to first check the dataset for uniqueness (within both itself
> and the target table) is very useful.
>
> It's possible that I've misunderstood anyway and that you mean purely
> that exclusion constraint IGNORE should be shelved until 9.6, in which
> case I apologise.

Well, the issue is that we can't really add exclusion constraints to
the IGNORE case later. So the fact that we cannot do exclusion
constraints kind of implies that we can either shelve IGNORE and maybe
look at it later, or accept that we'll never support exclusion
constraints with IGNORE. We'd then include IGNORE without exclusion
constraint support now and forever. I tend to think that we'll end up
doing the latter anyway, but I really don't want to add additional
risk of this not getting into 9.5 by arguing about that now. It
doesn't matter that much.

> I suppose there's no reason why we couldn't use a no-op ON CONFLICT
> UPDATE anyway

Right. IGNORE isn't really all that compelling for that reason. Note
that this will still lock the unmodified row, though.

--
Peter Geoghegan

On 30 January 2015 at 21:58, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Fri, Jan 30, 2015 at 6:59 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
>> I suppose there's no reason why we couldn't use a no-op ON CONFLICT
>> UPDATE anyway
>
> Right. IGNORE isn't really all that compelling for that reason. Note
> that this will still lock the unmodified row, though.

Mmmf. So I would have to make sure that my source tuples were unique
before doing the INSERT (otherwise the first ON CONFLICT UPDATE for a
tuple would block any other)? That's potentially very slow :(

When you say that you can't add exclusion constraints later, do you
mean from a coding point of view or just because people would get
confused whether exclusion constraints could be IGNOREd or not?

Geoff

On 2 February 2015 at 14:32, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> Mmmf. So I would have to make sure that my source tuples were unique
> before doing the INSERT (otherwise the first ON CONFLICT UPDATE for a
> tuple would block any other)? That's potentially very slow :(

Replying to my own message, because it occurs to me I might be being
stupid (surely not :) )

When you say "this will still lock the unmodified row" did you mean
just that it's locked to _other_ processes until commit? That would be
much less impactful.

Geoff