Re: Partitioning vs ON CONFLICT

On 2017/04/01 6:44, Robert Haas wrote:
> On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> In my opinion, for the very limited ON CONFLICT DO NOTHING + no
>> inference specification case, the implementation should not care about
>> the presence or absence of unique indexes within or across partitions.
>
> Hmm. That's an interesting point. The documentation says:
>
> ON CONFLICT can be used to specify an alternative action to raising a
> unique constraint or exclusion constraint violation error.
>
> And, indeed, you could get an unique constraint or exclusion error
> because of an index on the child even though it's not global to the
> partitioning hierarchy. So maybe we can support this after all, but

Oh, I see. Thanks to both of you for the explanations.

Users will be aware that a partitioned parent does not allow defining
unique/exclusion constraints that span partitions, so also that any
conflicts detected by INSERT .. ON CONFLICT DO NOTHING are only at the
level of individual leaf partitions, if there indeed are unique/exclusion
indexes defined on them.

So, if we have:

create table parent (a char, b int) partition by list (a);
create table part_a partition of parent (b unique) for values in ('a');
create table part_b partition of parent (b unique) for values in ('b');

Session-1 and session-2 both perform:

insert into parent values ('a', 1) on conflict do nothing;

Also, session-3 and session-4 both perform (possibly concurrently with
session-1 and session-2):

insert into parent values ('b', 1) on conflict do nothing;

One of session-1 or session-2 succeeds in inserting ('a', 1) into part_a
and the other does "nothing" when it finds it there already. Similarly,
one of session-3 and session-4 succeeds in inserting ('b', 1) into part_b
and the other does "nothing". If on conflict do nothing clause wasn't
there, the other session will error out. If there had not been those
unique indexes, part_a will have two instances of ('a', 1) and part_b will
have two of ('b', 1), irrespective of whether the on conflict do nothing
clause was specified.

Since nowhere has the user asked to ensure unique(b) across partitions by
defining the same on parent, this seems just fine. But one question to
ask may be whether that will *always* be the case? That is, will we take
ON CONFLICT DO NOTHING without the conflict target specification to mean
checking for conflicts on the individual leaf partition level, even in the
future when we may have global constraints?

> having messed it up once, I'm inclined to think we should postpone
> this to v11, think it over some more, and try to make sure that our
> second try doesn't crash...

Just in case, here is a patch that (re-)implements the limited support we
previously tried to implement in the commit that was just reverted.
Documentation is improved from the last version considering this
discussion and also the source code comments.

Thanks,
Amit