Partitioning vs ON CONFLICT

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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)

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers