INSERT ON CONFLICT and partitioned tables

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: INSERT ON CONFLICT and partitioned tables
Date: 2017-08-02 04:56:16
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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




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


Browse pgsql-hackers by date

  From Date Subject
Next Message Rushabh Lathia 2017-08-02 05:01:40 Re: reload-through-the-top-parent switch the partition table
Previous Message Chapman Flack 2017-08-02 02:30:30 Re: Faster methods for getting SPI results