Re: Partitioning option for COPY - Mailing list pgsql-hackers

On Wed, 2009-11-25 at 08:39 -0500, Emmanuel Cecchet wrote:
> 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?

After triggers can't change tuple, thus cant change routing.

> 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? 

What statement triggers do you mean ?

I don't think we have ON COPY triggers ? 

> 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.

I think we should keep the possibility to populate partitions offline
and then plug then into table as partitions (current INHERIT) and also
to extract partition into separate table (NO INHERIT).



-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability   Services, Consulting and Training