Re: INSERT ON CONFLICT and partitioned tables

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.

Yes.

> 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.

Thanks,
Amit

[1]
https://www.postgresql.org/docs/devel/static/sql-insert.html#sql-on-conflict