Re: INSERT ON CONFLICT and partitioned tables

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

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

Other than this patch looks good to me.

Regards,
Jeevan Ladhe

On Wed, Aug 2, 2017 at 10:26 AM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

> Starting a new thread for a patch I posted earlier [1] to handle ON
> CONFLICT DO NOTHING when inserting into a partitioned table. It's
> intended for PG 11 and so registered in the upcoming CF.
>
> Summary of the previous discussion and the patch for anyone interested:
>
> Currently, if an INSERT statement for a partitioned table mentions the ON
> CONFLICT clause, we error out immediately. It was implemented that way,
> because it was thought that it could not be handled with zero support for
> defining indexes on partitioned tables. Peter Geoghegan pointed out [2]
> that it's too restrictive a view.
>
> He pointed out that planner doesn't *always* expect indexes to be present
> on the table when ON CONFLICT is specified. They must be present though
> if DO UPDATE action is requested, because one would need to also specify
> the exact columns on which conflict will be checked and those must covered
> by the appropriate indexes. So, if the table is partitioned and DO UPDATE
> is specified, lack of indexes will result in an error saying that a
> suitable index is absent. DO UPDATE action cannot be supported until we
> implement the feature to define indexes on partitioned tables.
>
> OTOH, the DO NOTHING case should go through the planner without error,
> because neither any columns need to be specified nor any indexes need to
> be present covering them. So, DO NOTHING on partitioned tables might work
> after all. Conflict can only be determined using indexes, which
> partitioned tables don't allow, so how? Leaf partitions into which tuples
> are ultimately stored can have indexes defined on them, which can be used
> to check for the conflict.
>
> 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
>
> Thanks,
> Amit
>
> [1]
> https://www.postgresql.org/message-id/62be3d7a-08f6-5dcb-
> f5c8-a5b764ca96df%40lab.ntt.co.jp
>
> [2]
> https://www.postgresql.org/message-id/CAH2-Wzm10T%2B_PWVM4XO5zaknVbAXkOH9-
> JW3gRVPm1njLHck_w%40mail.gmail.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>