Auto creation of Partitions
Hi,
This is to get feedback to meet the following TODO:
- Simplify ability to create partitioned tables
This would allow creation of partitioned tables without requiring
creation of rules for INSERT/UPDATE/DELETE, and constraints for rapid
partition selection. Options could include range and hash partition
selection.
There was some discussion on the pgsql mailing lists, which lead to the
above TODO:
http://archives.postgresql.org/pgsql-hackers/2006-09/msg00189.php
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01874.php
We can have the following syntax to support auto creation of partitions in
Postgresql:
CREATE TABLE tabname (
...
...
) PARTITION BY
HASH(expr)
| RANGE(expr)
| LIST(expr)
[PARTITIONS num_partitions] /* will apply to HASH only for now*/
[PARTITION partition_name CHECK(...),
PARTITION partition_name CHECK(...)
...
];
Here "expr" will be one of the column names as specified for the master
table. Once we finalize the syntax, the above statement would end up
carrying out the following activities (disclaimer: we might add or remove
some activities based on the discussion here).
i ) Create master table.
ii) Create children tables based on the number of partitions specified and
make them inherit from the master table.
iii) Auto generate rules (or triggers?) using the checks mentioned for the
partitions, to handle INSERTs/DELETEs/UPDATEs to navigate them to the
appropriate child. Note that checks specified directly on the master table
will get inherited automatically.
iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it
on to the children tables.
v) If possible add CHECK (false) to the master table to avoid any activity
on it.
Some questions remain as to:
1) Whether we should use triggers/rules for step number (iii) above. Maybe
rules is the way to go.
2) What other attributes (access permissions e.g.) of the master along with
the ones specified in (iv) should be passed on to the children.
3) Some implementation specific issue e.g. whether SPI_execute would be a
good way of creating these rules.
Comments appreciated,
Regards,
Nikhils
EnterpriseDB http://www.enterprisedb.com
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.
Maybe rules is the way to go.
Since this would basically be a case of the updatable rules problem, you
should review those discussions in the past to check whether the issues
mentioned there don't interfere with that plan.
2) What other attributes (access permissions e.g.) of the master
along with the ones specified in (iv) should be passed on to the
children.
Moreover, how are later changes of those attributes propagated?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Hi,
On 3/6/07, Peter Eisentraut <peter_e@gmx.net> 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 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 one-by-one.
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
should review those discussions in the past to check whether the issues
mentioned there don't interfere with that plan.
The rules mentioned here will be to specify that all the
inserts/updates/deletes should go into proper children tables instead of the
parent. I do not see the updateable rules problem with regards to this, but
will check out the archives for discussion on this related to partitioning.
2) What other attributes (access permissions e.g.) of the master
along with the ones specified in (iv) should be passed on to the
children.Moreover, how are later changes of those attributes propagated?
Once created, this will be a normal inheritance relationship between the
tables and all the existing commands will apply to both the parent and the
child.
The basic idea here is to automate as many things as possible at partition
creation time. The user is free to make additional changes to the involved
tables later too.
Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com
"NikhilS" <nikkhils@gmail.com> 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 thread. A while back I had intended to add WITH
INDEXES to CREATE TABLE LIKE. That would let you create a tale LIKE parent
WITH CONSTRAINTS WITH INDEXES and get pretty much a perfect table ready for
adding to the inheritance structure.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Hi,
On 3/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
"NikhilS" <nikkhils@gmail.com> 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 havingto
manually specify CREATE INDEX and the likes on all the children tables
one-by-one.Missed the start of this thread. A while back I had intended to add WITH
INDEXES to CREATE TABLE LIKE. That would let you create a tale LIKE parent
WITH CONSTRAINTS WITH INDEXES and get pretty much a perfect table ready
for
adding to the inheritance structure.
Yeah, this one aims to do pretty much the above as part of the auto creation
of the inheritance-based partitions.
Regards,
Nikhils
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
--
EnterpriseDB http://www.enterprisedb.com
On 3/6/07, NikhilS <nikkhils@gmail.com> wrote:
Hi,
On 3/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
"NikhilS" <nikkhils@gmail.com> 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 userhaving to
manually specify CREATE INDEX and the likes on all the children tables
one-by-one.Missed the start of this thread. A while back I had intended to add WITH
INDEXES to CREATE TABLE LIKE. That would let you create a tale LIKE
parent
WITH CONSTRAINTS WITH INDEXES and get pretty much a perfect table ready
for
adding to the inheritance structure.Yeah, this one aims to do pretty much the above as part of the auto
creation of the inheritance-based partitions.
And to add, maybe if there is consensus/demand for the WITH INDEXES idea
mentioned above too, I could work on it as well.
Regards,
Nikhils
Regards,
Nikhils
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com--
EnterpriseDB http://www.enterprisedb.com
--
EnterpriseDB http://www.enterprisedb.com
NikhilS wrote:
Hi,
On 3/6/07, Peter Eisentraut <peter_e@gmx.net> 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 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 one-by-one.
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 where clause, or all child tables if the where clause is not on the
column/s used to partition, then this concept should be able to be
converted to indexes as well, so that when a primary or unique index
from a child table is inserted to, then each of the related child
indexes is consulted to ensure uniqueness.
This would only apply if the partitioning was not done by the primary or
unique column.
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
should review those discussions in the past to check whether the issues
mentioned there don't interfere with that plan.The rules mentioned here will be to specify that all the
inserts/updates/deletes should go into proper children tables instead of
the
parent. I do not see the updateable rules problem with regards to this, but
will check out the archives for discussion on this related to partitioning.
I would think that a trigger would be a better choice as I see the need
(or at least the possibility) for more than just a rewrite. When a value
is inserted that is outside of a condition currently covered by an
existing child table then a new child will need to be spawned to contain
the new data.
So say we partition by year and month of a date column? As new dates are
added new child tables would be created each month. Or is this beyond
the current plan and left to manual creation?
Will ALTER TABLE be extended to handle partitions? This will allow
partitioning existing tables (maybe without blocking access?) and allow
things like ALTER TABLE mytable ADD PARTITION (mydate within 200703)
and ALTER TABLE mytable DROP PARTITION (mydate within 199912) or would
dropping be covered by DELETE FROM mytable where mydate <= 199912 ?
Could such a syntax be devised for date columns? (month of mydate) or
similar to auto create partitions based on the year and month of a date
column? or will we just do CHECK(mydatecol >= 1/3/07 and mydatecol <=
31/3/07) for each month of data? Also (day of mydatecol) to partition
based on year and day of year.
Another syntax possibility - range(myserialcol of 500000) where new
child tables are created every 500000 rows?
Maybe I'm looking at auto-maintenance which is beyond any current planning?
--
Shane Ambler
pgSQL@Sheeky.Biz
Get Sheeky @ http://Sheeky.Biz
peter_e@gmx.net (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 seem to recall seeing "multiple-table-spanning
indices" being on the TODO list.
On the other, it may be that this sort of partitioning is only usable
for scenarios where it is only needful to maintain uniqueness on a
partition-by-partition basis.
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
should review those discussions in the past to check whether the issues
mentioned there don't interfere with that plan.2) What other attributes (access permissions e.g.) of the master
along with the ones specified in (iv) should be passed on to the
children.Moreover, how are later changes of those attributes propagated?
I hear rumour of there being a more comprehensive proposal on this in
the works...
--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://linuxfinances.info/info/finances.html
Rules of the Evil Overlord #189. "I will never tell the hero "Yes I
was the one who did it, but you'll never be able to prove it to that
incompetent old fool." Chances are, that incompetent old fool is
standing behind the curtain." <http://www.eviloverlord.com/>
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 where clause, or all child tables if the where clause is not on the
column/s used to partition, then this concept should be able to be
converted to indexes as well, so that when a primary or unique index
from a child table is inserted to, then each of the related child
indexes is consulted to ensure uniqueness.
But that's where it breaks down: you not only need to check that the
row you're inserting is unique, you need to make sure that other people
trying to insert the same value see it.
After all, a unique index does contain multiple instances of any
particular value, it's just that the DB has verified that none of these
are visible to the same transaction. If there is a collision a lock is
taken that waits until the colliding insert commits or rejects.
With b-tree it's easy, every value can only appear in one place, so
it's a very simple lock. The reason why GiST doesn't support unique
indexes is due to the fact that any particular value could appear in
many places, so to stop concurrent inserts you need to lock *every*
page in the tree that another backend might want to insert the record.
I fear this will also be a problem for indexes over multiple tables, you
will need to lock the page in every index that some other DB might want
to insert the value into. All while avoiding deadlocks.
I think the problems are related. If you can solve it for multiple
tables, you can solve it for GiST indexes also.
This would only apply if the partitioning was not done by the primary or
unique column.
That's the easy case, no problem there.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
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 tables that relate to
the where clause, or all child tables if the where clause is not on the
column/s used to partition, then this concept should be able to be
converted to indexes as well, so that when a primary or unique index
from a child table is inserted to, then each of the related child
indexes is consulted to ensure uniqueness.But that's where it breaks down: you not only need to check that the
row you're inserting is unique, you need to make sure that other people
trying to insert the same value see it.
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 lock on the referenced rows anymore.
As you pointed out, this would also make unique GiST indices possible
No real idea how to do this, though :-(
greetings, Florian Pfluge
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
one-by-one.
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 are planning to offer that, the
unique index issue needs to be solved, and I see nothing in your plan
about that.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> 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 are planning to offer that, the
unique index issue needs to be solved, and I see nothing in your plan
about that.
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.
regards, tom lane
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 lock on the referenced rows anymore.
That's called "predicate locking" and it's very, very hard to do.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
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
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> 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 are planning to offer that, the
unique index issue needs to be solved, and I see nothing in your plan
about that.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.
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.
cheers
andrew
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] 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 of PartitionsFlorian,
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 helpthis case,
it would also help RI triggers, because those wouldn't have
to acquire
a share lock on the referenced rows anymore.
That's called "predicate locking" and it's very, very hard to do.
That's definitely not needed.
Rather something good such that we can finally enforce RI ourselves in
the general case. This is currently not possible to do easily, except in
C code. This means we need to look at all the rows that exists, but are
normally be invisible to our view of the database. Still I'm not sure
about all cases, as the MVCC model is quite tricky and I'm not sure
whether my idea's about it are valid.
The basic idea is that you need to guarentee the constraint for the
'single underlaying model' (with everything visible) and for your view
(under your visibility rules). I believe, but are not certain, that
under these conditions any (valid) snapshot will obey the desired
constraints.
- Joris Dobbelsteen
Greetings all,
I wrote this patch about a week ago to introduce myself to coding on
PostgreSQL. I wasn't entirely sure what the 'INCLUDING INDEXES' option
was meant to do, so I held off submitting it until I could get around to
asking about that and tweaking the documentation to reflect the patch.
By useful coincidence the thread "Auto creation of Partitions" had this
post in it, which made the intent of the option clear enough for me to
go ahead and see what people think of this.
Gregory Stark wrote:
"NikhilS" <nikkhils@gmail.com> 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 thread. A while back I had intended to add WITH
INDEXES to CREATE TABLE LIKE. That would let you create a tale LIKE parent
WITH CONSTRAINTS WITH INDEXES and get pretty much a perfect table ready for
adding to the inheritance structure.
So, that's what this patch does. When a table is created with 'CREATE
TABLE ... LIKE parent INCLUDING INDEXES' this iterates over the parent
table indexes looking for constraint indexes, and alters the
CreateStmtContext to include equivalent indexes on the child table.
This is probably a somewhat naive implementation, being a first attempt.
I wasn't sure what sort of lock to place on the parent indexes or what
tablespace the new indexes should be created in. Any help improving it
would be appreciated.
Thank you,
-Trevor Hardcastle
Attachments:
like_including_indexes.patchtext/plain; name=like_including_indexes.patchDownload+96-19
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
Hi,
On 3/7/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Eisentraut <peter_e@gmx.net> 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 are planning to offer that, the
unique index issue needs to be solved, and I see nothing in your plan
about that.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.regards, tom lane
Sure, but as Chris mentioned earlier, wouldn't it be useful to maintain
uniqueness on a
partition-by-partition basis too?
Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com
Andrew Dunstan wrote:
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> 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 are planning to offer
that, the unique index issue needs to be solved, and I see nothing in
your plan about that.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.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 some kind of timestamp field is a common
usecase here too ...
Stefan
