Thread: 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.
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.
Attachment
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@pointblue.com.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.
Regards,
--
Kedar.
On Tue, Jun 9, 2009 at 12:26 AM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote:
On Mon, Jun 8, 2009 at 1:38 PM, Grzegorz Jaskiewicz <gj@pointblue.com.pl> wrote:there is no pg_partition.h file in the patch, please send it
>
> make[3]: *** No rule to make target `../../../src/include/catalog/pg_partition.h', needed by `postgres.bki'. Stop.
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
Attachment
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@pointblue.com.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
Attachment
Hi,
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
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.<br /><br />Is there any problem with formattingof the file? May be some characters('\') missing in conversion?<br /><br /><div class="gmail_quote"> On Tue, Jun9, 2009 at 4:14 PM, gj <span dir="ltr"><<a href="mailto:gj@pointblue.com.pl">gj@pointblue.com.pl</a>></span> wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:1ex;"> still doesn't work:<br /><br /> make[1]: Leaving directory `/home/gjaskie/Projects/postgres/pgsql/config'<br/> /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 14: a.keyorder,:<br/> command not found<br /> /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 15: where: command not<br/> found<br /> : command not foundcts/postgres/pgsql/partition.sh: line 16:<br /><br /> Please make sure you test patchesbefore sending here, on clean checkout!.<br /><br /><br /><br /></blockquote></div><br />
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.
I am sorry for the late reply. :(
Please find inline my comments.
On Tue, Jun 9, 2009 at 2:54 PM, Nikhil Sontakke <nikhil.sontakke@enterprisedb.com> wrote:
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.
Kedar >> I will look at it once done with catagorizing partition triggers. I am inclined toward using a new relkind like RELKIND_OVERFLOW.
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.
Kedar >> Yeah.
Kedar >> Thanks! Looking forward to your continued co-operation.
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.potdar@gmail.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.
--
Regards,
--
Kedar.
Read 'me' - http://kpotdar.livejournal.com
Regards,
--
Kedar.
On Tue, Jul 7, 2009 at 10:18 AM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote:
On Mon, Jun 8, 2009 at 9:02 AM, Kedar Potdar<kedar.potdar@gmail.com> wrote:if you are still working on this, can you please update the patch to cvs head?
> Hi,
>
> PFA patch, readme for automating partitions in PostgreSQL 8.4 Beta 2 and
> testcases.
>
--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