Thread: [HACKERS] Partitioning vs ON CONFLICT
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
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
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)
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
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
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
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
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
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
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
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
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
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
Hello, I tried this feature using most recently snapshot. In case of added constraint PRIMARY KEY for partition table, INSERT ONCONFLICT 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 whileprocessing 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 CONFLICTDO 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 transactionand 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
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, INSERTON 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 CONFLICTDO 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 currenttransaction 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
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
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, INSERTON 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
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
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
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
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
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. ♜
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
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
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
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
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
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