TODO Request

Hello,

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?

Joshua D. Drake

--

=== 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" <jd(at)commandprompt(dot)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(at)commandprompt(dot)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(at)postgresql(dot)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(at)commandprompt(dot)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(at)postgresql(dot)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(at)pervasive(dot)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(at)commandprompt(dot)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(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian bruce(at)momjian(dot)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(at)commandprompt(dot)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(at)postgresql(dot)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(at)postgresql(dot)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(at)momjian(dot)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