Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

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



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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

On 2017/08/02 4:02, Robert Haas wrote:
> On Tue, Aug 1, 2017 at 12:26 AM, Amit Langote
>  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



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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Robert Haas]

On Tue, Aug 1, 2017 at 12:26 AM, Amit Langote
 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


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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

On 2017/08/01 10:52, Robert Haas wrote:
> On Mon, Apr 3, 2017 at 6:28 AM, Amit Langote
>  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



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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Robert Haas]

On Mon, Apr 3, 2017 at 6:28 AM, Amit Langote
 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


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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

On 2017/04/01 6:44, Robert Haas wrote:
> On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan  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
>From ec732ac5d8fd87f657f51a464072eb57d5f888dd Mon Sep 17 00:00:00 2001
From: amit 
Date: Mon, 3 Apr 2017 19:13:38 +0900
Subject: [PATCH] Allow ON CONFLICT DO NOTHING on partitioned tables

ON CONFLICT .. DO UPDATE still doesn't work, because it requires
specifying the conflict target.  DO NOTHING doesn't require it,
but the executor will check for conflicts within only a given
leaf partitions, if relevant constraints exist.

Specifying the conflict target makes the planner look for the
required indexes on the parent table, which are not allowed, so an
error will always be reported in that case.
---
 doc/src/sgml/ddl.sgml | 12 
 src/backend/executor/execMain.c   | 10 ++
 src/backend/parser/analyze.c  |  8 
 src/test/regress/expected/insert_conflict.out | 10 ++
 src/test/regress/sql/insert_conflict.sql  | 10 ++
 5 files changed, 34 insertions(+), 16 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 5109778196..478d7171cd 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3268,10 +3268,14 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
  
   
Using the ON CONFLICT clause with partitioned tables
-   will cause an error, because unique or exclusion constraints can only be
-   created on individual partitions.  There is no support for enforcing
-   uniqueness (or an exclusion constraint) across an entire partitioning
-   hierarchy.
+   will cause an error if the conflict target is specified (see
+for more details).  That means it's not
+   possible to specify DO UPDATE as the alternative
+   action, because it requires the conflict target to be specified.
+   On the other hand, specifying DO NOTHING as the
+   alternative action works fine.  Note that in the latter case, a unique
+   constraint (or an exclusion constraint) of the individual leaf
+   

Re: [HACKERS] Partitioning vs ON CONFLICT

[Rukh Meski]

On Fri, Mar 31, 2017 at 11:44 PM, Robert Haas  wrote:
> On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan  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.

♜


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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Peter Geoghegan]

On Fri, Mar 31, 2017 at 5:44 PM, Robert Haas  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


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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Robert Haas]

On Fri, Mar 31, 2017 at 5:33 PM, Peter Geoghegan  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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Peter Geoghegan]

On Fri, Mar 31, 2017 at 4:47 PM, Robert Haas  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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Robert Haas]

On Thu, Mar 30, 2017 at 5:54 AM, Amit Langote
 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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Ashutosh Bapat]

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.shin...@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-ow...@postgresql.org 
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Langote
> Sent: Tuesday, March 28, 2017 9:56 AM
> To: Robert Haas <robertmh...@gmail.com>
> Cc: Peter Geoghegan <p...@bowt.ie>; Simon Riggs <si...@2ndquadrant.com>; 
> PostgreSQL Hackers <pgsql-hackers@postgresql.org>; Thom Brown <t...@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...@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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Shinoda, Noriyoshi]

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-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Langote
Sent: Tuesday, March 28, 2017 9:56 AM
To: Robert Haas <robertmh...@gmail.com>
Cc: Peter Geoghegan <p...@bowt.ie>; Simon Riggs <si...@2ndquadrant.com>; 
PostgreSQL Hackers <pgsql-hackers@postgresql.org>; Thom Brown <t...@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...@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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

On 2017/03/27 23:40, Robert Haas wrote:
> On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote
>  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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Robert Haas]

On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote
 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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

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
>From a6377bf0a060676ae461314f62f8da4aac8896f8 Mon Sep 17 00:00:00 2001
From: amit 
Date: Fri, 17 Feb 2017 14:18:01 +0900
Subject: [PATCH] ON CONFLICT DO NOTHING should work with partitioned tables

Currently, a check in transformInsertStmt() prevents *any*
ON CONFLICT clause from being specified on a partitioned table,
even those specifying DO NOTHING as the alternative action.  It
is harmless to allow those, so remove that check.  It would still
not be possible to use DO UPDATE with partitioned table though,
because infer_arbiter_indexes() will eventually error out upon
failing to find a unique/exclusion constraint.  Remember it is
not at the moment possible to create these constraints on
partitioned tables.

Adds a test and updates the note in document about using ON CONFLICT
with partitioned tables.
---
 doc/src/sgml/ddl.sgml |  8 ++--
 src/backend/parser/analyze.c  |  8 
 src/test/regress/expected/insert_conflict.out | 10 ++
 src/test/regress/sql/insert_conflict.sql  | 10 ++
 4 files changed, 26 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 09b5b3ff70..de57930520 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3854,8 +3854,12 @@ ANALYZE measurement;
 
 
  
-  INSERT statements with ON CONFLICT
-  clause are currently not allowed on partitioned tables.
+  Using the ON CONFLICT clause with partitioned tables
+  will cause an error if DO UPDATE is specified as the
+  alternative action, because it requires specifying a unique or exclusion
+  constraint to determine if there is a conflict.  Currently, it is not
+  possible to create indexes on partitioned tables required to implement
+  such constraints.
  
 
 
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 3571e50aea..25699fbc4a 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -842,16 +842,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 
 	/* Process ON CONFLICT, if any. */
 	if (stmt->onConflictClause)
-	{
-		/* Bail out if target relation is partitioned table */
-		if (pstate->p_target_rangetblentry->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-	(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-	 errmsg("ON CONFLICT clause is not supported with partitioned tables")));
-
 		qry->onConflict = transformOnConflictClause(pstate,
 	stmt->onConflictClause);
-	}
 
 	/*
 	 * If we have a RETURNING clause, we need to add the target relation to
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 8d005fddd4..c90d381b34 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -786,3 +786,13 @@ select * from selfconflict;
 (3 rows)
 
 drop table selfconflict;
+-- check that the following works:
+-- insert into partitioned_table on conflict do nothing
+create table parted_conflict_test (a int, b char) partition by list (a);
+create table parted_conflict_test_1 partition of parted_conflict_test for values in (1);
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+-- however, on conflict do update not supported yet
+insert into parted_conflict_test values (1) on conflict (a) do update set b = excluded.b where excluded.a = 1;
+ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
+drop table parted_conflict_test, parted_conflict_test_1;
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index df3a9b59b5..78bffc783d 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -471,3 +471,13 @@ commit;
 select * from selfconflict;
 
 drop table selfconflict;
+
+-- check that the following works:
+-- insert into partitioned_table on conflict do nothing
+create table parted_conflict_test (a int, b char) partition by list (a);
+create table parted_conflict_test_1 partition of parted_conflict_test for values in (1);
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+-- however, on conflict do update not supported yet
+insert into parted_conflict_test values (1) on conflict (a) do update set b = excluded.b where 

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Robert Haas]

On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote
 wrote:
> On 2017/02/17 14:50, Peter Geoghegan wrote:
>> On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
>>  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:
>
> 
>  INSERT statements with ON CONFLICT
>  clauses are unlikely to work as expected, as the ON CONFLICT
>  action is only taken in case of unique violations on the specified
>  target relation, not its child relations.
> 
>
> 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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

On 2017/02/17 14:50, Peter Geoghegan wrote:
> On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
>  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:


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


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
>From 188f9e64402ce70f36e48274927fc6d5784319fa Mon Sep 17 00:00:00 2001
From: amit 
Date: Fri, 17 Feb 2017 14:18:01 +0900
Subject: [PATCH] ON CONFLICT DO NOTHING should work with partitioned tables

Currently, a check in transformInsertStmt() prevents *any*
ON CONFLICT clause from being specified on a partitioned table,
even those specifying DO NOTHING as the alternative action.  It
is harmless to allow those, so remove that check.  It would still
not be possible to use DO UPDATE with partitioned table though,
because infer_arbiter_indexes() will eventually error out upon
failing to find a unique/exclusion constraint.  Remember it is
not at the moment possible to create these constraints on
partitioned tables.

Adds a test and updates the note in document about using ON CONFLICT
with partitioned tables.
---
 doc/src/sgml/ddl.sgml |  9 ++---
 src/backend/parser/analyze.c  |  8 
 src/test/regress/expected/insert_conflict.out | 10 ++
 src/test/regress/sql/insert_conflict.sql  | 10 ++
 4 files changed, 26 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index f909242e4c..c99951a660 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3862,9 +3862,12 @@ ANALYZE measurement;
  
 
  
-  INSERT statements with ON CONFLICT
-  clause are currently not allowed on partitioned tables, that is,
-  cause error when specified.
+  Using the ON CONFLICT clause with partitioned tables
+  will cause an error if DO UPDATE is specified as the
+  alternative action, because it requires specifying a unique or exclusion
+  constraint to determine if there is a conflict.  Currently, it is not
+  possible to create indexes on partitioned tables required to implement
+  such constraints.
  
 
 
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 0f7659bb6b..a25a7c503a 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -843,16 +843,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 
 	/* Process ON CONFLICT, if any. */
 	if (stmt->onConflictClause)
-	{
-		/* Bail out if target relation is partitioned table */
-		if (pstate->p_target_rangetblentry->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-	(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-	 errmsg("ON CONFLICT clause is not supported with partitioned tables")));
-
 		qry->onConflict = transformOnConflictClause(pstate,
 	stmt->onConflictClause);
-	}
 
 	/*
 	 * If we have a RETURNING clause, we need to add the target relation to
diff --git a/src/test/regress/expected/insert_conflict.out 

Re: [HACKERS] Partitioning vs ON CONFLICT

[Peter Geoghegan]

On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
 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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

On 2017/02/17 13:25, Peter Geoghegan wrote:
> On Thu, Feb 16, 2017 at 8:21 PM, Amit Langote
>  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
>From 63f2c2d6c47300cb7f1a422a0a5d2697223f55e3 Mon Sep 17 00:00:00 2001
From: amit 
Date: Fri, 17 Feb 2017 14:18:01 +0900
Subject: [PATCH] ON CONFLICT DO NOTHING should work with partitioned tables

The DO NOTHING conflict action does not require one to specify
conflict_target, which would require arbiter indexes to be defined
on the table.  So, only error out if DO UPDATE is requested as
the conflict action.

Adds the test as well.
---
 src/backend/parser/analyze.c  | 20 
 src/test/regress/expected/insert_conflict.out | 10 ++
 src/test/regress/sql/insert_conflict.sql  | 10 ++
 3 files changed, 32 insertions(+), 8 deletions(-)

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 0f7659bb6b..8e91f2f7c2 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -843,16 +843,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 
 	/* Process ON CONFLICT, if any. */
 	if (stmt->onConflictClause)
-	{
-		/* Bail out if target relation is partitioned table */
-		if (pstate->p_target_rangetblentry->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-	(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-	 errmsg("ON CONFLICT clause is not supported with partitioned tables")));
-
 		qry->onConflict = transformOnConflictClause(pstate,
 	stmt->onConflictClause);
-	}
 
 	/*
 	 * If we have a RETURNING clause, we need to add the target relation to
@@ -1010,6 +1002,18 @@ transformOnConflictClause(ParseState *pstate,
 	List	   *exclRelTlist = NIL;
 	OnConflictExpr *result;
 
+	/*
+	 * Bail out if target relation is partitioned table and on conflict
+	 * action is UPDATE; there won't be any arbiter indexes to infer the
+	 * conflict from.  That's because we do not yet support creating
+	 * indexes or index constraints on partitioned tables.
+	 */
+	if (onConflictClause->action == ONCONFLICT_UPDATE &&
+		pstate->p_target_rangetblentry->relkind == RELKIND_PARTITIONED_TABLE)
+		ereport(ERROR,
+(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ON CONFLICT ON UPDATE clause is not supported with partitioned tables")));
+
 	/* Process the arbiter clause, ON CONFLICT ON (...) */
 	transformOnConflictArbiter(pstate, onConflictClause, ,
 			   , );
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 8d005fddd4..d37f57d571 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -786,3 +786,13 @@ select * from selfconflict;
 (3 rows)
 
 drop table selfconflict;
+-- check that the following works:
+-- insert into partitioned_table on conflict do nothing
+create table parted_conflict_test (a int, b char) partition by list (a);
+create table parted_conflict_test_1 partition of parted_conflict_test for values in (1);
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+-- however, on conflict do update not supported yet
+insert into parted_conflict_test values (1) on conflict (a) do update set b = excluded.b where excluded.a = 1;
+ERROR:  ON CONFLICT ON UPDATE clause is 

Re: [HACKERS] Partitioning vs ON CONFLICT

[Peter Geoghegan]

On Thu, Feb 16, 2017 at 8:21 PM, Amit Langote
 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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Amit Langote]

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

Re: [HACKERS] Partitioning vs ON CONFLICT

[Peter Geoghegan]

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)

Re: [HACKERS] Partitioning vs ON CONFLICT

[Simon Riggs]

On 16 February 2017 at 14:54, Thom Brown  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 Riggshttp://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

[HACKERS] Partitioning vs ON CONFLICT

[Thom Brown]

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


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