Re: Partitioning option for COPY

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 ?

> 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

5. execute after triggers of parent table

order of 4. and 5. is selected arbitrarily, others are determined by
flow.

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

> it might be safer to
> not allow triggers on child tables altogether and use the parent table
> as the single point of entry to access the partition (and define
> triggers). With the current proposed implementation, would it be
> possible to define a view using child tables?

the child tables are there, and they _are_ defined, either implicitly
(using constraints, which "constraint exclusion" resolves to a set of
child tables) or explicitly, using child table names directly.

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