TODO Request

"Joshua D. Drake" <jd@commandprompt.com> writes:

Can we get:

Multiple table indexes (for uniqueness across partitions for example)
Auto creations of partitions
Hash partitioning
Key partitioning
Sub partitioning

Added to the TODO list?

Perhaps a certain amount of specificity as to what these mean,
and why we need them, would be appropriate.

regards, tom lane

Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

Can we get:

Well this should be fun.

Multiple table indexes (for uniqueness across partitions for example)
Auto creations of partitions

This would be something like:

create table foo () partition by ...

Hash partitioning

Partitioning by HASH is used primarily to ensure an even distribution of
data among a predetermined number of partitions.

Key partitioning

Partitioning by key is similar to partitioning by hash, except that
where hash partitioning employs a user-defined expression.

Sub partitioning

Subpartitioning � also known as composite partitioning � is the further
division of each partition in a partitioned table. (partitions that have
partitions)

Added to the TODO list?

Perhaps a certain amount of specificity as to what these mean,
and why we need them, would be appropriate.

For reference I am directly apply my fair use rights to the above per
the MySQL development docs. Reference below:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Yes I am fully aware that we don't need to do something just because
MySQL does it. However, Oracle has similar functionality and I would
like to see us keep up :)

Of course I would like it to be done correctly :)

Sincerely,

Joshua D. Drake

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

Can we get:

Multiple table indexes (for uniqueness across partitions for example)
Auto creations of partitions
Hash partitioning
Key partitioning
Sub partitioning

Added to the TODO list?

Perhaps a certain amount of specificity as to what these mean,
and why we need them, would be appropriate.

Further on this is an additional reference:

http://www.psoug.org/reference/partitions.html

We should also probably add:

Allow planner to correctly use indexes on min/max across partitions

Sincerely,

Joshua D. Drake

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Tue, Aug 29, 2006 at 03:53:57PM -0700, Joshua D. Drake wrote:

Hello,

Can we get:

Multiple table indexes (for uniqueness across partitions for example)

Before any of the below happen, I think it'd be good to get a cleaner
way to define partitions; one that didn't involve manually messing with
constraints, etc.

Auto creations of partitions

That would be nice, though if we had a built-in job facility of some
kind it wouldn't be needed for time-based partitioning.

Hash partitioning
Key partitioning
Sub partitioning

Is there anything stopping those from being done right now? The only
thing I can think of that we're missing is an optimization where a
partition with a single key doesn't contain that key's data. Currently,
this can be done with "UNION VIEW partitioning", but perhaps there's
some more clever way to do it in the inheritance case.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Added to 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.

* Allow auto-selection of partitioned tables for min/max() operations

I didn't add subparitions because that seems pretty complicated.

---------------------------------------------------------------------------

Joshua D. Drake wrote:

Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

Can we get:

Well this should be fun.

Multiple table indexes (for uniqueness across partitions for example)
Auto creations of partitions

This would be something like:

create table foo () partition by ...

Hash partitioning

Partitioning by HASH is used primarily to ensure an even distribution of
data among a predetermined number of partitions.

Key partitioning

Partitioning by key is similar to partitioning by hash, except that
where hash partitioning employs a user-defined expression.

Sub partitioning

Subpartitioning ? also known as composite partitioning ? is the further
division of each partition in a partitioned table. (partitions that have
partitions)

Added to the TODO list?

Perhaps a certain amount of specificity as to what these mean,
and why we need them, would be appropriate.

For reference I am directly apply my fair use rights to the above per
the MySQL development docs. Reference below:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Yes I am fully aware that we don't need to do something just because
MySQL does it. However, Oracle has similar functionality and I would
like to see us keep up :)

Of course I would like it to be done correctly :)

Sincerely,

Joshua D. Drake

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Added to 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.

* Allow auto-selection of partitioned tables for min/max() operations

I didn't add subparitions because that seems pretty complicated.

Thanks for this.

What about the other partioning types? And complicated should be what we
are after :)

Sincerely,

Joshua D. Drake

---------------------------------------------------------------------------

Joshua D. Drake wrote:

Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

Can we get:

Well this should be fun.

Multiple table indexes (for uniqueness across partitions for example)
Auto creations of partitions

This would be something like:

create table foo () partition by ...

Hash partitioning

Partitioning by HASH is used primarily to ensure an even distribution of
data among a predetermined number of partitions.

Key partitioning

Partitioning by key is similar to partitioning by hash, except that
where hash partitioning employs a user-defined expression.

Sub partitioning

Subpartitioning ? also known as composite partitioning ? is the further
division of each partition in a partitioned table. (partitions that have
partitions)

Added to the TODO list?

Perhaps a certain amount of specificity as to what these mean,
and why we need them, would be appropriate.

For reference I am directly apply my fair use rights to the above per
the MySQL development docs. Reference below:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Yes I am fully aware that we don't need to do something just because
MySQL does it. However, Oracle has similar functionality and I would
like to see us keep up :)

Of course I would like it to be done correctly :)

Sincerely,

Joshua D. Drake

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Joshua D. Drake wrote:

Bruce Momjian wrote:

Added to 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.

* Allow auto-selection of partitioned tables for min/max() operations

I didn't add subparitions because that seems pretty complicated.

Thanks for this.

What about the other partioning types? And complicated should be what we

Uh, what other types? I see key, hash, and sub listed below.

are after :)

It is not clear a complex solution would be accepted by the community.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Joshua D. Drake wrote:

Bruce Momjian wrote:

Added to 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.

* Allow auto-selection of partitioned tables for min/max() operations

I didn't add subparitions because that seems pretty complicated.

Thanks for this.

What about the other partioning types? And complicated should be what we

Uh, what other types? I see key, hash, and sub listed below.

Yeah, but I don't see them listed in the TODO... were you being implicit?

Joshua D. Drake

are after :)

It is not clear a complex solution would be accepted by the community.

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Ühel kenal päeval, T, 2006-08-29 kell 22:12, kirjutas Joshua D. Drake:

Auto creations of partitions

This would be something like:

create table foo () partition by ...

from the referenced MySQL manual entry

CREATE TABLE members (
...
joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

Do you have any idea how this should work ?

What date range should go into which partition ?

For reference I am directly apply my fair use rights to the above per
the MySQL development docs. Reference below:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Yes I am fully aware that we don't need to do something just because
MySQL does it. However, Oracle has similar functionality and I would
like to see us keep up :)

Of course I would like it to be done correctly :)

Do you know if ther is anything about partitioning in any ISO/ANSI SQL
standards ?

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Hannu Krosing

Ühel kenal päeval, T, 2006-08-29 kell 22:12, kirjutas Joshua D. Drake:

Auto creations of partitions

This would be something like:

create table foo () partition by ...

from the referenced MySQL manual entry

CREATE TABLE members (
...
joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

Do you have any idea how this should work ?

What date range should go into which partition ?

Since we don't have any knowledge about the date ranges in question, and the fact that they could change over time, I think the only stable way to handle this scenario would be to use a hash function which had 6 buckets (something like 'date % 6' could work).

I do see an issue, if someone wanted to change the number of partitions in use, since it would have to rehash the table, and move data around.

I don't see any other way to handle this, but I might not be thinking hard enough.

-rocco

Rocco Altier wrote:

From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Hannu Krosing

�hel kenal p�eval, T, 2006-08-29 kell 22:12, kirjutas Joshua D. Drake:

Auto creations of partitions

This would be something like:

create table foo () partition by ...

from the referenced MySQL manual entry

CREATE TABLE members (
...
joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

Do you have any idea how this should work ?

What date range should go into which partition ?

Since we don't have any knowledge about the date ranges in question,
and the fact that they could change over time, I think the only stable
way to handle this scenario would be to use a hash function which had
6 buckets (something like 'date % 6' could work).

IMHO we shouldn't be giving too many partitioning options until we solve
the important problems it brings with it, like FKs or unique constraints
not working across the hierarchy.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.