Patch for automating partitions in PostgreSQL 8.4 Beta 2

Hi,

PFA patch, readme for automating partitions in PostgreSQL 8.4 Beta 2 and
testcases.

The patch automates table partitioning to support Range and Hash partitions.
Please refer to attached readme file for further details.

The syntax used conforms to most of the suggestions mentioned in
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring
the following:
-- Specification of partition names is optional. System will be able to
generate partition names in such cases.
-- Sub partitioning

Regards,
--
Kedar.

Kedar,

Added to first CommitFest of 8.5. Thanks for the nice test case.

Folks who are not busy with 8.4 are urged to test this as soon as you can.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

make -C catalog all
( echo src/backend/catalog/catalog.o src/backend/catalog/dependency.o
src/backend/catalog/heap.o src/backend/catalog/index.o src/backend/
catalog/indexing.o src/backend/catalog/namespace.o src/backend/catalog/
aclchk.o src/backend/catalog/pg_aggregate.o src/backend/catalog/
pg_constraint.o src/backend/catalog/pg_conversion.o src/backend/
catalog/pg_depend.o src/backend/catalog/pg_enum.o src/backend/catalog/
pg_inherits.o src/backend/catalog/pg_largeobject.o src/backend/catalog/
pg_namespace.o src/backend/catalog/pg_operator.o src/backend/catalog/
pg_proc.o src/backend/catalog/pg_shdepend.o src/backend/catalog/
pg_type.o src/backend/catalog/storage.o src/backend/catalog/
toasting.o ) >objfiles.txt
make[3]: *** No rule to make target `../../../src/include/catalog/
pg_partition.h', needed by `postgres.bki'. Stop.
make[2]: *** [catalog-recursive] Error 2

(that's on mac os x).

On Mon, Jun 8, 2009 at 1:38 PM, Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> wrote:
>
> make[3]: *** No rule to make target `../../../src/include/catalog/pg_partition.h', needed by `postgres.bki'.  Stop.

there is no pg_partition.h file in the patch, please send it

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

PFA the required header file.

Regards,
--
Kedar.

On Tue, Jun 9, 2009 at 12:26 AM, Jaime Casanova <
jcasanov(at)systemguards(dot)com(dot)ec> wrote:

> On Mon, Jun 8, 2009 at 1:38 PM, Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl>
> wrote:
> >
> > make[3]: *** No rule to make target
> `../../../src/include/catalog/pg_partition.h', needed by `postgres.bki'.
> Stop.
>
> there is no pg_partition.h file in the patch, please send it
>
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>

gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -
Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -
fwrapv -bundle -multiply_defined suppress regress.o -
bundle_loader ../../../src/backend/postgres -L../../../src/port -o
regress.so
cp ../../../contrib/spi/refint.so refint.so
cp ../../../contrib/spi/autoinc.so autoinc.so
gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -
Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -
fwrapv pg_regress.o pg_regress_main.o -L../../../src/port -Wl,-
dead_strip_dylibs -lpgport -lz -lreadline -lm -o pg_regress
make -C config all
make[1]: Nothing to be done for `all'.

/bin/sh: /Users/gj/Projects/postgres-head/pgsql/partition.sh: No such
file or directory

make: *** [all] Error 127

PFA. This file is to be kept in 'pgsql_init' base directory.

On Tue, Jun 9, 2009 at 12:54 PM, Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl>wrote:

> gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
> -bundle -multiply_defined suppress regress.o -bundle_loader
> ../../../src/backend/postgres -L../../../src/port -o regress.so
> cp ../../../contrib/spi/refint.so refint.so
> cp ../../../contrib/spi/autoinc.so autoinc.so
> gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
> pg_regress.o pg_regress_main.o -L../../../src/port -Wl,-dead_strip_dylibs
> -lpgport -lz -lreadline -lm -o pg_regress
> make -C config all
> make[1]: Nothing to be done for `all'.
>
> /bin/sh: /Users/gj/Projects/postgres-head/pgsql/partition.sh: No such file
> or directory
>
> make: *** [all] Error 127
>
>
>

Hi,

>
> The patch automates table partitioning to support Range and Hash
> partitions. Please refer to attached readme file for further details.
>
> The syntax used conforms to most of the suggestions mentioned in
> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring
> the following:
> -- Specification of partition names is optional. System will be able to
> generate partition names in such cases.
> -- Sub partitioning
>

Some comments based on a brief glance of the patch:

- The logic to execute the partition triggers last still needs some more
work IMHO. Relying on just the names might not get accepted. I think you
should pay attention to Andrew Dunstan's suggestion in an earlier mail to
have tgkind enumerations to generalize the same or discuss it further.

"the scheme should turn tgisconstraint into a multi-valued item (tgkind: 'u'
= userland, 'c'= constraint, 'p' = partition or some such)."

- Similarly, assigning "of_relname_oid" names to overflow tables also might
not work. The best way ahead could be to invent a new relkind
RELKIND_OVERFLOW to handle it. Or maybe we can have a new schema pg_overflow
to store the overflow relation with the same name (suffixed with _overflow
to make it clearer) as the parent relation too. The relkind solution might
be cleaner though. This might need further discussion. In general, it is
definitely not a bad idea to discuss such sub-problems on the list :)

- Am I reading the patch correctly that you do not end up creating indexes
on the children tables? That is a big problem!

- You can remove the remnants of the first patch like the MutateColumnRefs()
function, for example (I agree this is WIP, but unwanted/unused functions
unnecessarily add to the size). With large patches, the more precise the
patch, the better it will be for reviewers/readers.

Great work all in all!

Regards,
Nikhils
--
http://www.enterprisedb.com

still doesn't work:

make[1]: Leaving directory `/home/gjaskie/Projects/postgres/pgsql/config'
/home/gjaskie/Projects/postgres/pgsql/partition.sh: line 14: a.keyorder,:
command not found
/home/gjaskie/Projects/postgres/pgsql/partition.sh: line 15: where: command not
found
: command not foundcts/postgres/pgsql/partition.sh: line 16:

Please make sure you test patches before sending here, on clean checkout!.

I did a fresh checkout and applied patch and added files and it works at my
end.

Is there any problem with formatting of the file? May be some
characters('\') missing in conversion?

On Tue, Jun 9, 2009 at 4:14 PM, gj <gj(at)pointblue(dot)com(dot)pl> wrote:

> still doesn't work:
>
> make[1]: Leaving directory `/home/gjaskie/Projects/postgres/pgsql/config'
> /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 14: a.keyorder,:
> command not found
> /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 15: where: command
> not
> found
> : command not foundcts/postgres/pgsql/partition.sh: line 16:
>
> Please make sure you test patches before sending here, on clean checkout!.
>
>
>
>

On Tue, 9 Jun 2009 at 13:52:08, Kedar Potdar wrote:

> I did a fresh checkout and applied patch and added files and it works at my
> end.
>
> Is there any problem with formatting of the file? May be some
> characters('\') missing in conversion?
>
For one, I think you should put it around in quotes, when you echo something out
- just in case.
Second, isn't there any better way to do it, than in shell script ?
Shouldn't that bit be called on make check, not on build (make).?

Hi Nikhil,

I am sorry for the late reply. :(

Please find inline my comments.

On Tue, Jun 9, 2009 at 2:54 PM, Nikhil Sontakke <
nikhil(dot)sontakke(at)enterprisedb(dot)com> wrote:

> Hi,
>
>
>>
>> The patch automates table partitioning to support Range and Hash
>> partitions. Please refer to attached readme file for further details.
>>
>> The syntax used conforms to most of the suggestions mentioned in
>> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php,
>> barring the following:
>> -- Specification of partition names is optional. System will be able to
>> generate partition names in such cases.
>> -- Sub partitioning
>>
>
> Some comments based on a brief glance of the patch:
>
> - The logic to execute the partition triggers last still needs some more
> work IMHO. Relying on just the names might not get accepted. I think you
> should pay attention to Andrew Dunstan's suggestion in an earlier mail to
> have tgkind enumerations to generalize the same or discuss it further.
>
> "the scheme should turn tgisconstraint into a multi-valued item (tgkind:
> 'u' = userland, 'c'= constraint, 'p' = partition or some such)."
>

Kedar >> I am working on to turn tgisconstraint into a multi-values item to
categorize user, constraint and partition triggers. In doing so, I am
thinking of adding adding 'PARTITION' keyword to existing syntax to create
partition triggers i.e. CREATE PARTITION TRIGGER ... The partition triggers
would now be identified with the type rather than naming scheme though
naming scheme for partition triggers would still be there.

>
> - Similarly, assigning "of_relname_oid" names to overflow tables also might
> not work. The best way ahead could be to invent a new relkind
> RELKIND_OVERFLOW to handle it. Or maybe we can have a new schema pg_overflow
> to store the overflow relation with the same name (suffixed with _overflow
> to make it clearer) as the parent relation too. The relkind solution might
> be cleaner though. This might need further discussion. In general, it is
> definitely not a bad idea to discuss such sub-problems on the list :)
>

Kedar >> I will look at it once done with catagorizing partition triggers. I
am inclined toward using a new relkind like RELKIND_OVERFLOW.

>
>
> - Am I reading the patch correctly that you do not end up creating indexes
> on the children tables? That is a big problem!

Kedar >> As Srinath K is working on global indexes, the merge of
partitioning and global indexes should be able to extend indexes created on
partitioned table to children as well.

>
>
> - You can remove the remnants of the first patch like the
> MutateColumnRefs() function, for example (I agree this is WIP, but
> unwanted/unused functions unnecessarily add to the size). With large
> patches, the more precise the patch, the better it will be for
> reviewers/readers.

Kedar >> Yeah.

>
>
> Great work all in all!

Kedar >> Thanks! Looking forward to your continued co-operation.

>
>
> Regards,
> Nikhils
> --
> http://www.enterprisedb.com
>

On Mon, Jun 8, 2009 at 9:02 AM, Kedar Potdar<kedar(dot)potdar(at)gmail(dot)com> wrote:
> Hi,
>
> PFA patch, readme for automating partitions in PostgreSQL 8.4 Beta 2 and
> testcases.
>

if you are still working on this, can you please update the patch to cvs head?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Yes. I am working to integrate some of the comments received for the patch.
I would be able to post latest patch in the next week.

Regards,
--
Kedar.

On Tue, Jul 7, 2009 at 10:18 AM, Jaime Casanova <
jcasanov(at)systemguards(dot)com(dot)ec> wrote:

> On Mon, Jun 8, 2009 at 9:02 AM, Kedar Potdar<kedar(dot)potdar(at)gmail(dot)com>
> wrote:
> > Hi,
> >
> > PFA patch, readme for automating partitions in PostgreSQL 8.4 Beta 2 and
> > testcases.
> >
>
> if you are still working on this, can you please update the patch to cvs
> head?
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>

--
Regards,
--
Kedar.

Read 'me' - http://kpotdar.livejournal.com

Anyone knows what's the latest on that patch ?
To be honest, this was the thing that I was looking forward most in
8.5 ... (and probably not only me alone).

Grzegorz Jaskiewicz wrote:
> Anyone knows what's the latest on that patch ?
> To be honest, this was the thing that I was looking forward most in
> 8.5 ... (and probably not only me alone).
We are also interested in integrating our autopartitioning patch for
COPY with that implementation. I can help with the partitioning
implementation and/or testing of that feature since this is of interest
for Aster too.

Emmanuel

--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com