Re: Partitioning option for COPY

On Sun, Nov 22, 2009 at 12:35 PM, Jan Urbański <wulczer(at)wulczer(dot)org> wrote:
> I was thinking more about SGML docs. They could mention that BEFORE
> triggers are fired both for the parent table and for the child table,
> while AFTER triggers will only be called on the target table. I'd add a
> sentence or two explaining what happens if you have a three-level
> inheritance hierarchy (that the tuple will be inserted in the bottommost
> table of the hierarchy).

I have a hard time believing this is OK, even with documentation.
While it might be OK in some (many?) particular use cases to fire
triggers in this way, making COPY with the partitioning option fire
different triggers at different times than COPY without the
partitioning option - and in fact every other method of getting data
into a table, all of which are consistent with each other and with
COPY without the partitioning option - seems like a bad idea to me. I
don't think the behavior described above is OK, and I also don't think
that the changes in the timing of AFTER-trigger firing are OK. I
understand that without that change there was a relcache leak, but I
think that just means that bug needs to be found and fixed.

I would also like to see some more discussion of the basic mechanism
of this patch. Essentially, what it's trying to do is traverse the
inheritance hierarchy looking for a table whose constraints match the
current tuple, and then inserting the tuple there. First - is this a
good idea? It's embeds some assumptions about how inheritance
hierarchies are set up which don't seem totally unreasonable, but even
so I'm not sure we want to go that route. Second - in lieu of
accepting this approach, do we want to wait for Itagaki Takahiro's
partitioning syntax patch to go in (as I am hoping that it will) and
then do something more structured based on the notation introduced
there?

One thing that biases me toward thinking that maybe we should wait is
the fact that this patch relies on an MRU list to determine into which
child table a particular tuple should be inserted. If the constraints
on the child tables are not mutually exclusive, the tuple routing
won't be deterministic, which seems undesirable to me. On the other
hand, if we got rid of the MRU cache and made the order deterministic
(say, alphabetical by partition name) then I'm guessing this would be
quite slow for large numbers of partitions when most of the tuples
need to go into the later partitions.

...Robert