Re: [HACKERS] Partitioning vs ON CONFLICT

2017-08-01 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-08-01 Thread 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? > >

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-08-01 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-07-31 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-07-31 Thread 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,

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-04-03 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-04-01 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-31 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-31 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-31 Thread 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) >

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-31 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-30 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-30 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-30 Thread Ashutosh Bapat
-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...@2nd

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-29 Thread Shinoda, Noriyoshi
c: 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-27 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-27 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-09 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-09 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-03-09 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-02-16 Thread 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. >

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-02-16 Thread 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()

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-02-16 Thread 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,

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-02-16 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-02-16 Thread 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

Re: [HACKERS] Partitioning vs ON CONFLICT

2017-02-16 Thread 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

2017-02-16 Thread 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; >

[HACKERS] Partitioning vs ON CONFLICT

2017-02-16 Thread 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