Re: Partitioning option for COPY

Hannu Krosing wrote:
> On Tue, 2009-11-24 at 10:08 -0500, Emmanuel Cecchet wrote:
>
>> Itagaki Takahiro wrote:
>>
>>> I just edited a wiki page for this discussion.
>>> I hope it can be a help.
>>> http://wiki.postgresql.org/wiki/Table_partitioning
>>>
>>>
>> I guess the problem of handling user triggers is still open.
>> If we allow triggers on partitions, badly written logic could lead to
>> infinite loops in routing. In the case of COPY, an after statement
>> trigger could change all the routing decisions taken for each row.
>>
>
> A simple update to the row can cause it to move between partitions, no ?
>
Yes.
>> I am not sure what the semantic should be if you have triggers defined on the
>> parent and child tables. Which triggers do you fire if the insert is on
>> the parent table but the tuple ends up in a child table?
>>
>
> I'd propose that triggers on both parent table and selected child are
> executed.
>
> 1. first you execute before triggers on parent table, which may
> change which partition the row belongs to
>
> 2. then you execute before triggers on selected child table
>
> 2.1 if this changes the child table selection repeat from 2.
>
> 3. save the tuple in child table
>
> 4. execute after triggers of the final selected child table
>
What if that trigger changes again the child table selection?
> 5. execute after triggers of parent table
>
Same here, what if the trigger changes the child table selection. Do we
re-execute triggers on the new child table?
Also it is debatable whether we should execute an after trigger on a
table where nothing was really inserted.
> order of 4. and 5. is selected arbitrarily, others are determined by
> flow.
>
Also the description omits the execution of before and after statement
triggers. While those can apply to the parent table (but the same
question about what happens if the after statement modifies routing
decision still applies), what does it mean in the case of COPY to have
statement triggers on the child tables? You cannot know in advance where
the tuples are going to go and fire the before statement triggers. If
you had to fire after statement triggers, in which order would you fire
them?
>> If the new implementation hides the child tables,
>>
>
> If you hide child tables, you suddenly need a lot of new syntax to
> "unhide" them, so that partitions can be manipulated. Currently it is
> easy to do it with INHERIT / NO INHERIT.
>
Agreed, but I think that we will discover some restrictions that will
apply to child tables.

Emmanuel

--
Emmanuel Cecchet
Aster Data
Web: http://www.asterdata.com