Hi, > From: Robert Haas [mailto:robertmh...@gmail.com] > Sent: Saturday, November 08, 2014 5:41 AM > > I'd be in favor of that.
Thanks! > I am not sure whether the code is close > enough to what we need to be really useful, but that's for you to > decide. Hmm, I'm not entirely convinced about the patch as it stands either but, I will try to restate below what the patch in its current state does anyway (just to refresh): The patch provides syntax to: * Specify partitioning key, optional partition definitions within CREATE TABLE, * A few ALTER TABLE commands that let you define a partitioning key (partitioning a table after the fact), attach/detach an existing table as a partition of a partitioned table, * CREATE PARTITION to create a new partition on a partitioned table. Above commands are merely transformed into ALTER TABLE subcommands that arrange partitioned table and partitions into inheritance hierarchy, but with extra information, that is, allowed values for the partition in a new anyarray column called 'pg_inherits.values'. A special case of ATExecAddInherit() namely ATExecAttachPartitionI(), as part of its processing, also adds partition constraints in the form of appropriate CHECK constraints. So, a few of the manual steps are automated and additional (IMHO non-opaque) metadata (namely partition boundaries/list values) is added. Additionally, defining a partitioning key (PARTITION BY) creates a pg_partition entry that specifies for a partitioned table the following - partition kind (range/list), an opclass for the key value comparison and a key 'expression' (say, "colname % 10"). A few key things I can think of as needing improvement would be (perhaps just reiterating a review of the patch): * partition pruning would still depend on constraint exclusion using the CHECK constraints (same old) * there is no tuple-routing at all (same can be said of partition pruning above) * partition pruning or tuple-routing would require a scan over pg_inherits (perhaps inefficient) * partitioning key is an expression which might not be a good idea in early stages of the implementation (might be better off with just the attnum of the column to partition on?) * there is no DROP PARTITION (in fact, it is suggested not to go CREATE/DROP PARTITION route at all) -> ALTER TABLE ... ADD/DROP PARTITION? Some other important ones: * dependency handling related oversights * constraint propagation related oversights And then some of the oddities of behaviour that I am seeing while trying out things that the patch does. Please feel free to suggest those that I am not seeing. I am sure these improvements need more than just tablecmds.c hacking which is what the current patch mostly does. The first two points could use separate follow-on patches as I feel they need extensive changes unless I am missing something. I will try to post possible solutions to these issues provided metadata in current form is OK to proceed. > In my view, the main problem we should be trying to solve > here is "avoid relying on constraint exclusion". In other words, the > syntax for adding a partition should put some metadata into the system > catalogs that lets us do partitioning pruning very very quickly, > without theorem-proving. For example, for list or range partitioning, > a list of partition bounds would be just right: you could > binary-search it. The same metadata should also be suitable for > routing inserts to the proper partition, and handling partition motion > when a tuple is updated. > > Now there's other stuff we might very well want to do, but I think > making partition pruning and tuple routing fast would be a pretty big > win by itself. > Those are definitely the goals worth striving for. Thanks for your time. Regards, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers