Re: [HACKERS] Auto creation of Partitions

2007-03-12 Thread Robert Treat
On Saturday 10 March 2007 00:13, NikhilS wrote: Hi, Given that Simon wants to do away with having the master table APPENDed in the planning phase, this would be better. ISTM you're trading appending the master table for appending the DUMP partition, which afaict would give you

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread Zeugswetter Andreas ADI SD
Since partition is inheritance-based, a simple DROP or NO INHERIT will do the job to deal with the partition. Do we want to reinvent additional syntax when these are around and are documented? Well, if the syntax for adding a new partition eventually ends up as ALTER TABLE ADD

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread Simon Riggs
On Fri, 2007-03-09 at 11:48 +0530, NikhilS wrote: Hi, On 3/9/07, Shane Ambler [EMAIL PROTECTED] wrote: Note to Nikhil: Make sure the new syntax doesn't prevent partitions from being placed upon multiple tablespaces in some manner, at CREATE

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread NikhilS
Hi, Why would we support HASH partitions? If you did, the full syntax for hash clusters should be supported. In MySQL, Oracle, the syntax for HASH partitions seems to be similar to the one mentioned. I do not know much about hash clusters though. If we do the CHECK clauses like that then

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread Luke Lonergan
Lonergan; Zeugswetter Andreas ADI SD; Peter Eisentraut; pgsql-hackers@postgresql.org Subject:Re: [HACKERS] Auto creation of Partitions On Fri, 2007-03-09 at 11:48 +0530, NikhilS wrote: Hi, On 3/9/07, Shane Ambler [EMAIL PROTECTED] wrote: Note to Nikhil: Make sure

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread Simon Riggs
On Fri, 2007-03-09 at 07:40 -0500, Luke Lonergan wrote: What happens to the data when the function is dropped or replaced? Well, that wouldn't happen because you build in a dependency. I'm not working on this, so don't expect lots of detail. The idea is essentially to implement things the way

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread Jim Nasby
On Mar 9, 2007, at 3:31 AM, Zeugswetter Andreas ADI SD wrote: Since partition is inheritance-based, a simple DROP or NO INHERIT will do the job to deal with the partition. Do we want to reinvent additional syntax when these are around and are documented? Well, if the syntax for adding a new

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread Hannu Krosing
Ühel kenal päeval, R, 2007-03-09 kell 15:41, kirjutas Jim Nasby: On Mar 9, 2007, at 3:31 AM, Zeugswetter Andreas ADI SD wrote: Since partition is inheritance-based, a simple DROP or NO INHERIT will do the job to deal with the partition. Do we want to reinvent additional syntax when these

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread Robert Treat
On Friday 09 March 2007 01:23, NikhilS wrote: Hi, This follows on from the suggestion I made - taken along the lines of the subject auto creation of partitions where I suggested the syntax of partition check(month of mydatecol) and have a new partition created as data was entered. With

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread NikhilS
Hi, On 3/10/07, Hannu Krosing [EMAIL PROTECTED] wrote: Ühel kenal päeval, R, 2007-03-09 kell 15:41, kirjutas Jim Nasby: On Mar 9, 2007, at 3:31 AM, Zeugswetter Andreas ADI SD wrote: Since partition is inheritance-based, a simple DROP or NO INHERIT will do the job to deal with the

Re: [HACKERS] Auto creation of Partitions

2007-03-09 Thread NikhilS
Hi, Given that Simon wants to do away with having the master table APPENDed in the planning phase, this would be better. ISTM you're trading appending the master table for appending the DUMP partition, which afaict would give you no gain. If there are entries in the master table, I

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread NikhilS
Hi, Maybe, the dummy entry could be extended to contain the bounds (max/min) for each of the other involved partitions and they could be updated each time a DML happens across the partitions. That ways, an update to a particular partition needs to lock out the others, examine the dummy entries

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread NikhilS
Hi, There are 2 other reasons to favor triggers though: 1) People (Josh Drake comes to mind) have found that if you get over a tiny number of partitions, the performance of rules is abysmal. 2) I believe it should be possible to construct an update trigger that allows you to perform updates

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Simon Riggs
On Wed, 2007-03-07 at 22:32 -0500, Luke Lonergan wrote: Andreas, On 3/7/07 11:45 AM, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: Whoa, do you have anything to back that up ? Sure - when we start to consider designs that implement advanced data management features, we run into

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Simon Riggs
On Wed, 2007-03-07 at 21:27 -0700, Jim Nasby wrote: On Mar 7, 2007, at 3:26 AM, Simon Riggs wrote: If you know that the constraints on each of the tables is distinct, then building a UNIQUE index on each of the partitions is sufficient to prove that all rows in the combined

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Gregory Stark
Jim Nasby [EMAIL PROTECTED] writes: One possibility would be to require 3 functions for a partitioned table: one accepts the partitioning key and tells you what partition it's in, one that tells you what the minimum partitioning key for a partition would be, and one that tells you what the

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Zeugswetter Andreas ADI SD
The hard part there is checking that the partition constraints are distinct. If the partition constraints are added one at a time, you can use the predicate testing logic to compare the to-be-added partition's constraint against each of the already added constraints. That becomes an

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Jim C. Nasby
On Thu, Mar 08, 2007 at 01:52:27PM +0530, NikhilS wrote: I think it'd be great to make adding and removing partitions as simple as ALTER TABLE. I don't think that DELETE should be the mechanism to drop a partition, though. Again, DML statements shouldn't be performing DDL. Since partition

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Jim C. Nasby
On Thu, Mar 08, 2007 at 10:12:30AM +, Gregory Stark wrote: Jim Nasby [EMAIL PROTECTED] writes: One possibility would be to require 3 functions for a partitioned table: one accepts the partitioning key and tells you what partition it's in, one that tells you what the minimum

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Jim C. Nasby
One other thought... a lot of this discussion seems to parallel the debate from a while ago about whether SERIAL should act like a macro (ie: it just sets everything up and users are free to monkey under the hood afterwards), or whether it should be it's own 'closed-box' construct. Currently, we

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Shane Ambler
Note to Nikhil: Make sure the new syntax doesn't prevent partitions from being placed upon multiple tablespaces in some manner, at CREATE TABLE time. What if the syntax was something like - CREATE TABLE tabname ( ... ... ) PARTITION BY HASH(expr) | RANGE(expr) | LIST(expr)

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Shane Ambler
Jim C. Nasby wrote: On Thu, Mar 08, 2007 at 01:52:27PM +0530, NikhilS wrote: I think it'd be great to make adding and removing partitions as simple as ALTER TABLE. I don't think that DELETE should be the mechanism to drop a partition, though. Again, DML statements shouldn't be performing DDL.

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread NikhilS
Hi, On 3/9/07, Shane Ambler [EMAIL PROTECTED] wrote: Note to Nikhil: Make sure the new syntax doesn't prevent partitions from being placed upon multiple tablespaces in some manner, at CREATE TABLE time. What if the syntax was something like - CREATE TABLE tabname ( ... ... )

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread NikhilS
Hi, This follows on from the suggestion I made - taken along the lines of the subject auto creation of partitions where I suggested the syntax of partition check(month of mydatecol) and have a new partition created as data was entered. With this scenario dropping the partition when it was

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Peter Eisentraut
Am Mittwoch, 7. März 2007 07:44 schrieb NikhilS: Sure, but as Chris mentioned earlier, wouldn't it be useful to maintain uniqueness on a partition-by-partition basis too? Many things might be useful, but the aim of the table partitioning venture is believed to be the provision of a

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Simon Riggs
On Tue, 2007-03-06 at 18:31 +0530, NikhilS wrote: On 3/6/07, Peter Eisentraut [EMAIL PROTECTED] wrote: NikhilS wrote: iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. How will you

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread NikhilS
Hi, If you know that the constraints on each of the tables is distinct, then building a UNIQUE index on each of the partitions is sufficient to prove that all rows in the combined partitioned table are distinct also. The hard part there is checking that the partition constraints are distinct.

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Zeugswetter Andreas ADI SD
iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. How will you maintain a primary key in such a table, considering that indexes can't span multiple tables? Many partitioning schemes have (or can be made to have) only one possible

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread NikhilS
Hi, On 3/7/07, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. How will you maintain a primary key in such a table, considering that indexes can't span multiple tables? Many

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Alvaro Herrera
I am wondering if we can implement unique indexes across several tables (inheritance hierarchy) not by using a single, big index covering all the tables, but rather by inserting a dummy entry into each partition's unique index. This dummy entry would have an expanded CTID which would include the

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Luke Lonergan
Simon, On 3/7/07 5:26 AM, Simon Riggs [EMAIL PROTECTED] wrote: What is really needed is a data structure that allows range partitions to be accessed more efficiently. This could make adding partitions and deciding in which partition a specific value goes an O(logN) operation. I think we need

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Simon Riggs
On Wed, 2007-03-07 at 10:23 -0500, Luke Lonergan wrote: Simon, On 3/7/07 5:26 AM, Simon Riggs [EMAIL PROTECTED] wrote: What is really needed is a data structure that allows range partitions to be accessed more efficiently. This could make adding partitions and deciding in which

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Zeugswetter Andreas ADI SD
What is really needed is a data structure that allows range partitions to be accessed more efficiently. This could make adding partitions and deciding in which partition a specific value goes an O(logN) operation. I think we need to re-evaluate the inheritance mechanism for

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Joris Dobbelsteen
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alvaro Herrera Sent: woensdag 7 maart 2007 15:59 To: NikhilS Cc: Zeugswetter Andreas ADI SD; Peter Eisentraut; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Auto creation of Partitions I am wondering

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Luke Lonergan
Andreas, On 3/7/07 11:45 AM, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: Whoa, do you have anything to back that up ? Sure - when we start to consider designs that implement advanced data management features, we run into problems with the architecture of tables-tables-tables Here

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Jim Nasby
On Mar 6, 2007, at 9:13 AM, Shane Ambler wrote: NikhilS wrote: On 3/6/07, Peter Eisentraut [EMAIL PROTECTED] wrote: 1) Whether we should use triggers/rules for step number (iii) above. Maybe rules is the way to go. Since this would basically be a case of the updatable rules problem, you

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Jim Nasby
On Mar 6, 2007, at 4:57 AM, NikhilS wrote: iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. Since we want to eventually support 'global' indexes, I think we need to be really careful here. There's actually 3 possible cases: 1) Index*

Re: [HACKERS] Auto creation of Partitions

2007-03-07 Thread Jim Nasby
On Mar 7, 2007, at 3:26 AM, Simon Riggs wrote: If you know that the constraints on each of the tables is distinct, then building a UNIQUE index on each of the partitions is sufficient to prove that all rows in the combined partitioned table are distinct also. The hard part there is checking

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Peter Eisentraut
NikhilS wrote: iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. How will you maintain a primary key in such a table, considering that indexes can't span multiple tables? 1) Whether we should use triggers/rules for step number (iii) above.

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread NikhilS
Hi, On 3/6/07, Peter Eisentraut [EMAIL PROTECTED] wrote: NikhilS wrote: iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. How will you maintain a primary key in such a table, considering that indexes can't span multiple tables? We will

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Gregory Stark
NikhilS [EMAIL PROTECTED] writes: the intention is to use this information from the parent and make it a property of the child table. This will avoid the step for the user having to manually specify CREATE INDEX and the likes on all the children tables one-by-one. Missed the start of this

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread NikhilS
On 3/6/07, NikhilS [EMAIL PROTECTED] wrote: Hi, On 3/6/07, Gregory Stark [EMAIL PROTECTED] wrote: NikhilS [EMAIL PROTECTED] writes: the intention is to use this information from the parent and make it a property of the child table. This will avoid the step for the user having to

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread NikhilS
Hi, On 3/6/07, Gregory Stark [EMAIL PROTECTED] wrote: NikhilS [EMAIL PROTECTED] writes: the intention is to use this information from the parent and make it a property of the child table. This will avoid the step for the user having to manually specify CREATE INDEX and the likes on all the

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Shane Ambler
NikhilS wrote: Hi, On 3/6/07, Peter Eisentraut [EMAIL PROTECTED] wrote: NikhilS wrote: iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. How will you maintain a primary key in such a table, considering that indexes can't span multiple

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Chris Browne
[EMAIL PROTECTED] (Peter Eisentraut) writes: NikhilS wrote: iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it on to the children tables. How will you maintain a primary key in such a table, considering that indexes can't span multiple tables? On the one hand, I

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Martijn van Oosterhout
On Wed, Mar 07, 2007 at 02:43:39AM +1030, Shane Ambler wrote: I think a way can be devised to maintain the primary key and unique constraints. If a search is done on the parent table, the planner knows to rewrite the query as a union (or union all) of all child tables that relate to the

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Florian G. Pflug
Martijn van Oosterhout wrote: On Wed, Mar 07, 2007 at 02:43:39AM +1030, Shane Ambler wrote: I think a way can be devised to maintain the primary key and unique constraints. If a search is done on the parent table, the planner knows to rewrite the query as a union (or union all) of all child

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Peter Eisentraut
NikhilS wrote: We will not (I know its a hard thing to do :) ), the intention is to use this information from the parent and make it a property of the child table. This will avoid the step for the user having to manually specify CREATE INDEX and the likes on all the children tables

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Josh Berkus
Florian, This sounds like what is really needed is a way to lock a certain condition, namely the existance or non-existance of a record with certain values in certain fields. This would not only help this case, it would also help RI triggers, because those wouldn't have to acquire a share

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes: But when I say CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ... then I expect that the primary key will be enforced across all partitions. We currently sidestep that issue by not offering seemingly transparent partitioning. But if you

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Josh Berkus
Agreed, it needs to Just Work. I think it'd still be useful though if we only support auto-partitioning on the primary key, and that restriction avoids the indexing problem. +1 -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Andrew Dunstan
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: But when I say CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ... then I expect that the primary key will be enforced across all partitions. We currently sidestep that issue by not offering seemingly transparent

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Joris Dobbelsteen
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: dinsdag 6 maart 2007 19:45 To: pgsql-hackers@postgresql.org Cc: Florian G. Pflug; Martijn van Oosterhout; Shane Ambler; NikhilS; Peter Eisentraut Subject: Re: [HACKERS] Auto creation

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread NikhilS
Hi Shane, Maybe I'm looking at auto-maintenance which is beyond any current planning? Many of your suggestions are useful, but auto-maintenance will be beyond the current plan. Regards, Nikhils EnterpriseDB http://www.enterprisedb.com

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread NikhilS
Hi, On 3/7/07, Tom Lane [EMAIL PROTECTED] wrote: Peter Eisentraut [EMAIL PROTECTED] writes: But when I say CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ... then I expect that the primary key will be enforced across all partitions. We currently sidestep that issue by not offering

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Stefan Kaltenbrunner
Andrew Dunstan wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: But when I say CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ... then I expect that the primary key will be enforced across all partitions. We currently sidestep that issue by not offering seemingly

Re: [HACKERS] Auto creation of Partitions

2007-03-06 Thread Joshua D. Drake
Maybe. The most obvious use for automatic partitioning that I can think of would be based in the value of a timestamptz field rather than any PK. Of course I tend to work more in the OLTP field than in DW type apps, where other considerations might apply. I second that - partitioning on