合 GP6和GP7中的分区表视图变化
Tags： GreenPlum新特性GPDB 7系统视图分区表GPDB 6比较
- About the Greenplum 6 and 7 Partitioning Catalogs
- pg_partitions
- Examples Retrieving Similar Information in Greenplum 7
- partitionname / parentpartitionname
- partitionposition / partitionrank
- partitioneveryclause
- partitionstartinclusive / partitionendinclusive
- partitionboundary
- Composing a Similar View in Greenplum 7
- pg_partition_columns
- Composing a Similar View in Greenplum 7
- pg_partition_rule
- pg_partition
- pg_partition_templates
- pg_partition_encoding
- Composing a Similar View in Greenplum 7
- pg_stat_partition_operations
- GP6 SQL总结
- GP7 SQL总结
- 参考
About the Greenplum 6 and 7 Partitioning Catalogs
以下在 Greenplum 6 中可用的与分区相关的目录表、视图和函数在 Greenplum 7 中已被移除：
- pg_partition
- pg_partition_columns
- pg_partition_encoding
- pg_partition_rule
- pg_partition_templates
- pg_partitions
- pg_stat_partition_operations
pg_partition_def()
Greenplum 7 新增了以下目录表和函数：
- gp_partition_template
- pg_partitioned_table
- pg_partition_tree()
- pg_partition_ancestors()
- pg_partition_root()
pg_partitions
The Greenplum 6 pg_partitions view displays all leaf partitions in the current database.
| Column Name | Greenplum 6 Description | Greenplum 7 Equivalent |
|---|---|---|
| schemaname | The name of the schema in which the root partitioned table resides. | Use pg_partition_root() to obtain the root object identifier, and then query pg_namespace. |
| tablename | The name of the root partitioned table. | Use pg_partition_root() to obtain the root object identifier, and then query pg_class. |
| partitionschemaname | The namespace of the leaf partition. | pg_namespace |
| partitiontablename | The table name of the leaf partition (the table name you use to access the partition directly). | pg_class |
| partitionname | The partition name of the leaf partition (the name you use to refer to the partition in an ALTER TABLE command). | N/A |
| parentpartitiontablename | The table name of the parent table of this partition. | Get the parent object identifier via pg_inherits and then query pg_class. |
| parentpartitionname | The partition name of the parent table of this partition. | N/A |
| partitiontype | The type of partition (range or list). | Get the parent object identifier via pg_inherits and then query pg_partitioned_table. |
| partitionlevel | The level of this partition in the partition hierarchy. | Get the level from pg_partition_tree() using the root object identifier. Note that the level differs in Greenplum 6 and 7. In Greenplum 6, the level of the immediate child of a partitioned table is 0. In Greenplum 7, the level of the partitioned table itself is 0, and the level of its immediate child is 1. |
| partitionrank | For range partitions, the rank of the partition compared to other partitions at the same level. | N/A |
| partitionposition | The rule order position of this partition. | N/A |
| partitionlistvalues | For list partitions, the list value(s) associated with this partition. | Get the partition boundary via pg_get_expr() and then filter the text. |
| partitionrangestart | For range partitions, the start value of this partition. | Get the partition boundary via pg_get_expr() and then filter the text. |
| partitionstartinclusive | Whether or not the start value is included in this partition. true if the start value is included. | always inclusive |
| partitionrangeend | For range partitions, the end value of this partition. | Get the partition boundary via pg_get_expr() and then filter the text. |
| partitionendinclusive | Whether or not the end value is included in this partition. true if the end value is included. | always exclusive |
| partitioneveryclause | The EVERY clause (interval) of this partition. | N/A |
| partitionisdefault | Whether or not this is a default partition. true if this is the default, otherwise false. | Get the partition boundary via pg_get_expr() and check if it is DEFAULT. Alternatively, use pg_partitioned_table.partdefid. |
| partitionboundary | The entire partition specification for this partition. | pg_get_expr(), but note that it is returned in modern syntax. |
| parenttablespace | The tablespace of the parent table of this partition. | Get the parent object identifier via pg_inherits and then query pg_tablespace. |
| partitiontablespace | The tablespace of this partition. | pg_tablespace |
Examples Retrieving Similar Information in Greenplum 7
partitionname / parentpartitionname
The partitionname and parentpartitionname columns provide the Greenplum 6 "partition name" in constrast to the actual table name. Because Greenplum 7 no longer stores the partition name in the catalog (previously in pg_partition_rule), it cannot retrieve that information now. Use the table names directly to refer to the partitions. However, if you really need the partition name, you can achieve that via some text massaging:
1 2 3 4 5 6 7 8 9 10 | SELECT c.relname AS table_name, split_part(substr(c.relname, position(inh.inhparent::regclass::text in c.relname) + length(inh.inhparent::regclass::text)), '_', 4) AS partition_name FROM pg_class c LEFT JOIN pg_inherits inh ON inh.inhrelid = c.oid WHERE c.relname LIKE concat(inh.inhparent::regclass::text, '%') AND c.relname LIKE '<your_partition_name>'; |
This query is valid only if the table name is implicitly generated by Greenplum from the partition name and is in the form of <parent_tablename>_prt_<level>_<partition_name>. This query will not work for a partitioned table created with modern syntax (where you always specify the table name when adding a partition) or for a partitioned table created with classic syntax where you explicitly specify the table name in a WITH clause for the partition.
partitionposition / partitionrank
The partitionposition and partitionrank columns in the Greenplum 6 pg_partitions view are based on the "order" number for each partition in the pg_partition_rule catalog, but that catalog is removed in Greenplum 7. However, if you are simply interested in retrieving the sorted order for range partitions, you can order the range value. For example, if the Greenplum 6 query below provides the highest rank partition:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT CASE WHEN partitionstartinclusive THEN partitionrangestart ELSE partitionrangeend END AS old_part_value FROM pg_catalog.pg_partitions p WHERE p.schemaname = '" + cfg.schema + "' AND p.tablename = '" + cfg.table + "' AND p.partitiontype = 'range' AND p.partitionlevel = " + str(cfg.partition_level) + " AND " + chk + " ORDER BY partitionrank LIMIT 1; |
A similar query in Greenplum 7 follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SELECT rank() OVER ( PARTITION BY pc.oid ORDER BY CAST( (regexp_matches(pg_get_expr(c.relpartbound, c.oid), 'FOR VALUES FROM \(([0-9]+)\) TO \(([0-9]+)\)'))[1] AS INTEGER ) ) AS rank, (regexp_matches(pg_get_expr(c.relpartbound, c.oid), 'FOR VALUES FROM \(([0-9]+)\) TO \(([0-9]+)\)'))[1] AS old_part_value FROM pg_class c LEFT JOIN pg_inherits inh ON inh.inhrelid = c.oid LEFT JOIN pg_partitioned_table pt ON inh.inhparent = pt.partrelid LEFT JOIN pg_class pc ON pc.oid = pt.partrelid WHERE c.relispartition = 't' AND pc.relname LIKE ('<partition_root_schema>.<partition_root_name>') ORDER BY rank LIMIT 1; |
Assuming that the partitioning method for the partitioned table is RANGE, the above query calculates the ranks for each child partition, and prints the range START value (which is always inclusive) with the highest rank.
You cannot retrieve the order number for list partitions with this query, however, because the order is based on the time the partition is added, and Greenplum 7 does not store that information. A query for list partitions must not be dependent on order information.
partitioneveryclause
Because Greenplum 7 does not store the use of the EVERY clause in the catalog, it can not discern if a partition was created with the clause. The main use of this column is to reconstruct a partition definition clause using the classic-syntax for display or pg_dump purposes. No mapping is provided at this time.
partitionstartinclusive / partitionendinclusive
Greenplum 7 supports the classic syntax INCLUSIVE and EXCLUSIVE clauses through the adjustment of START and END values. It dos not record whether a range START or END is inclusive or not, because the START is always inclusive and the END is always exclusive. So the partitionstartinclusive and partitionendinclusive columns are simply redundant.
partitionboundary
You can retrieve the partition boundary definition in Greenplum 7 via the pg_get_expr() function:
1 2 3 4 5 6 | SELECT pg_get_expr(relpartbound, oid) FROM pg_class WHERE relispartition = 't'; |
Composing a Similar View in Greenplum 7
An approximate pg_partitions view follows. This view, given all limitations addressed above, prints the information that is possible to retrieve in Greenplum 7.
Caution
This example is for illustrative purposes only, not for practical use.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | SELECT (SELECT relnamespace::regnamespace FROM pg_class WHERE oid = pg_partition_root(c.oid)) AS schemaname, (SELECT pg_partition_root(c.oid)::regclass) AS tablename, n.nspname AS partitionschemaname, c.relname AS partitiontablename, -- assuming the table name is implicitly generated like <parent>_prt_<id>_<partition> split_part( substr(c.relname, position(pc.relname in c.relname) + length(pc.relname)), '_', 4 ) AS partitionname, pc.relname AS parentpartitiontablename, -- same assumption as above split_part( substr(pc.relname, position(ppc.relname in pc.relname) + length(ppc.relname)), '_', 4 ) AS parentpartitionname, CASE WHEN pt.partstrat = 'r' THEN 'range' ELSE 'list' END AS partitiontype, (SELECT level FROM pg_partition_tree(pg_partition_root(c.oid)) WHERE relid = c.oid) AS partitionlevel, -- can be calculated like a previous example NULL AS partitionrank, -- cannot be trusted because no real order for list partitions NULL AS partitionposition, (regexp_matches(pg_get_expr(c.relpartbound, c.oid), 'FOR VALUES IN \((.*?)\)'))[1] AS partitionlistvalues, -- assuming range values are normal integers instead of expressions which Greenplum 7 supports (regexp_matches(pg_get_expr(c.relpartbound, c.oid), 'FOR VALUES FROM \(([0-9]+)\) TO \(([0-9]+)\)'))[1] AS partitionrangestart, 't' AS partitionstartinclusive, (regexp_matches(pg_get_expr(c.relpartbound, c.oid), 'FOR VALUES FROM \(([0-9]+)\) TO \(([0-9]+)\)'))[2] AS partitionrangeend, 'f' AS partitionendinclusive, -- information cannot be retrieved NULL AS partitioneveryclause, CASE WHEN pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT' THEN 't' ELSE 'f' END AS partitionisdefault, (regexp_matches(pg_get_expr(c.relpartbound, c.oid), '.+'))[1] AS partitionboundary, CASE WHEN pc.reltablespace = 0 THEN 'pg_default' ELSE (SELECT spcname FROM pg_tablespace WHERE oid = pc.reltablespace) END AS parenttablespace, CASE WHEN c.reltablespace = 0 THEN 'pg_default' ELSE (SELECT spcname FROM pg_tablespace WHERE oid = c.reltablespace) END AS partitiontablespace FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_inherits inh ON inh.inhrelid = c.oid LEFT JOIN pg_partitioned_table pt ON inh.inhparent = pt.partrelid LEFT JOIN pg_class pc ON pc.oid = pt.partrelid LEFT JOIN pg_inherits pinh ON pinh.inhrelid = pc.oid LEFT JOIN pg_partitioned_table ppt ON pinh.inhparent = ppt.partrelid LEFT JOIN pg_class ppc ON ppc.oid = ppt.partrelid WHERE c.relispartition = 't'; |
pg_partition_columns
The Greenplum 6 pg_partition_columns view displays the partition key columns of a partitioned table.
