Re: Partitioning vs ON CONFLICT - Mailing list pgsql-hackers
From | Amit Langote |
---|---|
Subject | Re: Partitioning vs ON CONFLICT |
Date | |
Msg-id | 62be3d7a-08f6-5dcb-f5c8-a5b764ca96df@lab.ntt.co.jp Whole thread Raw |
In response to | [HACKERS] Partitioning vs ON CONFLICT (Thom Brown <thom@linux.com>) |
List | pgsql-hackers |
On 2017/04/01 6:44, Robert Haas wrote: > On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan <pg@bowt.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
pgsql-hackers by date: