Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdeR9-0000HT-L0 for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Nov 2020 19:08:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kdeR8-0007Hs-Jm for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Nov 2020 19:08:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdeR8-0007Hl-Dl for pgsql-bugs@lists.postgresql.org; Fri, 13 Nov 2020 19:08:42 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdeR6-0004Oh-EG for pgsql-bugs@lists.postgresql.org; Fri, 13 Nov 2020 19:08:41 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0ADJ8dkg393979; Fri, 13 Nov 2020 14:08:39 -0500 From: Tom Lane To: Andy S cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition In-reply-to: References: <16714-dbf5759eba70bfe4@postgresql.org> <288078.1605283397@sss.pgh.pa.us> Comments: In-reply-to Andy S message dated "Fri, 13 Nov 2020 21:30:39 +0300" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <393977.1605294519.1@sss.pgh.pa.us> Date: Fri, 13 Nov 2020 14:08:39 -0500 Message-ID: <393978.1605294519@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Andy S writes: > That's then either a docs bug. The documentation states: >> For each individual row proposed for insertion, either the insertion >> proceeds, or, if an *arbiter* constraint or index specified by >> *conflict_target* is violated, the alternative *conflict_action* is taken Yeah, but you did not specify a valid arbiter constraint. ON CONFLICT is not a get-out-of-jail-free card; it will not trap any error whatsoever, only a detected unique-constraint violation. What you probably want here is to declare (part_key1, part_key4) as the primary key of the partitioned table, and use that in the ON CONFLICT spec. That will save you from having to make constraints on the individual partitions, too. regards, tom lane