This is really great !
Think about altering the partitioning (this is quite complex) : imagine a
table split in several partitions "archive" and "current" where a row is
moved from current to archive when it will not be updated anymore.
Sometimes you can partition on a simple numeric value, or even a boolean
value in this case. Other times you'd have to partition on a date,
(current month, current year, archive...). So, how to move the partition
between the two tables so that the oldest rows in the current month table
are moved to the current year table at the end of each month ?
Some ideas :
hidden field (like oid was) to indicate in which partition the tuple is ?
On Sat, 19 Mar 2005 21:02:38 +0100, Josh Berkus <josh@agliodbs.com> wrote:
> Folks,
>
> I may (or may not) soon have funding for implementing full table
> partitioning
> in PostgreSQL. I thought it would be a good idea to discuss with people
> here
> who are already using pseudo-partitioning what things need to be added to
> Postgresql in order to make full paritioning a reality; that is, what do
> other databases do that we don't?
>
> Implementations are seperated into phases I and II, II being
> harder-and-optional-stuff that may get done later, I being essential
> features.
>
> Ph. I
> -- CREATE TABLE ... WITH PARTITION ON {expression}
> ---- should automatically create expression index on {expression}
> -- INSERT INTO should automatically create new partitions where necessary
> ---- new tables should automatically inherit all constraints,
> indexes,
> keys of "parent" table
> -- UPDATE should automatically move rows between partitions where
> applicable
> -- Query Planner/Executor should be improved to not always materialize
> paritioned tables used in subqueries and joins.
>
> Ph. II
> -- Foreign Keys to/from partitioned tables should become possible
> -- Query Planner/Executor should be improved to only join partitions
> which are
> compliant with the query's WHERE or JOIN clauses where reasonable
> -- DELETE FROM should automatically drop empty partitions
> -- setting of WITH PARTITION ON {expression} TABLESPACE should
> automatically
> create a new tablespace for each new partition and its indexes.
> -- It should be possible to create new, empty partitions via a CREATE
> TABLE
> PARTITION OF {table} ON {value} expression.
>
> All syntax above is, of course, highly debatable.
>