Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1

ITAGAKI Takahiro wrote:
> Emmanuel Cecchet <manu@frogthinker.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@frogthinker.org
Skype: emmanuel_cecchet