Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Wed, Apr 22, 2009 at 11:34 AM, Tom Lane wrote: > The KISS principle applies with a vengeance here.  I think we should > make the partitioning stuff handle only the simplest cases but do those > well.  Anybody who wants something more complex can still try to tackle > it via the existing facilit

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, 2009-04-21 at 14:51 -0400, Tom Lane wrote: > The partitioning > rules should be simple enough that they can easily be applied at > runtime to determine which partition to look in. +1 -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Zeugswetter Andreas OSB sIT writes: >> Which leads me to the same conclusion: anything as complicated as CASE >> is the wrong design. But perhaps for slightly different reasons. > What I like about the sql CASE is, that it is expression based, and thus > allows full flexibility in partitioning

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

> Which leads me to the same conclusion: anything as complicated as CASE > is the wrong design. But perhaps for slightly different reasons. What I like about the sql CASE is, that it is expression based, and thus allows full flexibility in partitioning and is highly self documenting. Do we nee

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Original-Nachricht > Datum: Tue, 21 Apr 2009 14:53:54 -0400 > Von: Robert Haas > An: steven king > CC: lis...@guedesoft.net, pgsql-hackers@postgresql.org, st...@enterprisedb.com > Betreff: Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax >

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Does sql standard defines it ? and another question, what about updating existing partitions, with no need to drop/recreate ? -- 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] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 2:29 PM, steven king wrote: >> Rather than SWITCH CASE ... you probably would >> want to reuse the existing PostgreSQL syntax of CASE WHEN >> ... > > I think - at first we've to ask for the problem we have to solve. > > The syntax it isnt. If we get confused with CASE of

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Robert Haas writes: > I'm also not sure how skillful the constraint exclusion logic is at > proving theorems when CASE statements are involved. It's not at all, and unlikely to become so. However, I think worrying about that might be focusing on the wrong thing. If this patch expects us to stil

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Em Ter, 2009-04-21 às 19:33 +0530, Kedar Potdar escreveu: (...) > You can have multiple columns as partition key attributes and values > for these attributes should appear in the order specified. How would be the behavior if the partition keys are foreing key with ON UPDATE CASCADE? I'm thinkin

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Original-Nachricht > Datum: Tue, 21 Apr 2009 13:36:19 -0400 > Von: Robert Haas > An: steven king > CC: Greg Stark , pgsql-hackers@postgresql.org, > lis...@guedesoft.net > Betreff: Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax > >

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

More importantly it makes it a lot harder for the planner to do clever things. Currently having to append two tables means losing the ordering of the records and having to resort. Even if that's fixed it makes it harder to get reasonable estimates for size and distinctness. Ideally partitio

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 12:17 PM, steven king wrote: > the idea is, that this statement does not create a new kind of db-object. > this "CREATE PARTITIONS" statement should be a meta-statement -> "meta" like > the serial type. That was my assumption as well. > with create paritions you can cre

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

>> > The big win that is associated with table partitioning is using >> > constraint exclusion to avoid unnecessary partitions scans. > > there is no reason for not using the 'CASE condition' for constraint > exclusion. There is if you can't rely on the rows to be in the right partition. Allowing

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 12:35 PM, steven king wrote: > why a partition-key cannot be a complex expression? > > like this: > > SWITCH > CASE TABLE [IN ] > CASE TABLE [IN ] > CASE TABLE [IN ] > DEFAULT [IN ] > > that is generic Rather than SWITCH CASE ... you probably would want to reuse

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 11:50 AM, Csaba Nagy wrote: > On Tue, 2009-04-21 at 11:43 -0400, Robert Haas wrote: >> This doesn't sound like a very good idea, because the planner cannot >> then rely on the overflow table not containing tuples that ought to be >> within some other partition. >> >> The bi

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

> SWITCH > CASE TABLE [IN ] > CASE TABLE [IN ] > CASE TABLE [IN ] > DEFAULT [IN ] some examples: CREATE TABLE ttt ( id integer, txt varchar ) SWITCH id % 3 CASE 0 TABLE [IN ] CASE 1 TABLE [IN ] CASE 2 TABLE [IN ] DEFAULT [IN ] or SWITCH lower(txt) CASE 'hello' TABLE [IN ] CASE

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Original-Nachricht > Datum: Tue, 21 Apr 2009 16:37:15 +0100 > Von: Greg Stark > An: vac...@quantentunnel.de > CC: "Dickson S. Guedes" , pgsql-hackers@postgresql.org > Betreff: Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax > On

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Original-Nachricht > Datum: Tue, 21 Apr 2009 17:50:02 +0200 > Von: Csaba Nagy > An: Robert Haas > CC: Kedar Potdar , Greg Stark > , pgsql-hackers@postgresql.org > Betreff: Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax > > The bi

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, 2009-04-21 at 11:43 -0400, Robert Haas wrote: > This doesn't sound like a very good idea, because the planner cannot > then rely on the overflow table not containing tuples that ought to be > within some other partition. > > The big win that is associated with table partitioning is using >

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

th a lot of PL/pqSQL code or invocations of built-in functions. open your mind :) Original-Nachricht > Datum: Tue, 21 Apr 2009 11:39:37 -0400 > Von: Robert Haas > An: vac...@quantentunnel.de > CC: "Dickson S. Guedes" , pgsql-hackers@postgresql.org > Betreff: Re:

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 9:38 AM, Kedar Potdar wrote: > Currently, such records are left in the overflow partition and its > responsibility > of user to insert them into partitioned-table which will then re-direct > those to > appropriate partitions. This doesn't sound like a very good idea, becau

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 11:13 AM, wrote: > oh sorry .. > > but what I miss in this discussion - where we can define tablespace? > > CREATE PARTITIONS ON ( > > CASE WHEN THEN emp_xxx [IN ], > CASE WHEN THEN emp_yyy [IN ], > CASE WHEN emp_zzz [IN ], > ELSE emp_default [IN ] > ) Oh blech. That

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 4:13 PM, wrote: > oh sorry .. > > but what I miss in this discussion - where we can define tablespace? > > CREATE PARTITIONS ON ( > > CASE WHEN THEN emp_xxx [IN ], One thing you should realize is that the actual details of the syntax will be knocked around and redefined

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

:54 -0300 > Von: "Dickson S. Guedes" > An: vac...@quantentunnel.de, pgsql-hackers@postgresql.org > Betreff: Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax > Vacuum, I guess you forgot to reply-to-all. ":) > > Em Ter, 2009-04-21 às 16:05 +02

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

> > >> > (...) > >> > PARTITION BY RANGE (emp_id) > >> > ( > >> > emp_500 (START 1 END 500), > >> > emp_1500 (START 500 END 1500), > >> > emp_4000 (START 1520 END 4000) > >> > ); > >> > >> What if I need more columns to set the partitions? > > > > > > You can do so by using command like this, > > >

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

2009/4/21 Kedar Potdar : > > > 2009/4/21 Dickson S. Guedes >> >> Em Ter, 2009-04-21 às 17:20 +0530, Kedar Potdar escreveu: >> > Hi , >> > >> > We are working on a patch to automate partitioning in PostgreSQL. >> >> Nice. ":) >> >> > For Range partitions, we have proposed the syntax which is as >>

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

emp_xxx, > CASE WHEN THEN emp_yyy, > CASE WHEN emp_zzz, > ELSE emp_default > END CASE; > ); > > Original-Nachricht > > Datum: Tue, 21 Apr 2009 10:46:41 -0300 > > Von: "Dickson S. Guedes" > > An: Kedar Potdar , pgsql-hackers

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

2009/4/21 Dickson S. Guedes > Em Ter, 2009-04-21 às 17:20 +0530, Kedar Potdar escreveu: > > Hi , > > > > We are working on a patch to automate partitioning in PostgreSQL. > > Nice. ":) > > > For Range partitions, we have proposed the syntax which is as > > follows > > > > (...) > > PARTITION BY R

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Em Ter, 2009-04-21 às 17:20 +0530, Kedar Potdar escreveu: > Hi , > > We are working on a patch to automate partitioning in PostgreSQL. Nice. ":) > For Range partitions, we have proposed the syntax which is as > follows > > (...) > PARTITION BY RANGE (emp_id) > ( > emp_500 (START 1 END 500),

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Hi Greg, Thanks for your response. Currently, such records are left in the overflow partition and its responsibility of user to insert them into partitioned-table which will then re-direct those to appropriate partitions. Regards, -- Kedar. On Tue, Apr 21, 2009 at 5:29 PM, Greg Stark wrote: >

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 12:50 PM, Kedar Potdar wrote: > I want to seek general opinion from the community on preferences between > user-friendly ‘Oracle’ syntax, and a more generic syntax that allows ‘gaps’ > in partition ranges? What happens to records in the overflow table when you add a new p

[HACKERS] Automating Partitions in PostgreSQL - Query on syntax

Hi , We are working on a patchto automate partitioning in PostgreSQL. For Range partitions, we have proposed the syntax which is as follows – *CREATE TABLE emp (* *emp_id int not null primary key,* *desig