Partition insert trigger using C language

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi All,

Inspired by Charles' thread and the work of Emmanuel [1], I have made some experiments trying to create a trigger to make partitioning using C language.

The first attempt was not good, I tried to use SPI [2] to create a query to insert into the correct child table, but it took almost no improvement compared with the PL/pgSQL code.

Then, I used the Emmanuel's code and digged into the PG source code (basically at copy.c) to create a trigger function that insert to the partitioned table direct (using heap_insert instead of SQL) [3], and the improvement was about 100% (not 4/5 times like got by Emmanuel ). The function has no other performance trick, like caching the relations or something like that.

The function does partition based on month/year, but it's easy to change to day/month/year or something else. And, of course, it's not ready for production, as I'm not sure if it can break things.

The tests were made using a PL/pgSQL code to insert 1 milion rows, and I don't know if this is a real-life-like test (probably not). And there is a table partitioned by month, with a total of 12 partitions (with the insertions randomly distributed through all 2012).

I put the trigger and the experiments on a repository at GitHub:

https://github.com/matheusoliveira/pg_partitioning_tests

I don't know if this is the right list for the topic, and I thought the old one has to many messages, so I created this one to show this tirgger sample and see if someone has a comment about it.

PS: I'd be glad if someone could revise the code to make sure it don't brake in some corner case. I'm made some tests [4], but not sure if they covered everything.
PS2: It surely will not work on old versions of PostgreSQL, perhaps not even 9.1 (not tested).


[1] http://archives.postgresql.org/pgsql-hackers/2008-12/msg01221.php and http://archives.postgresql.org/pgsql-performance/2012-12/msg00189.php
[2] https://github.com/matheusoliveira/pg_partitioning_tests/blob/master/src/spi/partition_insert_trigger_spi.c
[3] https://github.com/matheusoliveira/pg_partitioning_tests/blob/master/src/partition_insert_trigger.c
[4] https://github.com/matheusoliveira/pg_partitioning_tests/tree/master/test/regress


Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux