Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1

> ok. what about let CREATE TABLE WITH PARTITIONING to create an entry
> in a catalog indicating the key of the partition and install the
> triggers and let the trigger decide if it has the partition to insert
> the new row (making UPDATE working almost as DELETE+INSERT if it needs
> to change of partitions) or create the new partition maybe with an
> apropiate CREATE PARTITION...
>
> that way i don't need to create triggers nor inherit tables
> manually... and because of that maybe we can make possible to add
> <expr> as partition key...
>
>
> PS: i'm against using CREATE TABLE because we are inventing new syntax
> but it seems like using ALTER TABLE is a *lot* of work altough ISTM
> more usefull

I think that's one of the useful things that could be done in this
area (not the only one, certainly), but I don't think we've defined
the semantics well enough to start talking about exactly which
commands to use. As to CREATE TABLE and ALTER TABLE, I suspect you'll
need both. We have to come to some consensus on whether predefining a
partition key is necessary, optional, or not supported. And we need
to define ways both to set things up and to change them later.

If there is no predefined partition key, there's probably nothing
terribly special that needs to be done to prepare a table for
partitioning. You could decide that all the data will live in the
parent table except for the partitions that are explicitly created.
When the user creates a partition, you create the new child table, set
it to inherit from the parent, add the necessary constraint,
create/update an automatically generated rule/trigger on the parent
that redirects DML to the appropriate partition, and move any EXISTING
tuples that belong in that partition into it. You'd also need
operations to merge a partition back into the parent table (moving the
data back), drop a partition (lose the data), and change the
definition of a partition (move data around).

A significant problem with this design is that you don't know that the
partition constraints are mutually exclusive. What do you do with
data that matches multiple partition constraints? You'll have to
devise some rule, like maybe picking the first partition
alphabetically, which will complicate the rearrangement of data when
partitions are added or removed.

If there IS a predefined partition key, then you'll need a way to tell
the parent table what it is (and a way to remove it later if you
change your mind). Then it should be possible to validate that child
partitions are defined only in terms of that key and that they are
mutually exclusive. You'll still need basically all the same
operations: create partition, modify partition, merge partition back
into parent, drop partition.

...Robert