Re: Partitioning option for COPY

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