Re: INSERT ON CONFLICT and partitioned tables

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Jeevan Ladhe <jeevan(dot)ladhe(at)enterprisedb(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: INSERT ON CONFLICT and partitioned tables
Date: 2017-08-03 04:24:04
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Thanks Jeevan for looking at this. See comments below.

On 2017/08/02 19:04, Jeevan Ladhe wrote:
> On Wed, Aug 2, 2017 at 10:26 AM, Amit Langote wrote:
>> The patch's job is simple:
>> - Remove the check in the parser that causes an error the moment the
>> ON CONFLICT clause is found.
>> - Fix leaf partition ResultRelInfo initialization code so that the call
>> ExecOpenIndices() specifies 'true' for speculative, so that the
>> information necessary for conflict checking will be initialized in the
>> leaf partition's ResultRelInfo
> I applied the patch on latest master sources and the patch applies cleanly.
> The documentation is built without errors.
> We do not support following syntax for 'do nothing':
> postgres=# insert into parted_conflict_test values (1, 'a') on conflict (b)
> do nothing;
> ERROR: there is no unique or exclusion constraint matching the ON CONFLICT
> specification

To nitpick, the above is not a syntax error; we *do* support the syntax to
specify conflict target even when the conflict action is DO NOTHING. The
error is emitted by the planner when it fails to find the index to cover
column 'b' that's specified as the conflict target.

> This limitation is because we do not support unique index on partitioned
> table.


> But, in that sense the following snippet of the documentation seems
> misleading:
> + will cause an error if the conflict target is specified (see
> + <xref linkend="sql-insert"> for more details). That means it's not
> + possible to specify <literal>DO UPDATE</literal> as the alternative
> + action, because it requires the conflict target to be specified.
> + On the other hand, specifying <literal>DO NOTHING</literal> as the
> + alternative action works fine.
> May be the last sentence can be rephrased as below:
> "On the other hand, specifying <literal>DO NOTHING</literal> without target
> as
> an alternative action works fine."

I have updated the text.

> Other than this patch looks good to me.

Updated patch attached.



Attachment Content-Type Size
0001-Allow-ON-CONFLICT-DO-NOTHING-on-partitioned-tables-v2.patch text/plain 5.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-08-03 04:35:51 Re: foreign table creation and NOT VALID check constraints
Previous Message Tom Lane 2017-08-03 04:15:52 Re: fixing pg_upgrade strings (was Re: pgsql: Add new files to and add translation)