ITAGAKI Takahiro wrote:
> Emmanuel Cecchet <manu(at)frogthinker(dot)org> wrote
>> In the meantime, I have made some more tests with the trigger in C (see
>> attached patch).
>>
>
> Hmm... The inserting partition is passed by trigger arguments.
>
Actually this is just a fallback option. The preferred option is to name
the trigger after the child table name. This way the trigger retrieve
the table name directly from the trigger name and no argument has to be
passed to the trigger.
> Users must replace triggers when the target is changed (ex. every month).
>
I am not sure what you mean. There is one trigger per child table but
the trigger is always the same, it is just the name that is given to it
that changes.
> Is it possible to expand all of child paritions from pg_inherits and
> determine a suitable parition by checking their constraints?
>
Ideally it would be better to do this way. I have not found yet how to
automatically get all the child partitions of a parent table from the
trigger. This would simplify things by having a single trigger.
> We can also use it when there are multiple inserting paritions,
> something like hash or list paritioning. Fixed target is only applicable
> to time-based range paritioning.
>
I think there is a misunderstanding on how the trigger works. You have 1
trigger per child table and they are all chained on the parent table.
When a tuple is inserted on the parent table, the first trigger is
fired, if the constraints of the 1st child table are satisfied, the
tuple is moved in the 1st child table and that's it. If it is a miss,
the tuple is passed to the next trigger that checks the constraints of
the 2nd table. And so on.
This will work with any type of partitioning (hash or even UDF) as long
as the constraints on the child table reflect the partitioning.
> BTW, there is another issue in trigger approach. If INSERT commands
> are interrupted by triggers, server says "INSERT 0 row" though
> rows are inserted into child tables. Since using C, we could
> use some back doors to modify a variable counting affected rows.
> We could use partitioned tables more transparently if we have it.
>
Even if you don't abort the query, the query reports 0 row if it has
been moved to another table (you can COPY 100k lines and the server will
return 0 if they were all successfully moved to child tables).
Technically this is correct since 0 rows were inserted in the parent
table. Right now any number >0 is the number of rows that did not
satisfy any child table constraint and were inserted in the master table
(useful if you don't want the copy command to fail).
Regards,
Emmanuel
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu(at)frogthinker(dot)org
Skype: emmanuel_cecchet