Re: odd behavior/possible bug (Was: Re: [HACKERS] PG10 partitioning - odd behavior/possible bug)

2017-09-03 Thread Joe Conway
On 09/03/2017 03:34 PM, Tom Lane wrote: > Joe Conway writes: >> Notice that tsr is not empty at all on the first loop, but on the second >> loop it is empty every second time the trigger fires. > > I think the issue is that now() isn't changing within the transaction, > so

Re: odd behavior/possible bug (Was: Re: [HACKERS] PG10 partitioning - odd behavior/possible bug)

2017-09-03 Thread Tom Lane
Joe Conway writes: > Notice that tsr is not empty at all on the first loop, but on the second > loop it is empty every second time the trigger fires. I think the issue is that now() isn't changing within the transaction, so when you construct "tstzrange(lower(OLD.tr), now(),

odd behavior/possible bug (Was: Re: [HACKERS] PG10 partitioning - odd behavior/possible bug)

2017-09-03 Thread Joe Conway
On 09/03/2017 02:28 PM, Joe Conway wrote: > I was playing around with partitioning and found an oddity that is best > described with the following reasonably minimal test case: > Notice that in the first loop iteration tsr is calculated from OLD.tr > correctly. But in the second loop iteration

[HACKERS] PG10 partitioning - odd behavior/possible bug

2017-09-03 Thread Joe Conway
I was playing around with partitioning and found an oddity that is best described with the following reasonably minimal test case: 8<- CREATE TABLE timetravel ( id int8, f1 text not null, tr tstzrange not null default tstzrange(now(), 'infinity', '[]') ) PARTITION BY RANGE

Re: [HACKERS] hash partitioning based on v10Beta2

2017-08-29 Thread yangjie
Hi, When the number of partitions and the data are more, adding new partitions, there will be some efficiency problems. I don't know how the solution you're talking about is

Re: [HACKERS] hash partitioning based on v10Beta2

2017-08-28 Thread Robert Haas
On Mon, Aug 28, 2017 at 10:44 PM, yangjie wrote: > When the number of partitions and the data are more, adding new partitions, > there will be some efficiency problems. > I don't know how the solution you're talking about is how to implement a > hash partition? I am having

Re: [HACKERS] hash partitioning based on v10Beta2

2017-08-28 Thread Robert Haas
On Sat, Aug 26, 2017 at 12:40 AM, yang...@highgo.com wrote: > A partition table can be create as bellow: > > CREATE TABLE h1 PARTITION OF h; > CREATE TABLE h2 PARTITION OF h; > CREATE TABLE h3 PARTITION OF h; This syntax is very problematic for reasons that have been

Re: [HACKERS] hash partitioning based on v10Beta2

2017-08-28 Thread yang...@highgo.com
On Sat, Aug 26, 2017 at 10:10 AM, yang...@highgo.com wrote: > Hi all, > > Now we have had the range / list partition, but hash partitioning is not > implemented yet. > Attached is a POC patch based on the v10Beta2 to add the > hash partitioning feature. > Although we will

Re: [HACKERS] hash partitioning based on v10Beta2

2017-08-27 Thread Rushabh Lathia
On Sat, Aug 26, 2017 at 10:10 AM, yang...@highgo.com wrote: > Hi all, > > Now we have had the range / list partition, but hash partitioning is not > implemented yet. > Attached is a POC patch based on the v10Beta2 to add the > hash partitioning feature. > Although we will

[HACKERS] hash partitioning based on v10Beta2

2017-08-26 Thread yang...@highgo.com
Hi all, Now we have had the range / list partition, but hash partitioning is not implemented yet. Attached is a POC patch based on the v10Beta2 to add the hash partitioning feature. Although we will need more discussions about the syntax and other specifications before going ahead the

Re: [HACKERS] New partitioning - some feedback

2017-07-19 Thread Robert Haas
On Tue, Jul 18, 2017 at 2:26 AM, Vik Fearing wrote: > On 07/07/2017 02:02 AM, Mark Kirkwood wrote: >> I'd prefer *not* to see a table and its partitions all intermixed in the >> same display (especially with nothing indicating which are partitions) - >> as this will

Re: [HACKERS] New partitioning - some feedback

2017-07-18 Thread Vik Fearing
On 07/07/2017 02:02 AM, Mark Kirkwood wrote: > I'd prefer *not* to see a table and its partitions all intermixed in the > same display (especially with nothing indicating which are partitions) - > as this will make for unwieldy long lists when tables have many > partitions. Also it would be good

Re: [HACKERS] New partitioning - some feedback

2017-07-16 Thread Mark Kirkwood
On 16/07/17 05:24, David Fetter wrote: On Fri, Jul 14, 2017 at 09:49:25PM -0500, Robert Haas wrote: On Mon, Jul 10, 2017 at 5:46 PM, David Fetter wrote: With utmost respect, it's less messy than adding '!' to the already way too random and mysterious syntax of psql's \

Re: [HACKERS] New partitioning - some feedback

2017-07-15 Thread David Fetter
On Fri, Jul 14, 2017 at 09:49:25PM -0500, Robert Haas wrote: > On Mon, Jul 10, 2017 at 5:46 PM, David Fetter wrote: > > With utmost respect, it's less messy than adding '!' to the already > > way too random and mysterious syntax of psql's \ commands. What > > should '\det!'

Re: [HACKERS] New partitioning - some feedback

2017-07-14 Thread Robert Haas
On Mon, Jul 10, 2017 at 5:46 PM, David Fetter wrote: > With utmost respect, it's less messy than adding '!' to the already > way too random and mysterious syntax of psql's \ commands. What > should '\det!' mean? What about '\dT!'? Since \det lists foreign tables, \det! would

Re: [HACKERS] New partitioning - some feedback

2017-07-13 Thread Amit Langote
On 2017/07/13 7:23, Dean Rasheed wrote: > On 12 July 2017 at 15:58, Alvaro Herrera wrote: >> Amit Langote wrote: >>> On 2017/07/11 13:34, Alvaro Herrera wrote: However, the "list tables" command \dt should definitely IMO not list partitions. >>> >>> Do you mean

Re: [HACKERS] New partitioning - some feedback

2017-07-12 Thread Amit Langote
On 2017/07/12 23:58, Alvaro Herrera wrote: > Amit Langote wrote: >> On 2017/07/11 13:34, Alvaro Herrera wrote: >>> Robert Haas wrote: On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote wrote: >>> > Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables

Re: [HACKERS] New partitioning - some feedback

2017-07-12 Thread Dean Rasheed
On 12 July 2017 at 23:23, Dean Rasheed wrote: > I also agree that there probably isn't much need for a list that > *only* includes partitions, but if someone comes up with a convincing > use case, then we could add another 2-letter command for that. > Actually, I just

Re: [HACKERS] New partitioning - some feedback

2017-07-12 Thread Dean Rasheed
On 12 July 2017 at 15:58, Alvaro Herrera wrote: > Amit Langote wrote: >> On 2017/07/11 13:34, Alvaro Herrera wrote: >> > However, the "list tables" >> > command \dt should definitely IMO not list partitions. >> >> Do you mean never? Even if a modifier is specified? In

Re: [HACKERS] New partitioning - some feedback

2017-07-12 Thread Alvaro Herrera
Amit Langote wrote: > On 2017/07/11 13:34, Alvaro Herrera wrote: > > Robert Haas wrote: > >> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote > >> wrote: > > > >>> Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type > >>> "partitioned table", we

Re: [HACKERS] New partitioning - some feedback

2017-07-12 Thread Ashutosh Bapat
On Wed, Jul 12, 2017 at 9:39 AM, Amit Langote wrote: > On 2017/07/12 12:47, Ashutosh Bapat wrote: >> On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote >> wrote: >>> On 2017/07/11 18:57, Ashutosh Bapat wrote: On Tue, Jul 11, 2017 at

Re: [HACKERS] New partitioning - some feedback

2017-07-12 Thread Amit Langote
On 2017/07/12 13:09, Amit Langote wrote: > On 2017/07/12 12:47, Ashutosh Bapat wrote: >> Do you see that those patches can be used in current discussion in any way? > > It wouldn't really be a bad idea to put that patch here, because there's > no special reason for it to be in the CF for PG 11,

Re: [HACKERS] New partitioning - some feedback

2017-07-11 Thread Amit Langote
On 2017/07/12 12:47, Ashutosh Bapat wrote: > On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote > wrote: >> On 2017/07/11 18:57, Ashutosh Bapat wrote: >>> On Tue, Jul 11, 2017 at 4:16 AM, David Fetter wrote: So whatever we land on needs to mention

Re: [HACKERS] New partitioning - some feedback

2017-07-11 Thread Ashutosh Bapat
On Wed, Jul 12, 2017 at 8:23 AM, Amit Langote wrote: > On 2017/07/11 18:57, Ashutosh Bapat wrote: >> On Tue, Jul 11, 2017 at 4:16 AM, David Fetter wrote: >>> So whatever we land on needs to mention partition_of and >>> has_partitions. Is that

Re: [HACKERS] New partitioning - some feedback

2017-07-11 Thread Amit Langote
On 2017/07/11 13:34, Alvaro Herrera wrote: > Robert Haas wrote: >> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote >> wrote: > >>> Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type >>> "partitioned table", we wouldn't need a separate flag for

Re: [HACKERS] New partitioning - some feedback

2017-07-11 Thread Amit Langote
On 2017/07/11 18:57, Ashutosh Bapat wrote: > On Tue, Jul 11, 2017 at 4:16 AM, David Fetter wrote: >> So whatever we land on needs to mention partition_of and >> has_partitions. Is that latter just its immediate partitions? >> Recursion all the way down? Somewhere in between?

Re: [HACKERS] New partitioning - some feedback

2017-07-11 Thread Ashutosh Bapat
On Tue, Jul 11, 2017 at 4:16 AM, David Fetter wrote: > On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote: >> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote >> wrote: >> > I posted a patch upthread which makes \d hide partitions >> >

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Alvaro Herrera
Robert Haas wrote: > On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote > wrote: > > Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type > > "partitioned table", we wouldn't need a separate flag for marking a table > > as having partitions. > > I

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Amit Langote
On 2017/07/11 10:34, Paul A Jungwirth wrote: >> Also, there seems to be at least some preference >> for excluding partitions by default from the \d listing. > > As another user of partitions I'll chime in and say that would be very > nice! On the other hand, with pre-10 partitions you do see all

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Paul A Jungwirth
> Also, there seems to be at least some preference > for excluding partitions by default from the \d listing. As another user of partitions I'll chime in and say that would be very nice! On the other hand, with pre-10 partitions you do see all the child tables with `\d`, so showing declarative

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Amit Langote
On 2017/07/11 7:33, Robert Haas wrote: > On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote > wrote: >> Actually, if \d had shown RELKIND_PARTITIONED_TABLE tables as of Type >> "partitioned table", we wouldn't need a separate flag for marking a table >> as having

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Greg Stark
On 10 July 2017 at 23:46, David Fetter wrote: > On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote: >> On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote >> wrote: >> > I posted a patch upthread which makes \d hide partitions >> >

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread David Fetter
On Mon, Jul 10, 2017 at 05:33:34PM -0500, Robert Haas wrote: > On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote > wrote: > > I posted a patch upthread which makes \d hide partitions > > (relispartition = true relations) and include them if the newly > > proposed '!'

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Robert Haas
On Mon, Jul 10, 2017 at 2:15 AM, Amit Langote wrote: > I posted a patch upthread which makes \d hide partitions (relispartition = > true relations) and include them if the newly proposed '!' modifier is > specified. The '+' modifier is being used to show additional

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread David Fetter
On Mon, Jul 10, 2017 at 04:15:28PM +0900, Amit Langote wrote: > On 2017/07/10 15:32, Craig Ringer wrote: > > On 8 July 2017 at 00:03, David Fetter wrote: > > > >> On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote: > >>> Hi Mark, > >>> > >>> On 2017/07/07 9:02, Mark

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Amit Langote
On 2017/07/10 15:32, Craig Ringer wrote: > On 8 July 2017 at 00:03, David Fetter wrote: > >> On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote: >>> Hi Mark, >>> >>> On 2017/07/07 9:02, Mark Kirkwood wrote: I've been trying out the new partitioning in version 10.

Re: [HACKERS] New partitioning - some feedback

2017-07-10 Thread Craig Ringer
On 8 July 2017 at 00:03, David Fetter wrote: > On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote: > > Hi Mark, > > > > On 2017/07/07 9:02, Mark Kirkwood wrote: > > > I've been trying out the new partitioning in version 10. Firstly, I > must > > > say this is

Re: [HACKERS] New partitioning - some feedback

2017-07-09 Thread Amit Langote
On 2017/07/08 14:12, Mark Kirkwood wrote: > On 07/07/17 19:54, Michael Banck wrote: >> On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: >>> On 07/07/17 13:29, Amit Langote wrote: Someone complained about this awhile back [1]. And then it came up again [2], where Noah

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Mark Kirkwood
On 07/07/17 19:54, Michael Banck wrote: On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: On 07/07/17 13:29, Amit Langote wrote: Someone complained about this awhile back [1]. And then it came up again [2], where Noah appeared to take a stance that partitions should be visible

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread David Fetter
On Fri, Jul 07, 2017 at 10:29:26AM +0900, Amit Langote wrote: > Hi Mark, > > On 2017/07/07 9:02, Mark Kirkwood wrote: > > I've been trying out the new partitioning in version 10. Firstly, I must > > say this is excellent - so much nicer than the old inheritance based method! > > Thanks. :) > >

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Robert Haas
On Fri, Jul 7, 2017 at 8:20 AM, Tom Lane wrote: > Robert Haas writes: >> I don't have a strong view on whether partitions should be hidden by >> default, although I lean slightly against it (say, -0.25). But if we >> do decide to hide them by default,

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Simon Riggs
On 7 July 2017 at 13:20, Tom Lane wrote: > Robert Haas writes: >> I don't have a strong view on whether partitions should be hidden by >> default, although I lean slightly against it (say, -0.25). But if we >> do decide to hide them by default, then I

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Tom Lane
Robert Haas writes: > I don't have a strong view on whether partitions should be hidden by > default, although I lean slightly against it (say, -0.25). But if we > do decide to hide them by default, then I definitely want an > easy-to-use modifier that overrides that

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Robert Haas
On Fri, Jul 7, 2017 at 3:54 AM, Michael Banck wrote: > +1. > > Or maybe just 'partition' is enough if 'partition table' would widen the > column output unnecessarily. Internally to the source code, the parent is called a "partitioned table" and the child is called a

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Simon Riggs
On 7 July 2017 at 08:54, Michael Banck wrote: > On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: >> On 07/07/17 13:29, Amit Langote wrote: >> >Someone complained about this awhile back [1]. And then it came up again >> >[2], where Noah appeared to take a

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Michael Banck
On Fri, Jul 07, 2017 at 07:40:55PM +1200, Mark Kirkwood wrote: > On 07/07/17 13:29, Amit Langote wrote: > >Someone complained about this awhile back [1]. And then it came up again > >[2], where Noah appeared to take a stance that partitions should be > >visible in views / output of commands that

Re: [HACKERS] New partitioning - some feedback

2017-07-07 Thread Mark Kirkwood
On 07/07/17 13:29, Amit Langote wrote: Someone complained about this awhile back [1]. And then it came up again [2], where Noah appeared to take a stance that partitions should be visible in views / output of commands that list "tables". Although I too tend to prefer not filling up the \d

Re: [HACKERS] New partitioning - some feedback

2017-07-06 Thread Amit Langote
Hi Mark, On 2017/07/07 9:02, Mark Kirkwood wrote: > I've been trying out the new partitioning in version 10. Firstly, I must > say this is excellent - so much nicer than the old inheritance based method! Thanks. :) > My only niggle is the display of partitioned tables via \d etc. e.g: > >

[HACKERS] New partitioning - some feedback

2017-07-06 Thread Mark Kirkwood
I've been trying out the new partitioning in version 10. Firstly, I must say this is excellent - so much nicer than the old inheritance based method! My only niggle is the display of partitioned tables via \d etc. e.g: part=# \d List of relations Schema | Name

Re: [HACKERS] Declarative partitioning - another take

2017-06-29 Thread Etsuro Fujita
Hi Maksim, On 2017/04/07 19:52, Maksim Milyutin wrote: On 07.04.2017 13:05, Etsuro Fujita wrote: On 2016/12/09 19:46, Maksim Milyutin wrote: I would like to work on two tasks: - insert (and eventually update) tuple routing for foreign partition. - the ability to create an index on the

Re: [HACKERS] Declarative partitioning - another take

2017-05-10 Thread Robert Haas
On Wed, May 10, 2017 at 6:50 AM, Etsuro Fujita wrote: > I started working on this. I agree that the changes made in > make_modifytable would be unacceptable, but I'd vote for Amit's idea of > passing a modified PlannerInfo to PlanForeignModify so that the FDW can do

Re: [HACKERS] Declarative partitioning - another take

2017-05-10 Thread Etsuro Fujita
On 2016/11/18 1:43, Robert Haas wrote: On Thu, Nov 17, 2016 at 6:27 AM, Amit Langote wrote: - The code in make_modifytable() to swap out the rte_array for a fake one looks like an unacceptable kludge. I don't know offhand what a better design would look like,

Re: [HACKERS] Declarative partitioning - another take

2017-05-09 Thread Amit Langote
On 2017/05/10 12:59, Robert Haas wrote: > On Tue, May 9, 2017 at 11:54 PM, Thomas Munro > wrote: >> +A statement that targets a parent table in a inheritance or partitioning >> >> A tiny typo: s/a inheritance/an inheritance/ > > Now he tells me. Thanks both.

Re: [HACKERS] Declarative partitioning - another take

2017-05-09 Thread Robert Haas
On Tue, May 9, 2017 at 11:54 PM, Thomas Munro wrote: > +A statement that targets a parent table in a inheritance or partitioning > > A tiny typo: s/a inheritance/an inheritance/ Now he tells me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The

Re: [HACKERS] Declarative partitioning - another take

2017-05-09 Thread Thomas Munro
On Wed, May 10, 2017 at 3:51 PM, Robert Haas wrote: > On Sun, May 7, 2017 at 9:44 PM, Amit Langote > wrote: >> I think that makes sense. Modified it to read: "A statement that targets >> a parent table in a inheritance or partitioning

Re: [HACKERS] Declarative partitioning - another take

2017-05-09 Thread Robert Haas
On Sun, May 7, 2017 at 9:44 PM, Amit Langote wrote: > I think that makes sense. Modified it to read: "A statement that targets > a parent table in a inheritance or partitioning hierarchy..." in the > attached updated patch. LGTM. Committed. -- Robert Haas

Re: [HACKERS] Declarative partitioning - another take

2017-05-07 Thread Amit Langote
On 2017/05/08 10:22, Thomas Munro wrote: > On Mon, May 8, 2017 at 12:47 PM, Amit Langote > wrote: >> On 2017/05/03 2:48, Robert Haas wrote: >>> On Tue, May 2, 2017 at 3:30 AM, Amit Langote >>> wrote: You're right. I agree that

Re: [HACKERS] Declarative partitioning - another take

2017-05-07 Thread Thomas Munro
On Mon, May 8, 2017 at 12:47 PM, Amit Langote wrote: > On 2017/05/03 2:48, Robert Haas wrote: >> On Tue, May 2, 2017 at 3:30 AM, Amit Langote >> wrote: >>> You're right. I agree that whatever text we add here should be pointing >>>

Re: [HACKERS] Declarative partitioning - another take

2017-05-07 Thread Amit Langote
On 2017/05/03 2:48, Robert Haas wrote: > On Tue, May 2, 2017 at 3:30 AM, Amit Langote > wrote: >> You're right. I agree that whatever text we add here should be pointing >> out that statement-level triggers of affected child tables are not fired, >> when root

Re: [HACKERS] Declarative partitioning - another take

2017-05-02 Thread Robert Haas
On Tue, May 2, 2017 at 3:30 AM, Amit Langote wrote: > You're right. I agree that whatever text we add here should be pointing > out that statement-level triggers of affected child tables are not fired, > when root parent is specified in the command. > > Since there

Re: [HACKERS] Declarative partitioning - another take

2017-05-02 Thread Amit Langote
On 2017/05/01 21:30, Robert Haas wrote: > On Mon, May 1, 2017 at 12:18 AM, Amit Langote > wrote: >> Attached updated patch. > > Committed, except for this bit: Thanks. > +A statement-level trigger defined on partitioned tables is fired only > +once for

Re: [HACKERS] Declarative partitioning - another take

2017-05-01 Thread Robert Haas
On Mon, May 1, 2017 at 12:18 AM, Amit Langote wrote: > Attached updated patch. Committed, except for this bit: +A statement-level trigger defined on partitioned tables is fired only +once for the table itself, not once for every table in the partitioning +

Re: [HACKERS] Declarative partitioning - another take

2017-04-30 Thread Amit Langote
On 2017/04/29 6:56, David Fetter wrote: > On Fri, Apr 28, 2017 at 06:29:48PM +0900, Amit Langote wrote: >> On 2017/04/28 7:36, David Fetter wrote: >>> Did I notice correctly that there's no way to handle transition tables >>> for partitions in AFTER STATEMENT triggers? >> >> Did you mean to ask

Re: [HACKERS] Declarative partitioning - another take

2017-04-30 Thread Amit Langote
Thanks for the review. On 2017/04/29 1:25, Robert Haas wrote: > On Fri, Apr 28, 2017 at 2:13 AM, Amit Langote > wrote: By the way, code changes I made in the attached are such that a subsequent patch could implement firing statement-level triggers of all

Re: [HACKERS] Declarative partitioning - another take

2017-04-30 Thread Noah Misch
On Mon, Apr 24, 2017 at 07:43:29PM +0900, Amit Langote wrote: > On 2017/04/21 17:00, Rajkumar Raghuwanshi wrote: > > I am able to create statement triggers at root partition, but these > > triggers, not getting fired on updating partition. > It would be great if you could check if the patches fix

Re: [HACKERS] Declarative partitioning - another take

2017-04-28 Thread David Fetter
On Fri, Apr 28, 2017 at 06:29:48PM +0900, Amit Langote wrote: > On 2017/04/28 7:36, David Fetter wrote: > > On Thu, Apr 27, 2017 at 10:30:54AM +0900, Amit Langote wrote: > >> On 2017/04/27 1:52, Robert Haas wrote: > >>> On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote > >>>

Re: [HACKERS] Declarative partitioning - another take

2017-04-28 Thread Robert Haas
On Fri, Apr 28, 2017 at 2:13 AM, Amit Langote wrote: > It seems to me that there is no difference in behavior between > inheritance-based and declarative partitioning as far as statement-level > triggers are concerned (at least currently). In both the cases, we

Re: [HACKERS] Declarative partitioning - another take

2017-04-28 Thread Amit Langote
On 2017/04/28 7:36, David Fetter wrote: > On Thu, Apr 27, 2017 at 10:30:54AM +0900, Amit Langote wrote: >> On 2017/04/27 1:52, Robert Haas wrote: >>> On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote >>> wrote: FWIW, I too prefer the latter, that is, fire only the

Re: [HACKERS] Declarative partitioning - another take

2017-04-28 Thread Amit Langote
Hi Rajkumar, On 2017/04/28 17:11, Rajkumar Raghuwanshi wrote: > On Fri, Apr 28, 2017 at 11:43 AM, Amit Langote < >> Updated patch attached. > > I have applied given patch, could see below behaviour with statement > trigger. > > When trying to delete value within partition range, triggers got

Re: [HACKERS] Declarative partitioning - another take

2017-04-28 Thread Rajkumar Raghuwanshi
On Fri, Apr 28, 2017 at 11:43 AM, Amit Langote < langote_amit...@lab.ntt.co.jp> wrote: > Updated patch attached. > Hi Amit, I have applied given patch, could see below behaviour with statement trigger. When trying to delete value within partition range, triggers got fired (with zero row

Re: [HACKERS] Declarative partitioning - another take

2017-04-28 Thread Amit Langote
Thanks for taking a look. On 2017/04/28 5:24, Robert Haas wrote: > On Wed, Apr 26, 2017 at 9:30 PM, Amit Langote > wrote: >>> Do we need to update the documentation? >> >> Yes, I think we should. How about as in the attached? > > Looks reasonable, but I was

Re: [HACKERS] Declarative partitioning - another take

2017-04-27 Thread David Fetter
On Thu, Apr 27, 2017 at 10:30:54AM +0900, Amit Langote wrote: > On 2017/04/27 1:52, Robert Haas wrote: > > On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote > > wrote: > >> FWIW, I too prefer the latter, that is, fire only the parent's triggers. > >> In that case,

Re: [HACKERS] Declarative partitioning - another take

2017-04-27 Thread Robert Haas
On Wed, Apr 26, 2017 at 9:30 PM, Amit Langote wrote: >> Do we need to update the documentation? > > Yes, I think we should. How about as in the attached? Looks reasonable, but I was thinking you might also update the section which contrasts inheritance-based

Re: [HACKERS] Declarative partitioning - another take

2017-04-26 Thread Amit Langote
On 2017/04/27 1:52, Robert Haas wrote: > On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote > wrote: >> FWIW, I too prefer the latter, that is, fire only the parent's triggers. >> In that case, applying only the patch 0001 will do. > > Do we need to update the

Re: [HACKERS] Declarative partitioning - another take

2017-04-26 Thread Robert Haas
On Tue, Apr 25, 2017 at 10:34 PM, Amit Langote wrote: > FWIW, I too prefer the latter, that is, fire only the parent's triggers. > In that case, applying only the patch 0001 will do. Do we need to update the documentation? -- Robert Haas EnterpriseDB:

Re: [HACKERS] Declarative partitioning - another take

2017-04-26 Thread Amit Khandekar
On 26 April 2017 at 00:28, Robert Haas wrote: > So what I'd prefer -- on > the totally unprincipled basis that it would let us improve > performance in the future -- if you operate on a partition directly, > you fire the partition's triggers, but if you operate on the

Re: [HACKERS] Declarative partitioning - another take

2017-04-25 Thread Amit Langote
Hi, Thanks for testing. On 2017/04/25 19:03, Rajkumar Raghuwanshi wrote: > Thanks for looking into it. I have applied fixes and checked for triggers. > I could see difference in behaviour of statement triggers for INSERT and > UPDATE, for insert only root partition triggers are getting fired but

Re: [HACKERS] Declarative partitioning - another take

2017-04-25 Thread Amit Langote
On 2017/04/26 3:58, Robert Haas wrote: > On Mon, Apr 24, 2017 at 6:43 AM, Amit Langote > wrote: >> The reason it doesn't work is that we do not allocate ResultRelInfos for >> partitioned tables (not even for the root partitioned table in the >> update/delete cases),

Re: [HACKERS] Declarative partitioning - another take

2017-04-25 Thread Robert Haas
On Mon, Apr 24, 2017 at 6:43 AM, Amit Langote wrote: > The reason it doesn't work is that we do not allocate ResultRelInfos for > partitioned tables (not even for the root partitioned table in the > update/delete cases), because the current implementation assumes

Re: [HACKERS] Declarative partitioning - another take

2017-04-25 Thread Rajkumar Raghuwanshi
On Mon, Apr 24, 2017 at 4:13 PM, Amit Langote wrote: > Hi Rajkumar, > > It would be great if you could check if the patches fix the issues. > Hi Amit, Thanks for looking into it. I have applied fixes and checked for triggers. I could see difference in behaviour

Re: [HACKERS] Declarative partitioning - another take

2017-04-24 Thread Amit Langote
Hi Rajkumar, Thanks for testing and the report. On 2017/04/21 17:00, Rajkumar Raghuwanshi wrote: > Hi, > > I have observed below with the statement triggers. > > I am able to create statement triggers at root partition, but these > triggers, not getting fired on updating partition. > > CREATE

Re: [HACKERS] Declarative partitioning - another take

2017-04-21 Thread Rajkumar Raghuwanshi
Hi, I have observed below with the statement triggers. I am able to create statement triggers at root partition, but these triggers, not getting fired on updating partition. CREATE TABLE pt (a INT, b INT) PARTITION BY RANGE(a); CREATE TABLE pt1 PARTITION OF pt FOR VALUES FROM (1) to (7); CREATE

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-13 Thread Amit Langote
On 2017/04/14 5:28, Robert Haas wrote: > On Thu, Apr 6, 2017 at 3:14 AM, Amit Langote > wrote: >>> The bulk of operations that work on traditional tables also work on >>> partitions >>> and partitioned tables. The next closest kind of relation, a materialized >>>

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-13 Thread Robert Haas
On Thu, Apr 6, 2017 at 3:14 AM, Amit Langote wrote: >> The bulk of operations that work on traditional tables also work on >> partitions >> and partitioned tables. The next closest kind of relation, a materialized >> view, is far less table-like. Therefore, I

Re: [HACKERS] Declarative partitioning - another take

2017-04-07 Thread Maksim Milyutin
On 07.04.2017 13:05, Etsuro Fujita wrote: On 2016/12/14 16:20, Etsuro Fujita wrote: On 2016/12/09 19:46, Maksim Milyutin wrote: I would like to work on two tasks: - insert (and eventually update) tuple routing for foreign partition. - the ability to create an index on the parent and have all

Re: [HACKERS] Declarative partitioning - another take

2017-04-07 Thread Etsuro Fujita
On 2016/12/14 16:20, Etsuro Fujita wrote: On 2016/12/09 19:46, Maksim Milyutin wrote: I would like to work on two tasks: - insert (and eventually update) tuple routing for foreign partition. - the ability to create an index on the parent and have all of the children inherit it; The first one

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-06 Thread Amit Langote
On 2017/04/06 16:02, Noah Misch wrote: > On Wed, Jan 25, 2017 at 01:19:00PM -0500, Robert Haas wrote: >> On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut >> wrote: >>> On 1/18/17 2:32 PM, Robert Haas wrote: Unless we can find something official, I suppose

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-06 Thread Noah Misch
On Wed, Jan 25, 2017 at 01:19:00PM -0500, Robert Haas wrote: > On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut > wrote: > > On 1/18/17 2:32 PM, Robert Haas wrote: > >> Unless we can find something official, I suppose we should just > >> display BASE TABLE in

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-03-30 Thread Amit Langote
On 2017/01/26 3:19, Robert Haas wrote: > On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut > wrote: >> On 1/18/17 2:32 PM, Robert Haas wrote: >>> Unless we can find something official, I suppose we should just >>> display BASE TABLE in that case as we do in other

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-24 Thread Amit Langote
On Fri, Mar 24, 2017 at 11:06 PM, Simon Riggs wrote: > On 1 March 2017 at 01:36, Amit Langote wrote: > >> I don't know which way you're thinking of fixing this, but a planner patch >> to implement faster partition-pruning will have taken care

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-24 Thread Aleksander Alekseev
Hi Simon, > > I don't know which way you're thinking of fixing this, but a planner patch > > to implement faster partition-pruning will have taken care of this, I > > think. As you may know, even declarative partitioned tables currently > > depend on constraint exclusion for partition-pruning

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-24 Thread Simon Riggs
On 1 March 2017 at 01:36, Amit Langote wrote: > I don't know which way you're thinking of fixing this, but a planner patch > to implement faster partition-pruning will have taken care of this, I > think. As you may know, even declarative partitioned tables

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-10 Thread Aleksander Alekseev
Hi Tels, Thanks a lot for the review! > "corresponding" Fixed. > Also a question: Some one-line comments are > > /* Comment. */ > > while others are > > /* > * Comment. > */ > > Why the difference? I'm trying to follow a code stile used in a code I'm modifying. In this case I got an

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-09 Thread Tels
Hi Aleksander, noticed this in your patch: > * Add a corespinding entry to pgStatTabHash. "corresponding" Also a question: Some one-line comments are /* Comment. */ while others are /* * Comment. */ Why the difference? Hope this helps, Tels PS: Sorry if this appears twice, I

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-09 Thread Tels
Hi Aleksander, noticed this in your patch: > * Add a corespinding entry to pgStatTabHash. "corresponding" Also a question: Some one-line comments are /* Comment. */ while others are /* * Comment. */ Why the difference? Hope this helps, Tels -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-09 Thread Aleksander Alekseev
Hi Amit, > Sorry, I didn't mean to dissuade you from trying those > micro-optimizations. If general inheritance cases can benefit from it > (which, until we have a different method, will be used by partitioned > tables as well), I think we should try it. OK, I'll see what could be done here as

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-09 Thread Aleksander Alekseev
Hi, Andres Thanks a lot for the review! > Why are we keeping that list / the "batch" allocation pattern? It > doesn't actually seem useful to me after these changes. Given that we > don't shrink hash-tables, we could just use the hash-table memory for > this, no? I don't think we can do that.

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-06 Thread Amit Langote
Hi Aleksander, On 2017/03/07 0:22, Aleksander Alekseev wrote: > Hello. > > OK, here is a patch. > > Benchmark, before: > > ``` > number of transactions actually processed: 1823 > latency average = 1153.495 ms > latency stddev = 154.366 ms > tps = 6.061104 (including connections establishing) >

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-06 Thread Andres Freund
Hi, This issue has bothered me in non-partitioned use-cases recently, so thanks for taking it up. On 2017-03-06 18:22:17 +0300, Aleksander Alekseev wrote: > diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c > index 2fb9a8bf58..fa906e7930 100644 > ---

Re: [HACKERS] Declarative partitioning optimization for large amount of partitions

2017-03-06 Thread Aleksander Alekseev
Hello. OK, here is a patch. Benchmark, before: ``` number of transactions actually processed: 1823 latency average = 1153.495 ms latency stddev = 154.366 ms tps = 6.061104 (including connections establishing) tps = 6.061211 (excluding connections establishing) ``` Benchmark, after: ``` number

  1   2   3   4   5   6   7   8   9   10   >