Re: Partition insert trigger using C language

________________________________
> From: matioli(dot)matheus(at)gmail(dot)com
> Date: Thu, 10 Jan 2013 16:45:43 -0200
> Subject: Partition insert trigger using C language
> To: pgsql-performance(at)postgresql(dot)org
> CC: charlesrg(at)outlook(dot)com
>
> 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<http://www.dextra.com.br/postgres/>

Interesting that you got an improvement. In my case I  get
almost no improvement at all:

PL/SQL – Dynamic Trigger


 4:15:54




PL/SQL - CASE / WHEN Statements


 4:12:29




PL/SQL
- IF Statements


 4:12:39




C
Trigger


 4:10:49

Here is my code, I’m using heap insert and updating the indexes.
With a similar approach of yours.

The trigger is aware of

http://www.charlesrg.com/~charles/pgsql/partition2.c