> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
