Horton

Thread

Commits

  1. Revert "Allow ON CONFLICT .. DO NOTHING on a partitioned table."

  2. Allow ON CONFLICT .. DO NOTHING on a partitioned table.

  1. Partitioning vs ON CONFLICT

    Thom Brown <thom@linux.com> — 2017-02-16T14:54:00Z

    Hi,

At the moment, partitioned tables have a restriction that prevents
them allowing INSERT ... ON CONFLICT ... statements:

postgres=# INSERT INTO cities SELECT 1, 'Crawley',105000 ON CONFLICT
(city_id) DO NOTHING;
ERROR:  ON CONFLICT clause is not supported with partitioned tables

Why do we have such a restriction?  And what would it take to remove it?

Thanks

Thom

  2. Re: Partitioning vs ON CONFLICT

    Simon Riggs <simon@2ndquadrant.com> — 2017-02-16T15:03:54Z

    On 16 February 2017 at 14:54, Thom Brown <thom@linux.com> wrote:
> Hi,
>
> At the moment, partitioned tables have a restriction that prevents
> them allowing INSERT ... ON CONFLICT ... statements:
>
> postgres=# INSERT INTO cities SELECT 1, 'Crawley',105000 ON CONFLICT
> (city_id) DO NOTHING;
> ERROR:  ON CONFLICT clause is not supported with partitioned tables
>
> Why do we have such a restriction?  And what would it take to remove it?

Partitioned tables don't yet support a global unique constraint that
would be required for support of ON CONFLICT processing.

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  3. Re: Partitioning vs ON CONFLICT

    Peter Geoghegan <pg@bowt.ie> — 2017-02-16T16:17:43Z

    But surely it should be possible to use DO NOTHING without inferring some
particular unique index? That's possible with an approach based on
inheritance.

--
Peter Geoghegan
(Sent from my phone)

  4. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-02-17T04:21:21Z

    On 2017/02/17 1:17, Peter Geoghegan wrote:
> But surely it should be possible to use DO NOTHING without inferring some
> particular unique index? That's possible with an approach based on
> inheritance.

Hmm.  Code after the following comment fragment in ExecInsert():

             * Do a non-conclusive check for conflicts first.

would be working on a leaf partition chosen by tuple-routing after an
insert on a partitioned table.  The leaf partitions can very well have a
unique index, which can be used for inference.  The problem however is
that infer_arbiter_indexes() in the optimizer would be looking at the root
partitioned, which cannot yet have any indexes defined on them, let alone
unique indexes.  When we develop a feature where defining an index on the
root partitioned table would create the same index on all the leaf
partitions and then extend it to support unique indexes, then we can
perhaps talk about supporting ON CONFLICT handing.  Does that make sense?

Thanks,
Amit

  5. Re: Partitioning vs ON CONFLICT

    Peter Geoghegan <pg@bowt.ie> — 2017-02-17T04:25:03Z

    On Thu, Feb 16, 2017 at 8:21 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> would be working on a leaf partition chosen by tuple-routing after an
> insert on a partitioned table.  The leaf partitions can very well have a
> unique index, which can be used for inference.  The problem however is
> that infer_arbiter_indexes() in the optimizer would be looking at the root
> partitioned, which cannot yet have any indexes defined on them, let alone
> unique indexes.  When we develop a feature where defining an index on the
> root partitioned table would create the same index on all the leaf
> partitions and then extend it to support unique indexes, then we can
> perhaps talk about supporting ON CONFLICT handing.  Does that make sense?

Yes, that makes sense, but I wasn't arguing that that should be
possible today. I was arguing that when you don't spell out an
arbiter, which ON CONFLICT DO NOTHING permits, then it should be
possible for it to just work today -- infer_arbiter_indexes() will
return immediately.

This should be just like the old approach involving inheritance, in
that that should be possible. No?

-- 
Peter Geoghegan

  6. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-02-17T05:27:40Z

    On 2017/02/17 13:25, Peter Geoghegan wrote:
> On Thu, Feb 16, 2017 at 8:21 PM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> would be working on a leaf partition chosen by tuple-routing after an
>> insert on a partitioned table.  The leaf partitions can very well have a
>> unique index, which can be used for inference.  The problem however is
>> that infer_arbiter_indexes() in the optimizer would be looking at the root
>> partitioned, which cannot yet have any indexes defined on them, let alone
>> unique indexes.  When we develop a feature where defining an index on the
>> root partitioned table would create the same index on all the leaf
>> partitions and then extend it to support unique indexes, then we can
>> perhaps talk about supporting ON CONFLICT handing.  Does that make sense?
> 
> Yes, that makes sense, but I wasn't arguing that that should be
> possible today. I was arguing that when you don't spell out an
> arbiter, which ON CONFLICT DO NOTHING permits, then it should be
> possible for it to just work today -- infer_arbiter_indexes() will
> return immediately.

I see.  It now seems that I should have realized the DO NOTHING action is
indeed supportable when I initially wrote the code that causes the current
error.

> This should be just like the old approach involving inheritance, in
> that that should be possible. No?

So we should error out only when the DO UPDATE conflict action is
requested.  Because it will require specifying conflict_target, which it's
not possible to do in case of partitioned tables.

Attached patch fixes that.  Thom, your example query should not error out
with the patch.  As discussed here, DO UPDATE cannot be supported at the
moment.

Thanks,
Amit

  7. Re: Partitioning vs ON CONFLICT

    Peter Geoghegan <pg@bowt.ie> — 2017-02-17T05:50:45Z

    On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> Attached patch fixes that.  Thom, your example query should not error out
> with the patch.  As discussed here, DO UPDATE cannot be supported at the
> moment.

Maybe you should just let infer_arbiter_indexes() fail, rather than
enforcing this directly. IIRC, that's what happens with
inheritance-based partitioning.

-- 
Peter Geoghegan

  8. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-02-17T06:47:29Z

    On 2017/02/17 14:50, Peter Geoghegan wrote:
> On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> Attached patch fixes that.  Thom, your example query should not error out
>> with the patch.  As discussed here, DO UPDATE cannot be supported at the
>> moment.
> 
> Maybe you should just let infer_arbiter_indexes() fail, rather than
> enforcing this directly. IIRC, that's what happens with
> inheritance-based partitioning.

That would be another way.  The error message emitted by
infer_arbiter_indexes() would be:

ERROR:  there is no unique or exclusion constraint matching the ON
CONFLICT specification

It does read better than what proposed patch makes
transformOnConflictClause() emit:

ERROR:  ON CONFLICT ON UPDATE clause is not supported with partitioned tables

I updated the patch.  Now it's reduced to simply removing the check in
transformInsertStmt() that prevented using *any* ON CONFLICT on
partitioned tables at all.


I don't however see why the error would *necessarily* occur in the case of
inheritance partitioning.  I mean if inserts into the root table in an
inheritance hierarchy, it's still possible to ON CONFLICT DO UPDATE using
the unique index only on that table for inference, although that's what a
user would intend to do.

create table foo (a int, b int, unique (a));
create table foo_part (like foo including indexes) inherits (foo);
insert into foo values (1, 2);

-- the following still works

insert into foo values (1, 2)
   on conflict (a) do update set b = excluded.b where excluded.a = 1;
insert into foo values (1, 2)
   on conflict (a) do update set b = excluded.b where excluded.a = 1;

As the documentation about inheritance partitioning notes, that may not be
the behavior expected for partitioned tables:

<para>
 <command>INSERT</command> statements with <literal>ON CONFLICT</>
 clauses are unlikely to work as expected, as the <literal>ON CONFLICT</>
 action is only taken in case of unique violations on the specified
 target relation, not its child relations.
</para>

With partitioned tables, since it's not possible to create index
constraints on them, ON CONFLICT DO UPDATE simply won't work.  So the
patch also updates the note in the document about partitioned tables and
ON CONFLICT.

Thanks,
Amit

  9. Re: Partitioning vs ON CONFLICT

    Robert Haas <robertmhaas@gmail.com> — 2017-03-09T14:25:29Z

    On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2017/02/17 14:50, Peter Geoghegan wrote:
>> On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>> Attached patch fixes that.  Thom, your example query should not error out
>>> with the patch.  As discussed here, DO UPDATE cannot be supported at the
>>> moment.
>>
>> Maybe you should just let infer_arbiter_indexes() fail, rather than
>> enforcing this directly. IIRC, that's what happens with
>> inheritance-based partitioning.
>
> That would be another way.  The error message emitted by
> infer_arbiter_indexes() would be:
>
> ERROR:  there is no unique or exclusion constraint matching the ON
> CONFLICT specification
>
> It does read better than what proposed patch makes
> transformOnConflictClause() emit:
>
> ERROR:  ON CONFLICT ON UPDATE clause is not supported with partitioned tables
>
> I updated the patch.  Now it's reduced to simply removing the check in
> transformInsertStmt() that prevented using *any* ON CONFLICT on
> partitioned tables at all.
>
>
> I don't however see why the error would *necessarily* occur in the case of
> inheritance partitioning.  I mean if inserts into the root table in an
> inheritance hierarchy, it's still possible to ON CONFLICT DO UPDATE using
> the unique index only on that table for inference, although that's what a
> user would intend to do.
>
> create table foo (a int, b int, unique (a));
> create table foo_part (like foo including indexes) inherits (foo);
> insert into foo values (1, 2);
>
> -- the following still works
>
> insert into foo values (1, 2)
>    on conflict (a) do update set b = excluded.b where excluded.a = 1;
> insert into foo values (1, 2)
>    on conflict (a) do update set b = excluded.b where excluded.a = 1;
>
> As the documentation about inheritance partitioning notes, that may not be
> the behavior expected for partitioned tables:
>
> <para>
>  <command>INSERT</command> statements with <literal>ON CONFLICT</>
>  clauses are unlikely to work as expected, as the <literal>ON CONFLICT</>
>  action is only taken in case of unique violations on the specified
>  target relation, not its child relations.
> </para>
>
> With partitioned tables, since it's not possible to create index
> constraints on them, ON CONFLICT DO UPDATE simply won't work.  So the
> patch also updates the note in the document about partitioned tables and
> ON CONFLICT.

This patch no longer applies.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  10. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-03-10T00:10:06Z

    On 2017/03/09 23:25, Robert Haas wrote:
> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>> I updated the patch.  Now it's reduced to simply removing the check in
>> transformInsertStmt() that prevented using *any* ON CONFLICT on
>> partitioned tables at all.
> 
> This patch no longer applies.

Rebased patch is attached.

Thanks,
Amit

  11. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-03-10T00:20:51Z

    On 2017/03/10 9:10, Amit Langote wrote:
> On 2017/03/09 23:25, Robert Haas wrote:
>> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>>> I updated the patch.  Now it's reduced to simply removing the check in
>>> transformInsertStmt() that prevented using *any* ON CONFLICT on
>>> partitioned tables at all.
>>
>> This patch no longer applies.
> 
> Rebased patch is attached.

Oops, really attached this time,

Thanks,
Amit

  12. Re: Partitioning vs ON CONFLICT

    Robert Haas <robertmhaas@gmail.com> — 2017-03-27T14:40:01Z

    On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2017/03/10 9:10, Amit Langote wrote:
>> On 2017/03/09 23:25, Robert Haas wrote:
>>> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>>>> I updated the patch.  Now it's reduced to simply removing the check in
>>>> transformInsertStmt() that prevented using *any* ON CONFLICT on
>>>> partitioned tables at all.
>>>
>>> This patch no longer applies.
>>
>> Rebased patch is attached.
>
> Oops, really attached this time,

Committed with a bit of wordsmithing of the documentation.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  13. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-03-28T00:56:26Z

    On 2017/03/27 23:40, Robert Haas wrote:
> On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> On 2017/03/10 9:10, Amit Langote wrote:
>>> On 2017/03/09 23:25, Robert Haas wrote:
>>>> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>>>>> I updated the patch.  Now it's reduced to simply removing the check in
>>>>> transformInsertStmt() that prevented using *any* ON CONFLICT on
>>>>> partitioned tables at all.
>>>>
>>>> This patch no longer applies.
>>>
>>> Rebased patch is attached.
>>
>> Oops, really attached this time,
> 
> Committed with a bit of wordsmithing of the documentation.

Thanks.

Regards,
Amit

  14. Re: Partitioning vs ON CONFLICT

    Shinoda, Noriyoshi <noriyoshi.shinoda@hpe.com> — 2017-03-30T01:30:31Z

    Hello, 

I tried this feature using most recently snapshot. In case of added constraint PRIMARY KEY for partition table, INSERT ON CONFLICT DO NOTHING statement failed with segmentaion fault.
If the primary key constraint was not created on the partition, this statement executed successfully.

- Test
postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1p1 PARTITION OF part1 FOR VALUES FROM (100) TO (200) ;
CREATE TABLE
postgres=> ALTER TABLE part1p1 ADD CONSTRAINT pk_part1p1 PRIMARY KEY (c1) ;
ALTER TABLE
postgres=> INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q

- Part of data/log/postgresql.log file 
2017-03-30 10:20:09.161 JST [12323] LOG:  server process (PID 12337) was terminated by signal 11: Segmentation fault
2017-03-30 10:20:09.161 JST [12323] DETAIL:  Failed process was running: INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ;
2017-03-30 10:20:09.161 JST [12323] LOG:  terminating any other active server processes
2017-03-30 10:20:09.163 JST [12345] FATAL:  the database system is in recovery mode
2017-03-30 10:20:09.164 JST [12329] WARNING:  terminating connection because of crash of another server process
2017-03-30 10:20:09.164 JST [12329] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

- Environment
OS: Red Hat Enterprise Linux 7 Update 1 (x86-64) 
Snapshot: 2017-03-29 20:30:05 with default configure.

Best Regards,

--
Noriyoshi Shinoda

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Langote
Sent: Tuesday, March 28, 2017 9:56 AM
To: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>; Simon Riggs <simon@2ndquadrant.com>; PostgreSQL Hackers <pgsql-hackers@postgresql.org>; Thom Brown <thom@linux.com>
Subject: Re: [HACKERS] Partitioning vs ON CONFLICT

On 2017/03/27 23:40, Robert Haas wrote:
> On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote 
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> On 2017/03/10 9:10, Amit Langote wrote:
>>> On 2017/03/09 23:25, Robert Haas wrote:
>>>> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>>>>> I updated the patch.  Now it's reduced to simply removing the 
>>>>> check in
>>>>> transformInsertStmt() that prevented using *any* ON CONFLICT on 
>>>>> partitioned tables at all.
>>>>
>>>> This patch no longer applies.
>>>
>>> Rebased patch is attached.
>>
>> Oops, really attached this time,
> 
> Committed with a bit of wordsmithing of the documentation.

Thanks.

Regards,
Amit

  15. Re: Partitioning vs ON CONFLICT

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-03-30T09:02:51Z

    This should be added to the open items list. I am not able to add it
myself, as I don't have "editor" privileges on open items wiki. I have
requested for those privileges.

On Thu, Mar 30, 2017 at 7:00 AM, Shinoda, Noriyoshi
<noriyoshi.shinoda@hpe.com> wrote:
> Hello,
>
> I tried this feature using most recently snapshot. In case of added constraint PRIMARY KEY for partition table, INSERT ON CONFLICT DO NOTHING statement failed with segmentaion fault.
> If the primary key constraint was not created on the partition, this statement executed successfully.
>
> - Test
> postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ;
> CREATE TABLE
> postgres=> CREATE TABLE part1p1 PARTITION OF part1 FOR VALUES FROM (100) TO (200) ;
> CREATE TABLE
> postgres=> ALTER TABLE part1p1 ADD CONSTRAINT pk_part1p1 PRIMARY KEY (c1) ;
> ALTER TABLE
> postgres=> INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ;
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !> \q
>
> - Part of data/log/postgresql.log file
> 2017-03-30 10:20:09.161 JST [12323] LOG:  server process (PID 12337) was terminated by signal 11: Segmentation fault
> 2017-03-30 10:20:09.161 JST [12323] DETAIL:  Failed process was running: INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ;
> 2017-03-30 10:20:09.161 JST [12323] LOG:  terminating any other active server processes
> 2017-03-30 10:20:09.163 JST [12345] FATAL:  the database system is in recovery mode
> 2017-03-30 10:20:09.164 JST [12329] WARNING:  terminating connection because of crash of another server process
> 2017-03-30 10:20:09.164 JST [12329] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
>
> - Environment
> OS: Red Hat Enterprise Linux 7 Update 1 (x86-64)
> Snapshot: 2017-03-29 20:30:05 with default configure.
>
> Best Regards,
>
> --
> Noriyoshi Shinoda
>
> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Langote
> Sent: Tuesday, March 28, 2017 9:56 AM
> To: Robert Haas <robertmhaas@gmail.com>
> Cc: Peter Geoghegan <pg@bowt.ie>; Simon Riggs <simon@2ndquadrant.com>; PostgreSQL Hackers <pgsql-hackers@postgresql.org>; Thom Brown <thom@linux.com>
> Subject: Re: [HACKERS] Partitioning vs ON CONFLICT
>
> On 2017/03/27 23:40, Robert Haas wrote:
>> On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>> On 2017/03/10 9:10, Amit Langote wrote:
>>>> On 2017/03/09 23:25, Robert Haas wrote:
>>>>> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>>>>>> I updated the patch.  Now it's reduced to simply removing the
>>>>>> check in
>>>>>> transformInsertStmt() that prevented using *any* ON CONFLICT on
>>>>>> partitioned tables at all.
>>>>>
>>>>> This patch no longer applies.
>>>>
>>>> Rebased patch is attached.
>>>
>>> Oops, really attached this time,
>>
>> Committed with a bit of wordsmithing of the documentation.
>
> Thanks.
>
> Regards,
> Amit
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers



-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  16. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-03-30T09:13:08Z

    On 2017/03/30 18:02, Ashutosh Bapat wrote:
> This should be added to the open items list. I am not able to add it
> myself, as I don't have "editor" privileges on open items wiki. I have
> requested for those privileges.

I am going to shortly, after I reply to Shinoda-san's report.  While the
crash can be fixed with a simple patch, I think we need to consider a
bigger question of whether ON CONFLICT processing on leaf partitions
should really occur.  Commit 8355a011a0 enabled specifying ON CONFLICT DO
NOTHING on when inserting into a partitioned root table, but I think we
might need to reconsider.

Thanks,
Amit

  17. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-03-30T09:54:04Z

    Shinoda-san,

Thanks a lot for testing.

On 2017/03/30 10:30, Shinoda, Noriyoshi wrote:
> Hello, 
> 
> I tried this feature using most recently snapshot. In case of added constraint PRIMARY KEY for partition table, INSERT ON CONFLICT DO NOTHING statement failed with segmentaion fault.
> If the primary key constraint was not created on the partition, this statement executed successfully.
> 
> - Test
> postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ;
> CREATE TABLE
> postgres=> CREATE TABLE part1p1 PARTITION OF part1 FOR VALUES FROM (100) TO (200) ;
> CREATE TABLE
> postgres=> ALTER TABLE part1p1 ADD CONSTRAINT pk_part1p1 PRIMARY KEY (c1) ;
> ALTER TABLE
> postgres=> INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ;
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !> \q

I found out why the crash occurs, but while I was trying to fix it, I
started growing doubtful about the way this is being handled currently.

Patch to fix the crash would be to pass 'true' instead of 'false' for
speculative when ExecSetupPartitionTupleRouting() calls ExecOpenIndices()
on leaf partitions.  That will initialize the information needed when
ExecInsert() wants check for conflicts using the constraint-enforcing
indexes.  If we do initialize the speculative insertion info (which will
fix the crash), ExecCheckIndexConstraints() will be called on a given leaf
partition's index to check if there is any conflict.  But since the insert
was performed on the root table, conflicts should be checked across all
the partitions, which won't be the case.  Even though the action is
NOTHING, the check for conflicts still uses only that one leaf partition's
index, which seems insufficient.

Commit 8355a011a0 enabled specifying ON CONFLICT DO NOTHING on when
inserting into a partitioned root table, but given the above, I think we
might need to reconsider it.

Thanks,
Amit

  18. Re: Partitioning vs ON CONFLICT

    Robert Haas <robertmhaas@gmail.com> — 2017-03-31T20:47:28Z

    On Thu, Mar 30, 2017 at 5:54 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> I found out why the crash occurs, but while I was trying to fix it, I
> started growing doubtful about the way this is being handled currently.
>
> Patch to fix the crash would be to pass 'true' instead of 'false' for
> speculative when ExecSetupPartitionTupleRouting() calls ExecOpenIndices()
> on leaf partitions.  That will initialize the information needed when
> ExecInsert() wants check for conflicts using the constraint-enforcing
> indexes.  If we do initialize the speculative insertion info (which will
> fix the crash), ExecCheckIndexConstraints() will be called on a given leaf
> partition's index to check if there is any conflict.  But since the insert
> was performed on the root table, conflicts should be checked across all
> the partitions, which won't be the case.  Even though the action is
> NOTHING, the check for conflicts still uses only that one leaf partition's
> index, which seems insufficient.
>
> Commit 8355a011a0 enabled specifying ON CONFLICT DO NOTHING on when
> inserting into a partitioned root table, but given the above, I think we
> might need to reconsider it.

It seems obvious in retrospect that the commit in question was not
quite up to the mark, considering that it didn't even update the
comment here:

        /*
         * Open partition indices (remember we do not support ON CONFLICT in
         * case of partitioned tables, so we do not need support information
         * for speculative insertion)
         */

Part of the question here is definitional.  Peter rightly pointed out
upthread that we support INSERT .. ON CONFLICT in an inheritance
situation, but that is different, because it infers whether there is a
conflict in the particular child into which you are trying to insert,
not whether there is a conflict across the whole hierarchy.  More or
less by definition, trying to insert into the room of the partitioning
hierarchy is a different beast: it should consider uniqueness across
the whole hierarchy in determining whether there is a conflict and, as
Simon pointed out in the second email on the thread, we lack a
mechanism to do that.  If somebody wants to consider only conflicts
within a specific partition, they can use INSERT .. ON CONFLICT with
the name of that partition, and that'll work fine; if they target the
parent, that should really apply globally to the hierarchy, which we
can't support.

So I think you (Amit) are right, and we should revert this commit.  We
can try again to make this work in a future release once we've had a
chance to think about it some more.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  19. Re: Partitioning vs ON CONFLICT

    Peter Geoghegan <pg@bowt.ie> — 2017-03-31T21:33:01Z

    On Fri, Mar 31, 2017 at 4:47 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>         /*
>          * Open partition indices (remember we do not support ON CONFLICT in
>          * case of partitioned tables, so we do not need support information
>          * for speculative insertion)
>          */
>
> Part of the question here is definitional.  Peter rightly pointed out
> upthread that we support INSERT .. ON CONFLICT in an inheritance
> situation, but that is different, because it infers whether there is a
> conflict in the particular child into which you are trying to insert,
> not whether there is a conflict across the whole hierarchy.

I would say that it doesn't infer anything at all, in the sense that
infer_arbiter_indexes() returns very early. It's then implied that
whatever constraint index OIDs that the executor later happens to find
will have speculative insertions. The optimizer doesn't try to predict
what that will look like within the executor, or even on a foreign
postgres_fdw server in the case of foreign tables. Foreign table
indexes are not known to the local installation, which is one reason
for this. You could INSERT ... ON CONFLICT DO NOTHING (no inference
specification) into an ordinary table with no indexes, and that also
works fine. (It's just silly.)

> More or
> less by definition, trying to insert into the room of the partitioning
> hierarchy is a different beast: it should consider uniqueness across
> the whole hierarchy in determining whether there is a conflict and, as
> Simon pointed out in the second email on the thread, we lack a
> mechanism to do that.

In my opinion, for the very limited ON CONFLICT DO NOTHING + no
inference specification case, the implementation should not care about
the presence or absence of unique indexes within or across partitions.
It might be sloppy for an application developer to do a whole lot of
this, but that's not a judgement I think we can make for them.

I don't feel strongly about this, though.

-- 
Peter Geoghegan

  20. Re: Partitioning vs ON CONFLICT

    Robert Haas <robertmhaas@gmail.com> — 2017-03-31T21:44:39Z

    On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> In my opinion, for the very limited ON CONFLICT DO NOTHING + no
> inference specification case, the implementation should not care about
> the presence or absence of unique indexes within or across partitions.

Hmm.  That's an interesting point.  The documentation says:

ON CONFLICT can be used to specify an alternative action to raising a
unique constraint or exclusion constraint violation error.

And, indeed, you could get an unique constraint or exclusion error
because of an index on the child even though it's not global to the
partitioning hierarchy.  So maybe we can support this after all, but
having messed it up once, I'm inclined to think we should postpone
this to v11, think it over some more, and try to make sure that our
second try doesn't crash...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  21. Re: Partitioning vs ON CONFLICT

    Peter Geoghegan <pg@bowt.ie> — 2017-03-31T22:26:22Z

    On Fri, Mar 31, 2017 at 5:44 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> And, indeed, you could get an unique constraint or exclusion error
> because of an index on the child even though it's not global to the
> partitioning hierarchy.  So maybe we can support this after all, but
> having messed it up once, I'm inclined to think we should postpone
> this to v11, think it over some more, a

Fine by me.

-- 
Peter Geoghegan

  22. Re: Partitioning vs ON CONFLICT

    Rukh Meski <rukh.meski@gmail.com> — 2017-04-01T12:35:22Z

    On Fri, Mar 31, 2017 at 11:44 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan <pg@bowt.ie> wrote:
>> In my opinion, for the very limited ON CONFLICT DO NOTHING + no
>> inference specification case, the implementation should not care about
>> the presence or absence of unique indexes within or across partitions.
>
> Hmm.  That's an interesting point.  The documentation says:
>
> ON CONFLICT can be used to specify an alternative action to raising a
> unique constraint or exclusion constraint violation error.
>
> And, indeed, you could get an unique constraint or exclusion error
> because of an index on the child even though it's not global to the
> partitioning hierarchy.  So maybe we can support this after all, but
> having messed it up once, I'm inclined to think we should postpone
> this to v11, think it over some more, and try to make sure that our
> second try doesn't crash...

Naturally this means that the partitioning work will be reverted as
well, since we have a consensus that new features shouldn't make
preexisting ones worse. It's a shame, since I was really hoping to see
it in 10.0.

♜

  23. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-04-03T10:28:36Z

    On 2017/04/01 6:44, Robert Haas wrote:
> On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan <pg@bowt.ie> wrote:
>> In my opinion, for the very limited ON CONFLICT DO NOTHING + no
>> inference specification case, the implementation should not care about
>> the presence or absence of unique indexes within or across partitions.
> 
> Hmm.  That's an interesting point.  The documentation says:
> 
> ON CONFLICT can be used to specify an alternative action to raising a
> unique constraint or exclusion constraint violation error.
> 
> And, indeed, you could get an unique constraint or exclusion error
> because of an index on the child even though it's not global to the
> partitioning hierarchy.  So maybe we can support this after all, but

Oh, I see.  Thanks to both of you for the explanations.

Users will be aware that a partitioned parent does not allow defining
unique/exclusion constraints that span partitions, so also that any
conflicts detected by INSERT .. ON CONFLICT DO NOTHING are only at the
level of individual leaf partitions, if there indeed are unique/exclusion
indexes defined on them.

So, if we have:

create table parent (a char, b int) partition by list (a);
create table part_a partition of parent (b unique) for values in ('a');
create table part_b partition of parent (b unique) for values in ('b');

Session-1 and session-2 both perform:

insert into parent values ('a', 1) on conflict do nothing;

Also, session-3 and session-4 both perform (possibly concurrently with
session-1 and session-2):

insert into parent values ('b', 1) on conflict do nothing;

One of session-1 or session-2 succeeds in inserting ('a', 1) into part_a
and the other does "nothing" when it finds it there already.  Similarly,
one of session-3 and session-4 succeeds in inserting ('b', 1) into part_b
and the other does "nothing".  If on conflict do nothing clause wasn't
there, the other session will error out.  If there had not been those
unique indexes, part_a will have two instances of ('a', 1) and part_b will
have two of ('b', 1), irrespective of whether the on conflict do nothing
clause was specified.

Since nowhere has the user asked to ensure unique(b) across partitions by
defining the same on parent, this seems just fine.  But one question to
ask may be whether that will *always* be the case?  That is, will we take
ON CONFLICT DO NOTHING without the conflict target specification to mean
checking for conflicts on the individual leaf partition level, even in the
future when we may have global constraints?

> having messed it up once, I'm inclined to think we should postpone
> this to v11, think it over some more, and try to make sure that our
> second try doesn't crash...

Just in case, here is a patch that (re-)implements the limited support we
previously tried to implement in the commit that was just reverted.
Documentation is improved from the last version considering this
discussion and also the source code comments.

Thanks,
Amit

  24. Re: Partitioning vs ON CONFLICT

    Robert Haas <robertmhaas@gmail.com> — 2017-08-01T01:52:50Z

    On Mon, Apr 3, 2017 at 6:28 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> Since nowhere has the user asked to ensure unique(b) across partitions by
> defining the same on parent, this seems just fine.  But one question to
> ask may be whether that will *always* be the case?  That is, will we take
> ON CONFLICT DO NOTHING without the conflict target specification to mean
> checking for conflicts on the individual leaf partition level, even in the
> future when we may have global constraints?

No.  We'll take it to mean that there is no conflict with any unique
constraint we're able to declare.  Currently, that means a
partition-local unique constraint because that's all there is.  It
will include any new things added in the future.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  25. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-08-01T04:26:01Z

    On 2017/08/01 10:52, Robert Haas wrote:
> On Mon, Apr 3, 2017 at 6:28 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> Since nowhere has the user asked to ensure unique(b) across partitions by
>> defining the same on parent, this seems just fine.  But one question to
>> ask may be whether that will *always* be the case?  That is, will we take
>> ON CONFLICT DO NOTHING without the conflict target specification to mean
>> checking for conflicts on the individual leaf partition level, even in the
>> future when we may have global constraints?
> 
> No.  We'll take it to mean that there is no conflict with any unique
> constraint we're able to declare.  Currently, that means a
> partition-local unique constraint because that's all there is.  It
> will include any new things added in the future.

So is the latest patch posted upthread to process ON CONFLICT DO NOTHING
using locally-defined unique indexes on leaf partitions something to consider?

Maybe, not until we have cascading index definition working [1]?

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/c8fe4f6b-ff46-aae0-89e3-e936a35f0cfd%40postgrespro.ru

  26. Re: Partitioning vs ON CONFLICT

    Robert Haas <robertmhaas@gmail.com> — 2017-08-01T19:02:34Z

    On Tue, Aug 1, 2017 at 12:26 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> So is the latest patch posted upthread to process ON CONFLICT DO NOTHING
> using locally-defined unique indexes on leaf partitions something to consider?

Yeah, for v11.

> Maybe, not until we have cascading index definition working [1]?

Not sure what that has to do with it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  27. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-08-02T00:31:11Z

    On 2017/08/02 4:02, Robert Haas wrote:
> On Tue, Aug 1, 2017 at 12:26 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> So is the latest patch posted upthread to process ON CONFLICT DO NOTHING
>> using locally-defined unique indexes on leaf partitions something to consider?
> 
> Yeah, for v11.

OK.

>> Maybe, not until we have cascading index definition working [1]?
> 
> Not sure what that has to do with it.

Hmm, scratch that.  I was thinking that if all partitions had uniformly
defined (unique) indexes, the behavior on specifying on conflict do
nothing would be consistent across all partitions, but I guess that's not
a really big win or anything.

Thanks,
Amit

  28. Re: Partitioning vs ON CONFLICT

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2017-08-02T01:53:48Z

    On 2017/08/02 9:31, Amit Langote wrote:
> On 2017/08/02 4:02, Robert Haas wrote:
>> On Tue, Aug 1, 2017 at 12:26 AM, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>> So is the latest patch posted upthread to process ON CONFLICT DO NOTHING
>>> using locally-defined unique indexes on leaf partitions something to consider?
>>
>> Yeah, for v11.
> 
> OK.

Will stick this into the next CF.

Thanks,
Amit