Re: Table Partitioning Feature

Hi Robert,

> I am a little fuzzy on what you're proposing here, but I think you're
> saying that you're only going to support range partitioning on
> integers or dates and that you plan to use the text type to store the
> integer or date values. FWIW, those don't seem like very good
> decisions to me. I think you should aim to support range partitioning
> on any combination of a datatype and a less-than operator, similar to
> what pg_statistic does for statistics. pg_statistic uses anyarray to
> store the datums.
>

We don't have any strong reason for not using anyarray datatypes. We
will consider your suggestion.

> I am also somewhat skeptical about the idea of using triggers for
> this. I haven't scrutinized the issue in detail, so I may be all
> wet... but ISTM that the concerns raised elsewhere about the order in
> which triggers can be expected to fire may bite you fairly hard. ISTM
> the right semantics are something like this:
>
> - fire all of the row-level BEFORE triggers on the parent table
> (giving up if any return NULL)
> - determine the correct child table based on the resulting tuple
> - fire all of the row-level BEFORE triggers on the child table (giving
> up if any return NULL)
> - insert the tuple into the child table
> - fire all of the row-level AFTER triggers on the child table... and
> possibly also the parent table... not sure about the order
>

The child tables will just have update triggers to take care of row
movements. Invalid Inserts on child tables will be taken care by the
constraints.
Parent table will have all the triggers (insert/update/del) to
redirect the rows to appropriate child tables.

The order of execution of triggers can create problems. However,
triggers are called in order of there names. So we can use a prefix
starting with "large" string value for partition names to make sure
that they are called last.

> You will also need to fire statement-level triggers on the appropriate
> tables, which is a little tricky. Presumably you want the tables on
> which the AFTER triggers fire to be the same ones as those on which
> the BEFORE triggers fire, but you don't know which child tables you're
> actually going to hit until you actually perform the action. Maybe
> the right thing to do is fire both sets of triggers on the parent
> table and those child tables not excluded by constraint exclusion...?
> But I'm not sure about that.

I am not sure i understood the problem. But our triggers will know
which partitions (child tables) will be subjected to
insert/update/del.

Thanks,
Amit
Persitent Systems

>
> Anyway, getting these types of behavior via triggers may be tricky.
> But then again maybe not: I haven't read the code.
>
>
> ...Robert
>