agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v4] Add assorted partition reporting functions 9+ messages / 2 participants [nested] [flat]
* [PATCH v3] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 49 ++++++++++ src/backend/catalog/partition.c | 130 ++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 +++++ src/include/catalog/partition.h | 1 + src/include/catalog/pg_proc.dat | 18 ++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/create_table.out | 60 +++++++++++++ src/test/regress/expected/partition_info.out | 62 +++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 25 ++++++ 11 files changed, 366 insertions(+), 5 deletions(-) create mode 100644 src/test/regress/expected/partition_info.out create mode 100644 src/test/regress/sql/partition_info.sql diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5dce8ef178..884b9c36a5 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19995,6 +19995,55 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); The function returns the number of new collation objects it created. </para> + <table id="functions-info-partition"> + <title>Partitioning Information Functions</title> + <tgroup cols="3"> + <thead> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row> + </thead> + + <tbody> + <row> + <entry><literal><function>pg_partition_parent(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get parent if table is a partition, <literal>NULL</literal> otherwise</entry> + </row> + <row> + <entry><literal><function>pg_partition_root_parent(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get top-most parent of a partition within partition tree</entry> + </row> + <row> + <entry><literal><function>pg_partition_tree_tables(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>get all tables in partition tree with given table as top-most parent</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + If the table passed to <function>pg_partition_root_parent</function> is not + a partition, the same table is returned as the result. Result of + <function>pg_partition_tree_tables</function> also contains the table + that's passed to it as the first row. + </para> + + <para> + For example, to check the total size of the data contained in + <structname>measurement</structname> table described in + <xref linkend="ddl-partitioning-declarative-example"/>, use the following + query: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_tree_tables('measurement') p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 558022647c..9af1c25bba 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -23,13 +23,16 @@ #include "catalog/partition.h" #include "catalog/pg_inherits.h" #include "catalog/pg_partitioned_table.h" +#include "funcapi.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "partitioning/partbounds.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -44,10 +47,6 @@ static void get_partition_ancestors_worker(Relation inhRel, Oid relid, * Obtain direct parent of given relation * * Returns inheritance parent of a partition by scanning pg_inherits - * - * Note: Because this function assumes that the relation whose OID is passed - * as an argument will have precisely one parent, it should only be called - * when it is known that the relation is a partition. */ Oid get_partition_parent(Oid relid) @@ -55,6 +54,9 @@ get_partition_parent(Oid relid) Relation catalogRelation; Oid result; + if (!get_rel_relispartition(relid)) + return InvalidOid; + catalogRelation = heap_open(InheritsRelationId, AccessShareLock); result = get_partition_parent_worker(catalogRelation, relid); @@ -71,6 +73,10 @@ get_partition_parent(Oid relid) * get_partition_parent_worker * Scan the pg_inherits relation to return the OID of the parent of the * given relation + * + * Note: Because this function assumes that the relation whose OID is passed + * as an argument will have precisely one parent, it should only be called + * when it is known that the relation is a partition. */ static Oid get_partition_parent_worker(Relation inhRel, Oid relid) @@ -148,6 +154,28 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors) } /* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + List *ancestors; + Oid result; + + if (!get_rel_relispartition(relid)) + return InvalidOid; + + ancestors = get_partition_ancestors(relid); + result = llast_oid(ancestors); + Assert(!get_rel_relispartition(result)); + list_free(ancestors); + + return result; +} + +/* * map_partition_varattnos - maps varattno of any Vars in expr from the * attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which * may be either a leaf partition or a partitioned table, but both of which @@ -357,3 +385,97 @@ get_proposed_default_constraint(List *new_part_constraints) return make_ands_implicit(defPartConstraint); } + +/* + * SQL wrapper around get_partition_root_parent(). + */ +Datum +pg_partition_root_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + + /* + * If the relation is actually a partition, 'rootoid' has been set to the + * OID of the root table in the partition tree. + */ + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + /* + * Otherwise, the table's not a partition. That is, it's either the root + * table in a partition tree or a standalone table that's not part of any + * partition tree. In any case, return the table OID itself as the + * result. + */ + else + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent(). + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + + /* + * If the relation is actually a partition, 'parentoid' has been set to + * the OID of the immediate parent. + */ + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + else + /* Not a partition, return NULL. */ + PG_RETURN_NULL(); +} + +/* + * Returns OIDs of tables in a partition tree. + */ +Datum +pg_partition_tree_tables(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + partoids = find_all_inheritors(reloid, NoLock, NULL); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..19262c6c4d 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h index 1f49e5d3a9..a2b11f40bc 100644 --- a/src/include/catalog/partition.h +++ b/src/include/catalog/partition.h @@ -32,6 +32,7 @@ typedef struct PartitionDescData extern Oid get_partition_parent(Oid relid); extern List *get_partition_ancestors(Oid relid); +extern Oid get_partition_root_parent(Oid relid); extern List *map_partition_varattnos(List *expr, int fromrel_varno, Relation to_rel, Relation from_rel, bool *found_whole_row); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 40d54ed030..b4725b8634 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,22 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# function to get the root partition parent +{ oid => '3423', descr => 'oid of the partition root parent', + proname => 'pg_partition_root_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' }, + +# function to get the partition parent +{ oid => '3424', descr => 'oid of the partition immediate parent', + proname => 'pg_partition_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_parent' }, + +# function to get OIDs of all tables in a given partition tree +{ oid => '3425', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_tree_tables', prorettype => '2205', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', prosrc => 'pg_partition_tree_tables' } + ] diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..d396d17ff1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 672719e5d5..f702518d4e 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -909,3 +909,63 @@ ERROR: cannot create a temporary relation as partition of permanent relation "p create temp table temp_part partition of temp_parted default; -- ok drop table perm_parted cascade; drop table temp_parted cascade; +-- tests to show partition tree inspection functions +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (maxvalue) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +insert into ptif_test select i, 1 from generate_series(-5, 5) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent +from pg_partition_tree_tables('ptif_test') p; + relname | parent | root_parent +-------------+------------+------------- + ptif_test | | ptif_test + ptif_test0 | ptif_test | ptif_test + ptif_test1 | ptif_test | ptif_test + ptif_test01 | ptif_test0 | ptif_test + ptif_test11 | ptif_test1 | ptif_test +(5 rows) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test') p; + relname | parent | root_parent | size +-------------+------------+-------------+------ + ptif_test | | ptif_test | 0 + ptif_test0 | ptif_test | ptif_test | 0 + ptif_test1 | ptif_test | ptif_test | 0 + ptif_test01 | ptif_test0 | ptif_test | 8192 + ptif_test11 | ptif_test1 | ptif_test | 8192 +(5 rows) + +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test') p; + total_size +------------ + 16384 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out new file mode 100644 index 0000000000..dfb42c2f1b --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,62 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (maxvalue) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +insert into ptif_test select i, 1 from generate_series(-5, 5) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent +from pg_partition_tree_tables('ptif_test') p; + relname | parent | root_parent +-------------+------------+------------- + ptif_test | | ptif_test + ptif_test0 | ptif_test | ptif_test + ptif_test1 | ptif_test | ptif_test + ptif_test01 | ptif_test0 | ptif_test + ptif_test11 | ptif_test1 | ptif_test +(5 rows) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test') p; + relname | parent | root_parent | size +-------------+------------+-------------+------ + ptif_test | | ptif_test | 0 + ptif_test0 | ptif_test | ptif_test | 0 + ptif_test1 | ptif_test | ptif_test | 0 + ptif_test01 | ptif_test0 | ptif_test | 8192 + ptif_test11 | ptif_test1 | ptif_test | 8192 +(5 rows) + +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test') p; + total_size +------------ + 16384 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 16f979c8d9..6cb820bbc4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate +test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 42632be675..7e374c2daa 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -188,6 +188,7 @@ test: reloptions test: hash_part test: indexing test: partition_aggregate +test: partition_info test: event_trigger test: fast_default test: stats diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql new file mode 100644 index 0000000000..35f5986ee0 --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,25 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (maxvalue) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +insert into ptif_test select i, 1 from generate_series(-5, 5) i; + +select pg_partition_parent('ptif_test0') as parent; +select pg_partition_parent('ptif_test01') as parent; +select pg_partition_root_parent('ptif_test01') as root_parent; +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent +from pg_partition_tree_tables('ptif_test') p; +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test') p; +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test') p; +drop table ptif_test; -- 2.11.0 --------------811B5F71712F672BE8507357-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- src/backend/catalog/partition.c | 117 +++++++++++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 +++++++ src/include/catalog/partition.h | 1 + src/include/catalog/pg_proc.h | 12 ++++ src/include/utils/lsyscache.h | 1 + 5 files changed, 152 insertions(+), 1 deletion(-) diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 8adc4ee977..ac92bbfa71 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -32,6 +32,7 @@ #include "catalog/pg_type.h" #include "commands/tablecmds.h" #include "executor/executor.h" +#include "funcapi.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -181,6 +182,7 @@ static int partition_bound_bsearch(PartitionKey key, static int get_partition_bound_num_indexes(PartitionBoundInfo b); static int get_greatest_modulus(PartitionBoundInfo b); static uint64 compute_hash_value(PartitionKey key, Datum *values, bool *isnull); +static Oid get_partition_parent_internal(Oid relid, bool recurse_to_root); /* SQL-callable function for use in hash partition CHECK constraints */ PG_FUNCTION_INFO_V1(satisfies_hash_partition); @@ -1362,7 +1364,7 @@ check_default_allows_bound(Relation parent, Relation default_rel, /* * get_partition_parent * - * Returns inheritance parent of a partition by scanning pg_inherits + * Returns inheritance parent of a partition. * * Note: Because this function assumes that the relation whose OID is passed * as an argument will have precisely one parent, it should only be called @@ -1371,6 +1373,37 @@ check_default_allows_bound(Relation parent, Relation default_rel, Oid get_partition_parent(Oid relid) { + if (!get_rel_relispartition(relid)) + return InvalidOid; + + return get_partition_parent_internal(relid, false); +} + +/* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + if (!get_rel_relispartition(relid)) + return InvalidOid; + + return get_partition_parent_internal(relid, true); +} + +/* + * get_partition_parent_internal + * + * Returns inheritance parent of a partition by scanning pg_inherits. + * If recurse_to_root, it will check if the parent itself is a partition and + * if so, it will recurse to find its parent and so on until root parent is + * found. + */ +static Oid +get_partition_parent_internal(Oid relid, bool recurse_to_root) +{ Form_pg_inherits form; Relation catalogRelation; SysScanDesc scan; @@ -1402,6 +1435,9 @@ get_partition_parent(Oid relid) systable_endscan(scan); heap_close(catalogRelation, AccessShareLock); + if (recurse_to_root && get_rel_relispartition(result)) + result = get_partition_parent_internal(result, recurse_to_root); + return result; } @@ -3396,3 +3432,82 @@ satisfies_hash_partition(PG_FUNCTION_ARGS) PG_RETURN_BOOL(rowHash % modulus == remainder); } + +/* + * SQL wrapper around get_partition_root_parent() in + * src/backend/catalog/partition.c. + */ +Datum +pg_partition_root(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + else + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent() in + * src/backend/catalog/partition.c. + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + else + PG_RETURN_NULL(); +} + +/* + * Returns Oids of tables in a publication. + */ +Datum +pg_partition_tree_tables(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + partoids = find_all_inheritors(reloid, NoLock, NULL); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index e8aa179347..92353a6004 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1836,6 +1836,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h index 2faf0ca26e..287642b01b 100644 --- a/src/include/catalog/partition.h +++ b/src/include/catalog/partition.h @@ -52,6 +52,7 @@ extern PartitionBoundInfo partition_bounds_copy(PartitionBoundInfo src, extern void check_new_partition_bound(char *relname, Relation parent, PartitionBoundSpec *spec); extern Oid get_partition_parent(Oid relid); +extern Oid get_partition_root_parent(Oid relid); extern List *get_qual_from_partbound(Relation rel, Relation parent, PartitionBoundSpec *spec); extern List *map_partition_varattnos(List *expr, int fromrel_varno, diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index f01648c961..64942b310c 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -5533,6 +5533,18 @@ DESCR("list of files in the WAL directory"); DATA(insert OID = 5028 ( satisfies_hash_partition PGNSP PGUID 12 1 0 2276 0 f f f f f f i s 4 0 16 "26 23 23 2276" _null_ "{i,i,i,v}" _null_ _null_ _null_ satisfies_hash_partition _null_ _null_ _null_ )); DESCR("hash partition CHECK constraint"); +/* function to get the root partition parent */ +DATA(insert OID = 3281 ( pg_partition_root PGNSP PGUID 12 10 0 0 0 f f f f t f s s 1 0 2205 "2205" _null_ _null_ _null_ _null_ _null_ pg_partition_root _null_ _null_ _null_ )); +DESCR("oid of the partition root parent"); + +/* function to get the partition parent */ +DATA(insert OID = 3556 ( pg_partition_parent PGNSP PGUID 12 10 0 0 0 f f f f t f s s 1 0 2205 "2205" _null_ _null_ _null_ _null_ _null_ pg_partition_parent _null_ _null_ _null_ )); +DESCR("oid of the partition immediate parent"); + +/* function to get OIDs of all tables in a given partition tree */ +DATA(insert OID = 3696 ( pg_partition_tree_tables PGNSP PGUID 12 1 1000 0 0 f f f f t t s s 1 0 2205 "2205" "{2205,2205}" "{i,o}" "{relid,relid}" _null_ _null_ pg_partition_tree_tables _null_ _null_ _null_ )); +DESCR("get OIDs of tables in a partition tree"); + /* * Symbolic values for provolatile column: these indicate whether the result * of a function is dependent *only* on the values of its explicit arguments, diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 9731e6f7ae..1000d9fd13 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -127,6 +127,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); -- 2.11.0 --------------FB214BDFA9CD15B454D4B087-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v6] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 86 ++++++++++ src/backend/catalog/partition.c | 241 ++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 +++ src/include/catalog/pg_proc.dat | 48 ++++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/partition_info.out | 204 +++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 91 ++++++++++ 9 files changed, 691 insertions(+), 5 deletions(-) create mode 100644 src/test/regress/expected/partition_info.out create mode 100644 src/test/regress/sql/partition_info.sql diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index edc9be92a6..e1b7ace898 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19995,6 +19995,92 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); The function returns the number of new collation objects it created. </para> + <table id="functions-info-partition"> + <title>Partitioning Information Functions</title> + <tgroup cols="3"> + <thead> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row> + </thead> + + <tbody> + <row> + <entry><literal><function>pg_partition_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get parent if table is a partition, <literal>NULL</literal> otherwise</entry> + </row> + <row> + <entry><literal><function>pg_partition_root_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get topmost parent of a partition within partition tree</entry> + </row> + <row> + <entry><literal><function>pg_partition_level(<type>regclass</type>, <type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get level of a partition within partition tree with respect to given parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_level(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get level of a partition within partition tree with respect to topmost root parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>, <type>bool</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry> + get partitions of a table; only immediate partitions are returned, + unless all tables in the partition tree, including itself and + partitions of lower levels, are requested by passing + <literal>true</literal> for second argument + </entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>Shorthand for <literal>pg_partition_children(..., false)</literal></entry> + </row> + <row> + <entry><literal><function>pg_partition_leaf_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>get all leaf partitions of a given table</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + If the table passed to <function>pg_partition_root_parent</function> is not + a partition, the same table is returned as the result. + </para> + + <para> + For example, to check the total size of the data contained in + <structname>measurement</structname> table described in + <xref linkend="ddl-partitioning-declarative-example"/>, use the following + query: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_children('measurement', true) p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + <para> + One could have used <function>pg_partition_leaf_children</function> in + this case and got the same result as shown below: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_leaf_children('measurement') p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 558022647c..9d11d1ea16 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -23,13 +23,16 @@ #include "catalog/partition.h" #include "catalog/pg_inherits.h" #include "catalog/pg_partitioned_table.h" +#include "funcapi.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "partitioning/partbounds.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -38,16 +41,14 @@ static Oid get_partition_parent_worker(Relation inhRel, Oid relid); static void get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors); +static Oid get_partition_root_parent(Oid relid); +static List *get_partition_tree_leaf_tables(Oid relid); /* * get_partition_parent * Obtain direct parent of given relation * * Returns inheritance parent of a partition by scanning pg_inherits - * - * Note: Because this function assumes that the relation whose OID is passed - * as an argument will have precisely one parent, it should only be called - * when it is known that the relation is a partition. */ Oid get_partition_parent(Oid relid) @@ -55,6 +56,9 @@ get_partition_parent(Oid relid) Relation catalogRelation; Oid result; + if (!get_rel_relispartition(relid)) + return InvalidOid; + catalogRelation = heap_open(InheritsRelationId, AccessShareLock); result = get_partition_parent_worker(catalogRelation, relid); @@ -71,6 +75,10 @@ get_partition_parent(Oid relid) * get_partition_parent_worker * Scan the pg_inherits relation to return the OID of the parent of the * given relation + * + * Note: Because this function assumes that the relation whose OID is passed + * as an argument will have precisely one parent, it should only be called + * when it is known that the relation is a partition. */ static Oid get_partition_parent_worker(Relation inhRel, Oid relid) @@ -148,6 +156,28 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors) } /* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + List *ancestors; + Oid result; + + if (!get_rel_relispartition(relid)) + return InvalidOid; + + ancestors = get_partition_ancestors(relid); + result = llast_oid(ancestors); + Assert(!get_rel_relispartition(result)); + list_free(ancestors); + + return result; +} + +/* * map_partition_varattnos - maps varattno of any Vars in expr from the * attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which * may be either a leaf partition or a partitioned table, but both of which @@ -357,3 +387,206 @@ get_proposed_default_constraint(List *new_part_constraints) return make_ands_implicit(defPartConstraint); } + +/* + * SQL wrapper around get_partition_root_parent(). + */ +Datum +pg_partition_root_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + + /* + * If the relation is actually a partition, 'rootoid' has been set to the + * OID of the root table in the partition tree. + */ + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + + /* + * Otherwise, the table's not a partition. That is, it's either the root + * table in a partition tree or a standalone table that's not part of any + * partition tree. In any case, return the table OID itself as the + * result. + */ + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent(). + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + + /* + * If the relation is actually a partition, 'parentoid' has been set to + * the OID of the immediate parent. + */ + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + + /* Not a partition, return NULL. */ + PG_RETURN_NULL(); +} + +/* + * Returns an integer representing the level a given partition is at in the + * partition tree that it's part of. + */ +Datum +pg_partition_level(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid = PG_GETARG_OID(1); + List *ancestors = get_partition_ancestors(reloid); + int level; + + /* If root is specified, find reloid's distance from it. */ + if (OidIsValid(rootoid)) + { + ListCell *lc; + + if (!list_member_oid(ancestors, rootoid)) + return 0; + + level = 0; + foreach(lc, ancestors) + { + level++; + if (lfirst_oid(lc) == rootoid) + break; + } + } + else + /* Distance from the root of the whole tree. */ + level = list_length(ancestors); + + list_free(ancestors); + PG_RETURN_INT32(level); +} + +/* + * Returns OIDs of tables in a partition tree. + */ +Datum +pg_partition_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + bool include_all = PG_GETARG_BOOL(1); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (include_all) + partoids = find_all_inheritors(reloid, NoLock, NULL); + else + partoids = find_inheritance_children(reloid, NoLock); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns OIDs of leaf tables in a partition tree. + */ +Datum +pg_partition_leaf_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + List *leafoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + leafoids = get_partition_tree_leaf_tables(reloid); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(leafoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns number of leaf partitions tables in a partition tree + */ +static List * +get_partition_tree_leaf_tables(Oid relid) +{ + List *partitions = find_all_inheritors(relid, NoLock, NULL); + ListCell *lc; + List *result = NIL; + + foreach(lc, partitions) + { + Oid partoid = lfirst_oid(lc); + + if (get_rel_relkind(partoid) != RELKIND_PARTITIONED_TABLE) + result = lappend_oid(result, partoid); + } + + list_free(partitions); + return result; +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..19262c6c4d 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a14651010f..e1d190f81a 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,52 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# function to get the root partition parent +{ oid => '3423', descr => 'oid of the partition root parent', + proname => 'pg_partition_root_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' }, + +# function to get the partition parent +{ oid => '3424', descr => 'oid of the partition immediate parent', + proname => 'pg_partition_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_parent' }, + +# function to get the level of a partition in the partition tree of given root +{ oid => '3425', descr => 'level of a partition in the partition tree for given root table', + proname => 'pg_partition_level', prorettype => 'int4', + proargtypes => 'regclass regclass', prosrc => 'pg_partition_level' }, + +# function to get the level of a partition in the whole partition tree +{ oid => '3426', descr => 'level of a partition in the partition tree', + proname => 'pg_partition_level', prolang => '14', prorettype => 'int4', + proargtypes => 'regclass', + prosrc => 'select pg_catalog.pg_partition_level($1, \'0\')' }, + +# function to get OIDs of all tables in a given partition tree +{ oid => '3427', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass bool', + proallargtypes => '{regclass,bool,regclass}', + proargmodes => '{i,i,o}', + proargnames => '{relid,include_all,relid}', + prosrc => 'pg_partition_children' } + +# function to get OIDs of immediate +{ oid => '3428', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prolang => '14', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'select pg_catalog.pg_partition_children($1, \'false\')' } + +# function to get OIDs of all tables in a given partition tree +{ oid => '3429', descr => 'get OIDs of leaf partitions in a partition tree', + proname => 'pg_partition_leaf_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'pg_partition_leaf_children' } + ] diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..d396d17ff1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out new file mode 100644 index 0000000000..d34f3031c9 --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,204 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +-- pg_partition_level where partition level wrt whole-tree root is returned +select pg_partition_level('ptif_test01') as level; -- 2 + level +------- + 2 +(1 row) + +select pg_partition_level('ptif_test0') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2 + level +------- + 2 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test0') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- 0 + level +------- + 0 +(1 row) + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test | 0 | | ptif_test | 0 + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(6 rows) + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(3 rows) + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + level | level_size +-------+------------ + 0 | 0 + 1 | 16384 + 2 | 32768 +(3 rows) + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + total_size +------------ + 49152 +(1 row) + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + total_size +------------ + 49152 +(1 row) + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 +(1 row) + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + total_size +------------ + 16384 +(1 row) + +-- number of leaf partitions in the tree +select count(*) from pg_partition_leaf_children('ptif_test') as num_tables; + count +------- + 3 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 16f979c8d9..6cb820bbc4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate +test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 42632be675..7e374c2daa 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -188,6 +188,7 @@ test: reloptions test: hash_part test: indexing test: partition_aggregate +test: partition_info test: event_trigger test: fast_default test: stats diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql new file mode 100644 index 0000000000..e3b2fa0be0 --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,91 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; + +select pg_partition_parent('ptif_test0') as parent; +select pg_partition_parent('ptif_test01') as parent; +select pg_partition_root_parent('ptif_test01') as root_parent; + +-- pg_partition_level where partition level wrt whole-tree root is returned +select pg_partition_level('ptif_test01') as level; -- 2 +select pg_partition_level('ptif_test0') as level; -- 1 +select pg_partition_level('ptif_test') as level; -- 0 + +select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2 +select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1 +select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0 + +select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1 +select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0 +select pg_partition_level('ptif_test0', 'ptif_test0') as level; -- 0 +select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- 0 + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + +-- number of leaf partitions in the tree +select count(*) from pg_partition_leaf_children('ptif_test') as num_tables; + +drop table ptif_test; -- 2.11.0 --------------47B6CCCFBDE8802B935B0C7A-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v8] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 89 ++++++++++ src/backend/catalog/partition.c | 244 ++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 +++ src/include/catalog/pg_proc.dat | 48 ++++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/partition_info.out | 204 ++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 91 ++++++++++ 9 files changed, 697 insertions(+), 5 deletions(-) create mode 100644 src/test/regress/expected/partition_info.out create mode 100644 src/test/regress/sql/partition_info.sql diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index edc9be92a6..326f4dbe58 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19995,6 +19995,95 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); The function returns the number of new collation objects it created. </para> + <table id="functions-info-partition"> + <title>Partitioning Information Functions</title> + <tgroup cols="3"> + <thead> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row> + </thead> + + <tbody> + <row> + <entry><literal><function>pg_partition_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get parent if table is a partition, <literal>NULL</literal> otherwise</entry> + </row> + <row> + <entry><literal><function>pg_partition_root_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get topmost parent of a partition within partition tree</entry> + </row> + <row> + <entry><literal><function>pg_partition_level(<type>regclass</type>, <type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get level of a partition within partition tree with respect to given parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_level(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get level of a partition within partition tree with respect to topmost root parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>, <type>bool</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry> + get partitions of a table; only immediate partitions are returned, + unless all tables in the partition tree, including itself and + partitions of lower levels, are requested by passing + <literal>true</literal> for second argument + </entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>Shorthand for <literal>pg_partition_children(..., false)</literal></entry> + </row> + <row> + <entry><literal><function>pg_partition_leaf_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>get all leaf partitions of a given table</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + If the table passed to <function>pg_partition_root_parent</function> is not + a partition, it is returned as the its own root parent. If the table + passed for the second argument of <function>pg_partition_level</function> + is not same as the first argument or not an ancestor of the table in the + first argument, -1 is returned as the result. + </para> + + <para> + To check the total size of the data contained in + <structname>measurement</structname> table described in + <xref linkend="ddl-partitioning-declarative-example"/>, use the following + query: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_children('measurement', true) p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + <para> + One could have used <function>pg_partition_leaf_children</function> in + this case and got the same result as shown below: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_leaf_children('measurement') p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 558022647c..d90aaf8bb8 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -23,13 +23,16 @@ #include "catalog/partition.h" #include "catalog/pg_inherits.h" #include "catalog/pg_partitioned_table.h" +#include "funcapi.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "partitioning/partbounds.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -38,16 +41,14 @@ static Oid get_partition_parent_worker(Relation inhRel, Oid relid); static void get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors); +static Oid get_partition_root_parent(Oid relid); +static List *get_partition_tree_leaf_tables(Oid relid); /* * get_partition_parent * Obtain direct parent of given relation * * Returns inheritance parent of a partition by scanning pg_inherits - * - * Note: Because this function assumes that the relation whose OID is passed - * as an argument will have precisely one parent, it should only be called - * when it is known that the relation is a partition. */ Oid get_partition_parent(Oid relid) @@ -55,6 +56,9 @@ get_partition_parent(Oid relid) Relation catalogRelation; Oid result; + if (!get_rel_relispartition(relid)) + return InvalidOid; + catalogRelation = heap_open(InheritsRelationId, AccessShareLock); result = get_partition_parent_worker(catalogRelation, relid); @@ -71,6 +75,10 @@ get_partition_parent(Oid relid) * get_partition_parent_worker * Scan the pg_inherits relation to return the OID of the parent of the * given relation + * + * Note: Because this function assumes that the relation whose OID is passed + * as an argument will have precisely one parent, it should only be called + * when it is known that the relation is a partition. */ static Oid get_partition_parent_worker(Relation inhRel, Oid relid) @@ -148,6 +156,28 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors) } /* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + List *ancestors; + Oid result; + + if (!get_rel_relispartition(relid)) + return InvalidOid; + + ancestors = get_partition_ancestors(relid); + result = llast_oid(ancestors); + Assert(!get_rel_relispartition(result)); + list_free(ancestors); + + return result; +} + +/* * map_partition_varattnos - maps varattno of any Vars in expr from the * attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which * may be either a leaf partition or a partitioned table, but both of which @@ -357,3 +387,209 @@ get_proposed_default_constraint(List *new_part_constraints) return make_ands_implicit(defPartConstraint); } + +/* + * SQL wrapper around get_partition_root_parent(). + */ +Datum +pg_partition_root_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + + /* + * If the relation is actually a partition, 'rootoid' has been set to the + * OID of the root table in the partition tree. + */ + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + + /* + * Otherwise, the table's not a partition. That is, it's either the root + * table in a partition tree or a standalone table that's not part of any + * partition tree. In any case, return the table OID itself as the + * result. + */ + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent(). + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + + /* + * If the relation is actually a partition, 'parentoid' has been set to + * the OID of the immediate parent. + */ + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + + /* Not a partition, return NULL. */ + PG_RETURN_NULL(); +} + +/* + * Returns an integer representing the level a given partition is at in the + * partition tree that it's part of. + */ +Datum +pg_partition_level(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid = PG_GETARG_OID(1); + List *ancestors = get_partition_ancestors(reloid); + int level; + + /* If root is specified, find reloid's distance from it. */ + if (OidIsValid(rootoid)) + { + ListCell *lc; + + if (reloid == rootoid) + return 0; + + if (!list_member_oid(ancestors, rootoid)) + return -1; + + level = 0; + foreach(lc, ancestors) + { + level++; + if (lfirst_oid(lc) == rootoid) + break; + } + } + else + /* Distance from the root of the whole tree. */ + level = list_length(ancestors); + + list_free(ancestors); + PG_RETURN_INT32(level); +} + +/* + * Returns OIDs of tables in a partition tree. + */ +Datum +pg_partition_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + bool include_all = PG_GETARG_BOOL(1); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (include_all) + partoids = find_all_inheritors(reloid, NoLock, NULL); + else + partoids = find_inheritance_children(reloid, NoLock); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns OIDs of leaf tables in a partition tree. + */ +Datum +pg_partition_leaf_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + List *leafoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + leafoids = get_partition_tree_leaf_tables(reloid); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(leafoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns number of leaf partitions tables in a partition tree + */ +static List * +get_partition_tree_leaf_tables(Oid relid) +{ + List *partitions = find_all_inheritors(relid, NoLock, NULL); + ListCell *lc; + List *result = NIL; + + foreach(lc, partitions) + { + Oid partoid = lfirst_oid(lc); + + if (get_rel_relkind(partoid) != RELKIND_PARTITIONED_TABLE) + result = lappend_oid(result, partoid); + } + + list_free(partitions); + return result; +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..19262c6c4d 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a14651010f..e1d190f81a 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,52 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# function to get the root partition parent +{ oid => '3423', descr => 'oid of the partition root parent', + proname => 'pg_partition_root_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' }, + +# function to get the partition parent +{ oid => '3424', descr => 'oid of the partition immediate parent', + proname => 'pg_partition_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_parent' }, + +# function to get the level of a partition in the partition tree of given root +{ oid => '3425', descr => 'level of a partition in the partition tree for given root table', + proname => 'pg_partition_level', prorettype => 'int4', + proargtypes => 'regclass regclass', prosrc => 'pg_partition_level' }, + +# function to get the level of a partition in the whole partition tree +{ oid => '3426', descr => 'level of a partition in the partition tree', + proname => 'pg_partition_level', prolang => '14', prorettype => 'int4', + proargtypes => 'regclass', + prosrc => 'select pg_catalog.pg_partition_level($1, \'0\')' }, + +# function to get OIDs of all tables in a given partition tree +{ oid => '3427', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass bool', + proallargtypes => '{regclass,bool,regclass}', + proargmodes => '{i,i,o}', + proargnames => '{relid,include_all,relid}', + prosrc => 'pg_partition_children' } + +# function to get OIDs of immediate +{ oid => '3428', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prolang => '14', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'select pg_catalog.pg_partition_children($1, \'false\')' } + +# function to get OIDs of all tables in a given partition tree +{ oid => '3429', descr => 'get OIDs of leaf partitions in a partition tree', + proname => 'pg_partition_leaf_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'pg_partition_leaf_children' } + ] diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..d396d17ff1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out new file mode 100644 index 0000000000..e93069913c --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,204 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +-- pg_partition_level where partition level wrt whole-tree root is returned +select pg_partition_level('ptif_test01') as level; -- 2 + level +------- + 2 +(1 row) + +select pg_partition_level('ptif_test0') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2 + level +------- + 2 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test0') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- -1 + level +------- + -1 +(1 row) + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test | 0 | | ptif_test | 0 + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(6 rows) + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(3 rows) + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + level | level_size +-------+------------ + 0 | 0 + 1 | 16384 + 2 | 32768 +(3 rows) + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + total_size +------------ + 49152 +(1 row) + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + total_size +------------ + 49152 +(1 row) + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 +(1 row) + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + total_size +------------ + 16384 +(1 row) + +-- number of leaf partitions in the tree +select count(*) from pg_partition_leaf_children('ptif_test') as num_tables; + count +------- + 3 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 16f979c8d9..6cb820bbc4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate +test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 42632be675..7e374c2daa 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -188,6 +188,7 @@ test: reloptions test: hash_part test: indexing test: partition_aggregate +test: partition_info test: event_trigger test: fast_default test: stats diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql new file mode 100644 index 0000000000..5675e8c526 --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,91 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; + +select pg_partition_parent('ptif_test0') as parent; +select pg_partition_parent('ptif_test01') as parent; +select pg_partition_root_parent('ptif_test01') as root_parent; + +-- pg_partition_level where partition level wrt whole-tree root is returned +select pg_partition_level('ptif_test01') as level; -- 2 +select pg_partition_level('ptif_test0') as level; -- 1 +select pg_partition_level('ptif_test') as level; -- 0 + +select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2 +select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1 +select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0 + +select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1 +select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0 +select pg_partition_level('ptif_test0', 'ptif_test0') as level; -- 0 +select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- -1 + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + +-- number of leaf partitions in the tree +select count(*) from pg_partition_leaf_children('ptif_test') as num_tables; + +drop table ptif_test; -- 2.11.0 --------------87361936771E96D5B9B3CE60-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v2] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 34 ++++++++ src/backend/catalog/partition.c | 129 ++++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 +++++ src/include/catalog/partition.h | 1 + src/include/catalog/pg_proc.dat | 18 ++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/create_table.out | 60 ++++++++++++++ src/test/regress/sql/create_table.sql | 24 ++++++ 8 files changed, 285 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5dce8ef178..df621d1e17 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19995,6 +19995,40 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); The function returns the number of new collation objects it created. </para> + <table id="functions-info-partition"> + <title>Partitioning Information Functions</title> + <tgroup cols="3"> + <thead> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row> + </thead> + + <tbody> + <row> + <entry><literal><function>pg_partition_root_parent(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get root table of partition tree of which the table is part</entry> + </row> + <row> + <entry><literal><function>pg_partition_parent(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get parent table if the table is a partition, <literal>NULL</literal> otherwise</entry> + </row> + <row> + <entry><literal><function>pg_partition_tree_tables(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>get all tables in partition tree under given root table</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + If the table passed to <function>pg_partition_root_parent</function> is not + a partition, the same table is returned as the result. Result of + <function>pg_partition_tree_tables</function> also contains the table + that's passed to it as the first row. + </para> + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 558022647c..5b3e8d52c5 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -23,13 +23,16 @@ #include "catalog/partition.h" #include "catalog/pg_inherits.h" #include "catalog/pg_partitioned_table.h" +#include "funcapi.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "partitioning/partbounds.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -44,10 +47,6 @@ static void get_partition_ancestors_worker(Relation inhRel, Oid relid, * Obtain direct parent of given relation * * Returns inheritance parent of a partition by scanning pg_inherits - * - * Note: Because this function assumes that the relation whose OID is passed - * as an argument will have precisely one parent, it should only be called - * when it is known that the relation is a partition. */ Oid get_partition_parent(Oid relid) @@ -55,6 +54,9 @@ get_partition_parent(Oid relid) Relation catalogRelation; Oid result; + if (!get_rel_relispartition(relid)) + return InvalidOid; + catalogRelation = heap_open(InheritsRelationId, AccessShareLock); result = get_partition_parent_worker(catalogRelation, relid); @@ -71,6 +73,10 @@ get_partition_parent(Oid relid) * get_partition_parent_worker * Scan the pg_inherits relation to return the OID of the parent of the * given relation + * + * Note: Because this function assumes that the relation whose OID is passed + * as an argument will have precisely one parent, it should only be called + * when it is known that the relation is a partition. */ static Oid get_partition_parent_worker(Relation inhRel, Oid relid) @@ -148,6 +154,27 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors) } /* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + List *ancestors; + Oid result; + + if (!get_rel_relispartition(relid)) + return InvalidOid; + + ancestors = get_partition_ancestors(relid); + result = llast_oid(ancestors); + list_free(ancestors); + + return result; +} + +/* * map_partition_varattnos - maps varattno of any Vars in expr from the * attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which * may be either a leaf partition or a partitioned table, but both of which @@ -357,3 +384,97 @@ get_proposed_default_constraint(List *new_part_constraints) return make_ands_implicit(defPartConstraint); } + +/* + * SQL wrapper around get_partition_root_parent(). + */ +Datum +pg_partition_root_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + + /* + * If the relation is actually a partition, 'rootoid' has been set to the + * OID of the root table in the partition tree. + */ + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + /* + * Otherwise, the table's not a partition. That is, it's either the root + * table in a partition tree or a standalone table that's not part of any + * partition tree. In any case, return the table OID itself as the + * result. + */ + else + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent(). + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + + /* + * If the relation is actually a partition, 'parentoid' has been set to + * the OID of the immediate parent. + */ + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + else + /* Not a partition, return NULL. */ + PG_RETURN_NULL(); +} + +/* + * Returns OIDs of tables in a partition tree. + */ +Datum +pg_partition_tree_tables(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + partoids = find_all_inheritors(reloid, NoLock, NULL); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..19262c6c4d 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h index 1f49e5d3a9..a2b11f40bc 100644 --- a/src/include/catalog/partition.h +++ b/src/include/catalog/partition.h @@ -32,6 +32,7 @@ typedef struct PartitionDescData extern Oid get_partition_parent(Oid relid); extern List *get_partition_ancestors(Oid relid); +extern Oid get_partition_root_parent(Oid relid); extern List *map_partition_varattnos(List *expr, int fromrel_varno, Relation to_rel, Relation from_rel, bool *found_whole_row); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 40d54ed030..b4725b8634 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,22 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# function to get the root partition parent +{ oid => '3423', descr => 'oid of the partition root parent', + proname => 'pg_partition_root_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' }, + +# function to get the partition parent +{ oid => '3424', descr => 'oid of the partition immediate parent', + proname => 'pg_partition_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_parent' }, + +# function to get OIDs of all tables in a given partition tree +{ oid => '3425', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_tree_tables', prorettype => '2205', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', prosrc => 'pg_partition_tree_tables' } + ] diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..d396d17ff1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 672719e5d5..f702518d4e 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -909,3 +909,63 @@ ERROR: cannot create a temporary relation as partition of permanent relation "p create temp table temp_part partition of temp_parted default; -- ok drop table perm_parted cascade; drop table temp_parted cascade; +-- tests to show partition tree inspection functions +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (maxvalue) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +insert into ptif_test select i, 1 from generate_series(-5, 5) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent +from pg_partition_tree_tables('ptif_test') p; + relname | parent | root_parent +-------------+------------+------------- + ptif_test | | ptif_test + ptif_test0 | ptif_test | ptif_test + ptif_test1 | ptif_test | ptif_test + ptif_test01 | ptif_test0 | ptif_test + ptif_test11 | ptif_test1 | ptif_test +(5 rows) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test') p; + relname | parent | root_parent | size +-------------+------------+-------------+------ + ptif_test | | ptif_test | 0 + ptif_test0 | ptif_test | ptif_test | 0 + ptif_test1 | ptif_test | ptif_test | 0 + ptif_test01 | ptif_test0 | ptif_test | 8192 + ptif_test11 | ptif_test1 | ptif_test | 8192 +(5 rows) + +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test') p; + total_size +------------ + 16384 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 78944950fe..d0a083dd69 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -735,3 +735,27 @@ create temp table temp_part partition of perm_parted default; -- error create temp table temp_part partition of temp_parted default; -- ok drop table perm_parted cascade; drop table temp_parted cascade; + +-- tests to show partition tree inspection functions +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (maxvalue) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +insert into ptif_test select i, 1 from generate_series(-5, 5) i; + +select pg_partition_parent('ptif_test0') as parent; +select pg_partition_parent('ptif_test01') as parent; +select pg_partition_root_parent('ptif_test01') as root_parent; +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent +from pg_partition_tree_tables('ptif_test') p; +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test') p; +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test') p; +drop table ptif_test; -- 2.11.0 --------------DE56535FB8085EFCFB300C28-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v5] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 86 ++++++++++ src/backend/catalog/partition.c | 241 ++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 +++ src/include/catalog/pg_proc.dat | 47 ++++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/partition_info.out | 161 ++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 80 +++++++++ 9 files changed, 636 insertions(+), 5 deletions(-) create mode 100644 src/test/regress/expected/partition_info.out create mode 100644 src/test/regress/sql/partition_info.sql diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index edc9be92a6..829fcae9dd 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19995,6 +19995,92 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); The function returns the number of new collation objects it created. </para> + <table id="functions-info-partition"> + <title>Partitioning Information Functions</title> + <tgroup cols="3"> + <thead> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row> + </thead> + + <tbody> + <row> + <entry><literal><function>pg_partition_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get parent if table is a partition, <literal>NULL</literal> otherwise</entry> + </row> + <row> + <entry><literal><function>pg_partition_root_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get topmost parent of a partition within partition tree</entry> + </row> + <row> + <entry><literal><function>pg_partition_level(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get level of a partition within partition tree with respect to the topmost parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>, <type>bool</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry> + get partitions of a table; only immediate partitions are returned, + unless all tables in the partition tree, including itself and + partitions of lower levels, are requested by passing + <literal>true</literal> for second argument + </entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>Shorthand for <literal>pg_partition_children(..., false)</literal></entry> + </row> + <row> + <entry><literal><function>pg_partition_leaf_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>get all leaf partitions of a given table</entry> + </row> + <row> + <entry><literal><function>pg_partition_tree_leaf_count(<type>regclass</type>)</function></literal></entry> + <entry><type>integer</type></entry> + <entry>get number of leaf tables in partition tree with given table as top-most parent</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + If the table passed to <function>pg_partition_root_parent</function> is not + a partition, the same table is returned as the result. + </para> + + <para> + For example, to check the total size of the data contained in + <structname>measurement</structname> table described in + <xref linkend="ddl-partitioning-declarative-example"/>, use the following + query: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_children('measurement', true) p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + <para> + One could have used <function>pg_partition_leaf_children</function> in + this case and got the same result as shown below: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_leaf_children('measurement') p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 558022647c..299d8e79e7 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -23,13 +23,16 @@ #include "catalog/partition.h" #include "catalog/pg_inherits.h" #include "catalog/pg_partitioned_table.h" +#include "funcapi.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "partitioning/partbounds.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -38,16 +41,14 @@ static Oid get_partition_parent_worker(Relation inhRel, Oid relid); static void get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors); +static Oid get_partition_root_parent(Oid relid); +static List *get_partition_tree_leaf_tables(Oid relid); /* * get_partition_parent * Obtain direct parent of given relation * * Returns inheritance parent of a partition by scanning pg_inherits - * - * Note: Because this function assumes that the relation whose OID is passed - * as an argument will have precisely one parent, it should only be called - * when it is known that the relation is a partition. */ Oid get_partition_parent(Oid relid) @@ -55,6 +56,9 @@ get_partition_parent(Oid relid) Relation catalogRelation; Oid result; + if (!get_rel_relispartition(relid)) + return InvalidOid; + catalogRelation = heap_open(InheritsRelationId, AccessShareLock); result = get_partition_parent_worker(catalogRelation, relid); @@ -71,6 +75,10 @@ get_partition_parent(Oid relid) * get_partition_parent_worker * Scan the pg_inherits relation to return the OID of the parent of the * given relation + * + * Note: Because this function assumes that the relation whose OID is passed + * as an argument will have precisely one parent, it should only be called + * when it is known that the relation is a partition. */ static Oid get_partition_parent_worker(Relation inhRel, Oid relid) @@ -148,6 +156,28 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors) } /* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + List *ancestors; + Oid result; + + if (!get_rel_relispartition(relid)) + return InvalidOid; + + ancestors = get_partition_ancestors(relid); + result = llast_oid(ancestors); + Assert(!get_rel_relispartition(result)); + list_free(ancestors); + + return result; +} + +/* * map_partition_varattnos - maps varattno of any Vars in expr from the * attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which * may be either a leaf partition or a partitioned table, but both of which @@ -357,3 +387,206 @@ get_proposed_default_constraint(List *new_part_constraints) return make_ands_implicit(defPartConstraint); } + +/* + * SQL wrapper around get_partition_root_parent(). + */ +Datum +pg_partition_root_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + + /* + * If the relation is actually a partition, 'rootoid' has been set to the + * OID of the root table in the partition tree. + */ + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + + /* + * Otherwise, the table's not a partition. That is, it's either the root + * table in a partition tree or a standalone table that's not part of any + * partition tree. In any case, return the table OID itself as the + * result. + */ + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent(). + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + + /* + * If the relation is actually a partition, 'parentoid' has been set to + * the OID of the immediate parent. + */ + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + + /* Not a partition, return NULL. */ + PG_RETURN_NULL(); +} + +/* + * Returns an integer representing the level a given partition is at in the + * partition tree that it's part of. + */ +Datum +pg_partition_level(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + List *ancestors = get_partition_ancestors(reloid); + int level = list_length(ancestors); + + list_free(ancestors); + PG_RETURN_INT32(level); +} + +/* + * Returns OIDs of tables in a partition tree. + */ +Datum +pg_partition_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + bool include_all = PG_GETARG_BOOL(1); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (include_all) + partoids = find_all_inheritors(reloid, NoLock, NULL); + else + partoids = find_inheritance_children(reloid, NoLock); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns OIDs of leaf tables in a partition tree. + */ +Datum +pg_partition_leaf_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + List *leafoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + leafoids = get_partition_tree_leaf_tables(reloid); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(leafoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns number of leaf partitions tables in a partition tree + */ +static List * +get_partition_tree_leaf_tables(Oid relid) +{ + List *partitions = find_all_inheritors(relid, NoLock, NULL); + ListCell *lc; + List *result = NIL; + + foreach(lc, partitions) + { + Oid partoid = lfirst_oid(lc); + + if (get_rel_relkind(partoid) != RELKIND_PARTITIONED_TABLE) + result = lappend_oid(result, partoid); + } + + list_free(partitions); + return result; +} + +/* + * Returns number of leaf partitions tables in a partition tree + */ +Datum +pg_partition_children_leaf_count(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + List *partitions = find_all_inheritors(reloid, NoLock, NULL); + ListCell *lc; + int result = 0; + + foreach(lc, partitions) + { + if (get_rel_relkind(lfirst_oid(lc)) != RELKIND_PARTITIONED_TABLE) + result++; + } + + list_free(partitions); + PG_RETURN_INT32(result); +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..19262c6c4d 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a14651010f..8b97bdbbde 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,51 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# function to get the root partition parent +{ oid => '3423', descr => 'oid of the partition root parent', + proname => 'pg_partition_root_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' }, + +# function to get the partition parent +{ oid => '3424', descr => 'oid of the partition immediate parent', + proname => 'pg_partition_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_parent' }, + +# function to get the level where a partition is at in the partition tree +{ oid => '3425', descr => 'level of a partition in the partition tree', + proname => 'pg_partition_level', prorettype => 'int4', + proargtypes => 'regclass', prosrc => 'pg_partition_level' }, + +# function to get OIDs of all tables in a given partition tree +{ oid => '3426', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass bool', + proallargtypes => '{regclass,bool,regclass}', + proargmodes => '{i,i,o}', + proargnames => '{relid,include_all,relid}', + prosrc => 'pg_partition_children' } + +# function to get OIDs of immediate +{ oid => '3427', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prolang => '14', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'select pg_catalog.pg_partition_children($1, \'false\')' } + +# function to get OIDs of all tables in a given partition tree +{ oid => '3428', descr => 'get OIDs of leaf partitions in a partition tree', + proname => 'pg_partition_leaf_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'pg_partition_leaf_children' } + +# function to get the number of leaf partitions in a given partition tree +{ oid => '3429', descr => 'number of leaf partitions in the partition tree', + proname => 'pg_partition_children_leaf_count', prorettype => 'int4', + proargtypes => 'regclass', prosrc => 'pg_partition_children_leaf_count' }, + ] diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..d396d17ff1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out new file mode 100644 index 0000000000..86046b7f7b --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,161 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +select pg_partition_level('ptif_test01') as level; -- 2 + level +------- + 2 +(1 row) + +select pg_partition_level('ptif_test0') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test') as level; -- 0 + level +------- + 0 +(1 row) + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test | 0 | | ptif_test | 0 + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(6 rows) + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(3 rows) + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + level | level_size +-------+------------ + 0 | 0 + 1 | 16384 + 2 | 32768 +(3 rows) + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + total_size +------------ + 49152 +(1 row) + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + total_size +------------ + 49152 +(1 row) + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 +(1 row) + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + total_size +------------ + 16384 +(1 row) + +-- number of leaf partitions in the tree +select pg_partition_children_leaf_count('ptif_test') as num_tables; + num_tables +------------ + 3 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 16f979c8d9..6cb820bbc4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate +test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 42632be675..7e374c2daa 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -188,6 +188,7 @@ test: reloptions test: hash_part test: indexing test: partition_aggregate +test: partition_info test: event_trigger test: fast_default test: stats diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql new file mode 100644 index 0000000000..6c031e8198 --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,80 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; + +select pg_partition_parent('ptif_test0') as parent; +select pg_partition_parent('ptif_test01') as parent; +select pg_partition_root_parent('ptif_test01') as root_parent; +select pg_partition_level('ptif_test01') as level; -- 2 +select pg_partition_level('ptif_test0') as level; -- 1 +select pg_partition_level('ptif_test') as level; -- 0 + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + +-- number of leaf partitions in the tree +select pg_partition_children_leaf_count('ptif_test') as num_tables; + +drop table ptif_test; -- 2.11.0 --------------FB017433C2081A1BE90BB8A4-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v4] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 56 ++++++++++ src/backend/catalog/partition.c | 155 ++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 ++++ src/include/catalog/partition.h | 1 + src/include/catalog/pg_proc.dat | 24 +++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/partition_info.out | 88 +++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 40 +++++++ 10 files changed, 385 insertions(+), 5 deletions(-) create mode 100644 src/test/regress/expected/partition_info.out create mode 100644 src/test/regress/sql/partition_info.sql diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index edc9be92a6..33119f2265 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19995,6 +19995,62 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); The function returns the number of new collation objects it created. </para> + <table id="functions-info-partition"> + <title>Partitioning Information Functions</title> + <tgroup cols="3"> + <thead> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row> + </thead> + + <tbody> + <row> + <entry><literal><function>pg_partition_parent(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get parent if table is a partition, <literal>NULL</literal> otherwise</entry> + </row> + <row> + <entry><literal><function>pg_partition_root_parent(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get top-most parent of a partition within partition tree</entry> + </row> + <row> + <entry><literal><function>pg_partition_tree_tables(<parameter>regclass</parameter>, <parameter>bool</parameter>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>get all tables in partition tree with given table as top-most parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_tree_number_of_tables(<parameter>regclass</parameter>)</function></literal></entry> + <entry><type>integer</type></entry> + <entry>get number of tables in partition tree with given table as top-most parent</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + If the table passed to <function>pg_partition_root_parent</function> is not + a partition, the same table is returned as the result. Result of + <function>pg_partition_tree_tables</function> also contains the table + that's passed to it as the first row, unless explicitly requested not + to be included by passing <literal>false</literal> for the second + argument. + </para> + + <para> + For example, to check the total size of the data contained in + <structname>measurement</structname> table described in + <xref linkend="ddl-partitioning-declarative-example"/>, use the following + query: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_tree_tables('measurement', true) p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 558022647c..20635ce513 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -23,13 +23,16 @@ #include "catalog/partition.h" #include "catalog/pg_inherits.h" #include "catalog/pg_partitioned_table.h" +#include "funcapi.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "partitioning/partbounds.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -44,10 +47,6 @@ static void get_partition_ancestors_worker(Relation inhRel, Oid relid, * Obtain direct parent of given relation * * Returns inheritance parent of a partition by scanning pg_inherits - * - * Note: Because this function assumes that the relation whose OID is passed - * as an argument will have precisely one parent, it should only be called - * when it is known that the relation is a partition. */ Oid get_partition_parent(Oid relid) @@ -55,6 +54,9 @@ get_partition_parent(Oid relid) Relation catalogRelation; Oid result; + if (!get_rel_relispartition(relid)) + return InvalidOid; + catalogRelation = heap_open(InheritsRelationId, AccessShareLock); result = get_partition_parent_worker(catalogRelation, relid); @@ -71,6 +73,10 @@ get_partition_parent(Oid relid) * get_partition_parent_worker * Scan the pg_inherits relation to return the OID of the parent of the * given relation + * + * Note: Because this function assumes that the relation whose OID is passed + * as an argument will have precisely one parent, it should only be called + * when it is known that the relation is a partition. */ static Oid get_partition_parent_worker(Relation inhRel, Oid relid) @@ -148,6 +154,28 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors) } /* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + List *ancestors; + Oid result; + + if (!get_rel_relispartition(relid)) + return InvalidOid; + + ancestors = get_partition_ancestors(relid); + result = llast_oid(ancestors); + Assert(!get_rel_relispartition(result)); + list_free(ancestors); + + return result; +} + +/* * map_partition_varattnos - maps varattno of any Vars in expr from the * attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which * may be either a leaf partition or a partitioned table, but both of which @@ -357,3 +385,122 @@ get_proposed_default_constraint(List *new_part_constraints) return make_ands_implicit(defPartConstraint); } + +/* + * SQL wrapper around get_partition_root_parent(). + */ +Datum +pg_partition_root_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + + /* + * If the relation is actually a partition, 'rootoid' has been set to the + * OID of the root table in the partition tree. + */ + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + + /* + * Otherwise, the table's not a partition. That is, it's either the root + * table in a partition tree or a standalone table that's not part of any + * partition tree. In any case, return the table OID itself as the + * result. + */ + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent(). + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + + /* + * If the relation is actually a partition, 'parentoid' has been set to + * the OID of the immediate parent. + */ + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + + /* Not a partition, return NULL. */ + PG_RETURN_NULL(); +} + +/* + * Returns OIDs of tables in a partition tree. + */ +Datum +pg_partition_tree_tables(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + bool include_self = PG_GETARG_BOOL(1); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + partoids = find_all_inheritors(reloid, NoLock, NULL); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + + /* + * Passed in OID is put at the head of the list returned by + * find_all_inheritors(). If user asked to not include the passed in + * relid in the result, skip it by making *lc point to the next + * list member. + */ + if (!include_self) + *lc = lnext(*lc); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns number of tables in a partition tree + */ +Datum +pg_partition_tree_number_of_tables(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + List *partitions = find_all_inheritors(reloid, NoLock, NULL); + int result = list_length(partitions); + + list_free(partitions); + PG_RETURN_INT32(result); +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..19262c6c4d 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h index 1f49e5d3a9..a2b11f40bc 100644 --- a/src/include/catalog/partition.h +++ b/src/include/catalog/partition.h @@ -32,6 +32,7 @@ typedef struct PartitionDescData extern Oid get_partition_parent(Oid relid); extern List *get_partition_ancestors(Oid relid); +extern Oid get_partition_root_parent(Oid relid); extern List *map_partition_varattnos(List *expr, int fromrel_varno, Relation to_rel, Relation from_rel, bool *found_whole_row); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a14651010f..5835a9aaf2 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,28 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# function to get the root partition parent +{ oid => '3423', descr => 'oid of the partition root parent', + proname => 'pg_partition_root_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' }, + +# function to get the partition parent +{ oid => '3424', descr => 'oid of the partition immediate parent', + proname => 'pg_partition_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_parent' }, + +# function to get OIDs of all tables in a given partition tree +{ oid => '3425', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_tree_tables', prorettype => '2205', + prorows => '100', proretset => 't', proargtypes => 'regclass bool', + proallargtypes => '{regclass,bool,regclass}', + proargmodes => '{i,i,o}', + proargnames => '{relid,include_self,relid}', + prosrc => 'pg_partition_tree_tables' } + +# function to get the number of tables in a given partition tree +{ oid => '3426', descr => 'number of tables in the partition tree', + proname => 'pg_partition_tree_number_of_tables', prorettype => 'int4', + proargtypes => 'regclass', prosrc => 'pg_partition_tree_number_of_tables' }, + ] diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..d396d17ff1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out new file mode 100644 index 0000000000..745b9fcbb5 --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,88 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (maxvalue) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +insert into ptif_test select i, 1 from generate_series(-5, 5) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent +from pg_partition_tree_tables('ptif_test', true) p; + relname | parent | root_parent +-------------+------------+------------- + ptif_test | | ptif_test + ptif_test0 | ptif_test | ptif_test + ptif_test1 | ptif_test | ptif_test + ptif_test01 | ptif_test0 | ptif_test + ptif_test11 | ptif_test1 | ptif_test +(5 rows) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test', true) p; + relname | parent | root_parent | size +-------------+------------+-------------+------ + ptif_test | | ptif_test | 0 + ptif_test0 | ptif_test | ptif_test | 0 + ptif_test1 | ptif_test | ptif_test | 0 + ptif_test01 | ptif_test0 | ptif_test | 8192 + ptif_test11 | ptif_test1 | ptif_test | 8192 +(5 rows) + +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test', true) p; + total_size +------------ + 16384 +(1 row) + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test', false) p; + relname | parent | root_parent | size +-------------+------------+-------------+------ + ptif_test0 | ptif_test | ptif_test | 0 + ptif_test1 | ptif_test | ptif_test | 0 + ptif_test01 | ptif_test0 | ptif_test | 8192 + ptif_test11 | ptif_test1 | ptif_test | 8192 +(4 rows) + +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test', false) p; + total_size +------------ + 16384 +(1 row) + +select pg_partition_tree_number_of_tables('ptif_test') as num_tables; + num_tables +------------ + 5 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 16f979c8d9..6cb820bbc4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate +test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 42632be675..7e374c2daa 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -188,6 +188,7 @@ test: reloptions test: hash_part test: indexing test: partition_aggregate +test: partition_info test: event_trigger test: fast_default test: stats diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql new file mode 100644 index 0000000000..6772740fca --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,40 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (maxvalue) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +insert into ptif_test select i, 1 from generate_series(-5, 5) i; + +select pg_partition_parent('ptif_test0') as parent; +select pg_partition_parent('ptif_test01') as parent; +select pg_partition_root_parent('ptif_test01') as root_parent; + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent +from pg_partition_tree_tables('ptif_test', true) p; + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test', true) p; + +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test', true) p; + +select p as relname, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_tree_tables('ptif_test', false) p; + +select sum(pg_relation_size(p)) as total_size +from pg_partition_tree_tables('ptif_test', false) p; + +select pg_partition_tree_number_of_tables('ptif_test') as num_tables; + +drop table ptif_test; -- 2.11.0 --------------1A2E6EFB868FDFF2FD7331C8-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v7] Add assorted partition reporting functions @ 2018-01-16 10:02 amit <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: amit @ 2018-01-16 10:02 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 86 ++++++++++ src/backend/catalog/partition.c | 244 ++++++++++++++++++++++++++- src/backend/utils/cache/lsyscache.c | 22 +++ src/include/catalog/pg_proc.dat | 48 ++++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/partition_info.out | 204 ++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/partition_info.sql | 91 ++++++++++ 9 files changed, 694 insertions(+), 5 deletions(-) create mode 100644 src/test/regress/expected/partition_info.out create mode 100644 src/test/regress/sql/partition_info.sql diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index edc9be92a6..e1b7ace898 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19995,6 +19995,92 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); The function returns the number of new collation objects it created. </para> + <table id="functions-info-partition"> + <title>Partitioning Information Functions</title> + <tgroup cols="3"> + <thead> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row> + </thead> + + <tbody> + <row> + <entry><literal><function>pg_partition_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get parent if table is a partition, <literal>NULL</literal> otherwise</entry> + </row> + <row> + <entry><literal><function>pg_partition_root_parent(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get topmost parent of a partition within partition tree</entry> + </row> + <row> + <entry><literal><function>pg_partition_level(<type>regclass</type>, <type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get level of a partition within partition tree with respect to given parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_level(<type>regclass</type>)</function></literal></entry> + <entry><type>regclass</type></entry> + <entry>get level of a partition within partition tree with respect to topmost root parent</entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>, <type>bool</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry> + get partitions of a table; only immediate partitions are returned, + unless all tables in the partition tree, including itself and + partitions of lower levels, are requested by passing + <literal>true</literal> for second argument + </entry> + </row> + <row> + <entry><literal><function>pg_partition_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>Shorthand for <literal>pg_partition_children(..., false)</literal></entry> + </row> + <row> + <entry><literal><function>pg_partition_leaf_children(<type>regclass</type>)</function></literal></entry> + <entry><type>setof regclass</type></entry> + <entry>get all leaf partitions of a given table</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + If the table passed to <function>pg_partition_root_parent</function> is not + a partition, the same table is returned as the result. + </para> + + <para> + For example, to check the total size of the data contained in + <structname>measurement</structname> table described in + <xref linkend="ddl-partitioning-declarative-example"/>, use the following + query: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_children('measurement', true) p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + <para> + One could have used <function>pg_partition_leaf_children</function> in + this case and got the same result as shown below: + </para> + +<programlisting> +select pg_size_pretty(sum(pg_relation_size(p))) as total_size from pg_partition_leaf_children('measurement') p; + total_size +------------ + 24 kB +(1 row) +</programlisting> + + </sect2> <sect2 id="functions-admin-index"> diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 558022647c..d90aaf8bb8 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -23,13 +23,16 @@ #include "catalog/partition.h" #include "catalog/pg_inherits.h" #include "catalog/pg_partitioned_table.h" +#include "funcapi.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "partitioning/partbounds.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -38,16 +41,14 @@ static Oid get_partition_parent_worker(Relation inhRel, Oid relid); static void get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors); +static Oid get_partition_root_parent(Oid relid); +static List *get_partition_tree_leaf_tables(Oid relid); /* * get_partition_parent * Obtain direct parent of given relation * * Returns inheritance parent of a partition by scanning pg_inherits - * - * Note: Because this function assumes that the relation whose OID is passed - * as an argument will have precisely one parent, it should only be called - * when it is known that the relation is a partition. */ Oid get_partition_parent(Oid relid) @@ -55,6 +56,9 @@ get_partition_parent(Oid relid) Relation catalogRelation; Oid result; + if (!get_rel_relispartition(relid)) + return InvalidOid; + catalogRelation = heap_open(InheritsRelationId, AccessShareLock); result = get_partition_parent_worker(catalogRelation, relid); @@ -71,6 +75,10 @@ get_partition_parent(Oid relid) * get_partition_parent_worker * Scan the pg_inherits relation to return the OID of the parent of the * given relation + * + * Note: Because this function assumes that the relation whose OID is passed + * as an argument will have precisely one parent, it should only be called + * when it is known that the relation is a partition. */ static Oid get_partition_parent_worker(Relation inhRel, Oid relid) @@ -148,6 +156,28 @@ get_partition_ancestors_worker(Relation inhRel, Oid relid, List **ancestors) } /* + * get_partition_root_parent + * + * Returns root inheritance ancestor of a partition. + */ +Oid +get_partition_root_parent(Oid relid) +{ + List *ancestors; + Oid result; + + if (!get_rel_relispartition(relid)) + return InvalidOid; + + ancestors = get_partition_ancestors(relid); + result = llast_oid(ancestors); + Assert(!get_rel_relispartition(result)); + list_free(ancestors); + + return result; +} + +/* * map_partition_varattnos - maps varattno of any Vars in expr from the * attno's of 'from_rel' to the attno's of 'to_rel' partition, each of which * may be either a leaf partition or a partitioned table, but both of which @@ -357,3 +387,209 @@ get_proposed_default_constraint(List *new_part_constraints) return make_ands_implicit(defPartConstraint); } + +/* + * SQL wrapper around get_partition_root_parent(). + */ +Datum +pg_partition_root_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid; + + rootoid = get_partition_root_parent(reloid); + + /* + * If the relation is actually a partition, 'rootoid' has been set to the + * OID of the root table in the partition tree. + */ + if (OidIsValid(rootoid)) + PG_RETURN_OID(rootoid); + + /* + * Otherwise, the table's not a partition. That is, it's either the root + * table in a partition tree or a standalone table that's not part of any + * partition tree. In any case, return the table OID itself as the + * result. + */ + PG_RETURN_OID(reloid); +} + +/* + * SQL wrapper around get_partition_parent(). + */ +Datum +pg_partition_parent(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid parentoid; + + parentoid = get_partition_parent(reloid); + + /* + * If the relation is actually a partition, 'parentoid' has been set to + * the OID of the immediate parent. + */ + if (OidIsValid(parentoid)) + PG_RETURN_OID(parentoid); + + /* Not a partition, return NULL. */ + PG_RETURN_NULL(); +} + +/* + * Returns an integer representing the level a given partition is at in the + * partition tree that it's part of. + */ +Datum +pg_partition_level(PG_FUNCTION_ARGS) +{ + Oid reloid = PG_GETARG_OID(0); + Oid rootoid = PG_GETARG_OID(1); + List *ancestors = get_partition_ancestors(reloid); + int level; + + /* If root is specified, find reloid's distance from it. */ + if (OidIsValid(rootoid)) + { + ListCell *lc; + + if (reloid == rootoid) + return 0; + + if (!list_member_oid(ancestors, rootoid)) + return -1; + + level = 0; + foreach(lc, ancestors) + { + level++; + if (lfirst_oid(lc) == rootoid) + break; + } + } + else + /* Distance from the root of the whole tree. */ + level = list_length(ancestors); + + list_free(ancestors); + PG_RETURN_INT32(level); +} + +/* + * Returns OIDs of tables in a partition tree. + */ +Datum +pg_partition_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + bool include_all = PG_GETARG_BOOL(1); + List *partoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (include_all) + partoids = find_all_inheritors(reloid, NoLock, NULL); + else + partoids = find_inheritance_children(reloid, NoLock); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(partoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns OIDs of leaf tables in a partition tree. + */ +Datum +pg_partition_leaf_children(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + Oid reloid = PG_GETARG_OID(0); + List *leafoids; + ListCell **lc; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + leafoids = get_partition_tree_leaf_tables(reloid); + lc = (ListCell **) palloc(sizeof(ListCell *)); + *lc = list_head(leafoids); + + funcctx->user_fctx = (void *) lc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + lc = (ListCell **) funcctx->user_fctx; + + while (*lc != NULL) + { + Oid partoid = lfirst_oid(*lc); + + *lc = lnext(*lc); + SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(partoid)); + } + + SRF_RETURN_DONE(funcctx); +} + +/* + * Returns number of leaf partitions tables in a partition tree + */ +static List * +get_partition_tree_leaf_tables(Oid relid) +{ + List *partitions = find_all_inheritors(relid, NoLock, NULL); + ListCell *lc; + List *result = NIL; + + foreach(lc, partitions) + { + Oid partoid = lfirst_oid(lc); + + if (get_rel_relkind(partoid) != RELKIND_PARTITIONED_TABLE) + result = lappend_oid(result, partoid); + } + + list_free(partitions); + return result; +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..19262c6c4d 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1821,6 +1821,28 @@ get_rel_relkind(Oid relid) } /* + * get_rel_relispartition + * + * Returns the value of pg_class.relispartition for a given relation. + */ +char +get_rel_relispartition(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + bool result; + + tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup = (Form_pg_class) GETSTRUCT(tp); + result = reltup->relispartition; + ReleaseSysCache(tp); + + return result; +} + +/* * get_rel_tablespace * * Returns the pg_tablespace OID associated with a given relation. diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a14651010f..e1d190f81a 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,52 @@ proisstrict => 'f', prorettype => 'bool', proargtypes => 'oid int4 int4 any', proargmodes => '{i,i,i,v}', prosrc => 'satisfies_hash_partition' }, +# function to get the root partition parent +{ oid => '3423', descr => 'oid of the partition root parent', + proname => 'pg_partition_root_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_root_parent' }, + +# function to get the partition parent +{ oid => '3424', descr => 'oid of the partition immediate parent', + proname => 'pg_partition_parent', prorettype => 'regclass', + proargtypes => 'regclass', prosrc => 'pg_partition_parent' }, + +# function to get the level of a partition in the partition tree of given root +{ oid => '3425', descr => 'level of a partition in the partition tree for given root table', + proname => 'pg_partition_level', prorettype => 'int4', + proargtypes => 'regclass regclass', prosrc => 'pg_partition_level' }, + +# function to get the level of a partition in the whole partition tree +{ oid => '3426', descr => 'level of a partition in the partition tree', + proname => 'pg_partition_level', prolang => '14', prorettype => 'int4', + proargtypes => 'regclass', + prosrc => 'select pg_catalog.pg_partition_level($1, \'0\')' }, + +# function to get OIDs of all tables in a given partition tree +{ oid => '3427', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass bool', + proallargtypes => '{regclass,bool,regclass}', + proargmodes => '{i,i,o}', + proargnames => '{relid,include_all,relid}', + prosrc => 'pg_partition_children' } + +# function to get OIDs of immediate +{ oid => '3428', descr => 'get OIDs of tables in a partition tree', + proname => 'pg_partition_children', prolang => '14', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'select pg_catalog.pg_partition_children($1, \'false\')' } + +# function to get OIDs of all tables in a given partition tree +{ oid => '3429', descr => 'get OIDs of leaf partitions in a partition tree', + proname => 'pg_partition_leaf_children', prorettype => 'regclass', + prorows => '100', proretset => 't', proargtypes => 'regclass', + proallargtypes => '{regclass,regclass}', + proargmodes => '{i,o}', + proargnames => '{relid,relid}', + prosrc => 'pg_partition_leaf_children' } + ] diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..d396d17ff1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -126,6 +126,7 @@ extern char *get_rel_name(Oid relid); extern Oid get_rel_namespace(Oid relid); extern Oid get_rel_type_id(Oid relid); extern char get_rel_relkind(Oid relid); +extern char get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out new file mode 100644 index 0000000000..e93069913c --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,204 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; +select pg_partition_parent('ptif_test0') as parent; + parent +----------- + ptif_test +(1 row) + +select pg_partition_parent('ptif_test01') as parent; + parent +------------ + ptif_test0 +(1 row) + +select pg_partition_root_parent('ptif_test01') as root_parent; + root_parent +------------- + ptif_test +(1 row) + +-- pg_partition_level where partition level wrt whole-tree root is returned +select pg_partition_level('ptif_test01') as level; -- 2 + level +------- + 2 +(1 row) + +select pg_partition_level('ptif_test0') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2 + level +------- + 2 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1 + level +------- + 1 +(1 row) + +select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test0') as level; -- 0 + level +------- + 0 +(1 row) + +select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- -1 + level +------- + -1 +(1 row) + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test | 0 | | ptif_test | 0 + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(6 rows) + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test2 | 1 | ptif_test | ptif_test | 16384 + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 + ptif_test11 | 2 | ptif_test1 | ptif_test | 8192 +(3 rows) + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + level | level_size +-------+------------ + 0 | 0 + 1 | 16384 + 2 | 32768 +(3 rows) + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + total_size +------------ + 49152 +(1 row) + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + total_size +------------ + 49152 +(1 row) + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + relname | level | parent | root_parent | size +------------+-------+-----------+-------------+------- + ptif_test0 | 1 | ptif_test | ptif_test | 0 + ptif_test1 | 1 | ptif_test | ptif_test | 0 + ptif_test2 | 1 | ptif_test | ptif_test | 16384 +(3 rows) + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + relname | level | parent | root_parent | size +-------------+-------+------------+-------------+------- + ptif_test01 | 2 | ptif_test0 | ptif_test | 24576 +(1 row) + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + total_size +------------ + 16384 +(1 row) + +-- number of leaf partitions in the tree +select count(*) from pg_partition_leaf_children('ptif_test') as num_tables; + count +------- + 3 +(1 row) + +drop table ptif_test; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 16f979c8d9..6cb820bbc4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate +test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 42632be675..7e374c2daa 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -188,6 +188,7 @@ test: reloptions test: hash_part test: indexing test: partition_aggregate +test: partition_info test: event_trigger test: fast_default test: stats diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql new file mode 100644 index 0000000000..5675e8c526 --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,91 @@ +-- +-- Tests to show partition tree inspection functions +-- +create table ptif_test (a int, b int) partition by range (a); +create table ptif_test0 partition of ptif_test for values from (minvalue) to (0) partition by list (b); +create table ptif_test01 partition of ptif_test0 for values in (1); +create table ptif_test1 partition of ptif_test for values from (0) to (100) partition by list (b); +create table ptif_test11 partition of ptif_test1 for values in (1); +create table ptif_test2 partition of ptif_test for values from (100) to (maxvalue); +insert into ptif_test select i, 1 from generate_series(-500, 500) i; + +select pg_partition_parent('ptif_test0') as parent; +select pg_partition_parent('ptif_test01') as parent; +select pg_partition_root_parent('ptif_test01') as root_parent; + +-- pg_partition_level where partition level wrt whole-tree root is returned +select pg_partition_level('ptif_test01') as level; -- 2 +select pg_partition_level('ptif_test0') as level; -- 1 +select pg_partition_level('ptif_test') as level; -- 0 + +select pg_partition_level('ptif_test01', 'ptif_test') as level; -- 2 +select pg_partition_level('ptif_test0', 'ptif_test') as level; -- 1 +select pg_partition_level('ptif_test', 'ptif_test') as level; -- 0 + +select pg_partition_level('ptif_test01', 'ptif_test0') as level; -- 1 +select pg_partition_level('ptif_test01', 'ptif_test01') as level; -- 0 +select pg_partition_level('ptif_test0', 'ptif_test0') as level; -- 0 +select pg_partition_level('ptif_test0', 'ptif_test01') as level; -- -1 + +-- all tables in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', true) p; + +-- only leaf partitions in the tree +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_leaf_children('ptif_test') p; + +-- total relation size grouped by level +select pg_partition_level(p) as level, + sum(pg_relation_size(p)) as level_size +from pg_partition_children('ptif_test', true) p +group by level order by level; + +-- total relation size of the whole tree +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test', true) p; + +-- total relation size of the leaf partitions; should be same as above +select sum(pg_relation_size(p)) as total_size +from pg_partition_leaf_children('ptif_test') p; + +-- immediate partitions only +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test', false) p; + +-- could also be written as +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test') p; + +-- immedidate partitions of ptif_test0, which is a non-root partitioned table +select p as relname, + pg_partition_level(p) as level, + pg_partition_parent(p) as parent, + pg_partition_root_parent(p) as root_parent, + pg_relation_size(p) as size +from pg_partition_children('ptif_test0') p; + +-- total size of first level partitions +select sum(pg_relation_size(p)) as total_size +from pg_partition_children('ptif_test') p; + +-- number of leaf partitions in the tree +select count(*) from pg_partition_leaf_children('ptif_test') as num_tables; + +drop table ptif_test; -- 2.11.0 --------------24D47EF568EBA525B01BEEC7-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v2a 4/4] Extra tests @ 2025-09-04 16:28 Nikolay Shaplov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Nikolay Shaplov @ 2025-09-04 16:28 UTC (permalink / raw) Add more tests for ternary reloptions in dummy_index_am module --- src/test/modules/dummy_index_am/README | 1 + .../modules/dummy_index_am/dummy_index_am.c | 36 +++++++++++++----- .../dummy_index_am/expected/reloptions.out | 38 +++++++++++++++++-- .../modules/dummy_index_am/sql/reloptions.sql | 16 ++++++++ src/test/regress/expected/reloptions.out | 4 +- src/test/regress/sql/reloptions.sql | 4 +- 6 files changed, 81 insertions(+), 18 deletions(-) diff --git a/src/test/modules/dummy_index_am/README b/src/test/modules/dummy_index_am/README index 61510f02fa..d80aff0db1 100644 --- a/src/test/modules/dummy_index_am/README +++ b/src/test/modules/dummy_index_am/README @@ -6,6 +6,7 @@ access method, whose code is kept a maximum simple. This includes tests for all relation option types: - boolean +- ternary - enum - integer - real diff --git a/src/test/modules/dummy_index_am/dummy_index_am.c b/src/test/modules/dummy_index_am/dummy_index_am.c index 94ef639b6f..bf8446ddc6 100644 --- a/src/test/modules/dummy_index_am/dummy_index_am.c +++ b/src/test/modules/dummy_index_am/dummy_index_am.c @@ -22,7 +22,7 @@ PG_MODULE_MAGIC; /* parse table for fillRelOptions */ -static relopt_parse_elt di_relopt_tab[6]; +static relopt_parse_elt di_relopt_tab[8]; /* Kind of relation options for dummy index */ static relopt_kind di_relopt_kind; @@ -40,6 +40,8 @@ typedef struct DummyIndexOptions int option_int; double option_real; bool option_bool; + ternary option_ternary1; + ternary option_ternary2; DummyAmEnum option_enum; int option_string_val_offset; int option_string_null_offset; @@ -96,23 +98,37 @@ create_reloptions_table(void) di_relopt_tab[2].opttype = RELOPT_TYPE_BOOL; di_relopt_tab[2].offset = offsetof(DummyIndexOptions, option_bool); + add_ternary_reloption(di_relopt_kind, "option_ternary1", + "First ternary option for dummy_index_am", + TERNARY_UNSET, NULL, AccessExclusiveLock); + di_relopt_tab[3].optname = "option_ternary1"; + di_relopt_tab[3].opttype = RELOPT_TYPE_TERNARY; + di_relopt_tab[3].offset = offsetof(DummyIndexOptions, option_ternary1); + + add_ternary_reloption(di_relopt_kind, "option_ternary2", + "Second ternary option for dummy_index_am", + TERNARY_TRUE, "do_not_know_yet", AccessExclusiveLock); + di_relopt_tab[4].optname = "option_ternary2"; + di_relopt_tab[4].opttype = RELOPT_TYPE_TERNARY; + di_relopt_tab[4].offset = offsetof(DummyIndexOptions, option_ternary2); + add_enum_reloption(di_relopt_kind, "option_enum", "Enum option for dummy_index_am", dummyAmEnumValues, DUMMY_AM_ENUM_ONE, "Valid values are \"one\" and \"two\".", AccessExclusiveLock); - di_relopt_tab[3].optname = "option_enum"; - di_relopt_tab[3].opttype = RELOPT_TYPE_ENUM; - di_relopt_tab[3].offset = offsetof(DummyIndexOptions, option_enum); + di_relopt_tab[5].optname = "option_enum"; + di_relopt_tab[5].opttype = RELOPT_TYPE_ENUM; + di_relopt_tab[5].offset = offsetof(DummyIndexOptions, option_enum); add_string_reloption(di_relopt_kind, "option_string_val", "String option for dummy_index_am with non-NULL default", "DefaultValue", &validate_string_option, AccessExclusiveLock); - di_relopt_tab[4].optname = "option_string_val"; - di_relopt_tab[4].opttype = RELOPT_TYPE_STRING; - di_relopt_tab[4].offset = offsetof(DummyIndexOptions, + di_relopt_tab[6].optname = "option_string_val"; + di_relopt_tab[6].opttype = RELOPT_TYPE_STRING; + di_relopt_tab[6].offset = offsetof(DummyIndexOptions, option_string_val_offset); /* @@ -123,9 +139,9 @@ create_reloptions_table(void) NULL, /* description */ NULL, &validate_string_option, AccessExclusiveLock); - di_relopt_tab[5].optname = "option_string_null"; - di_relopt_tab[5].opttype = RELOPT_TYPE_STRING; - di_relopt_tab[5].offset = offsetof(DummyIndexOptions, + di_relopt_tab[7].optname = "option_string_null"; + di_relopt_tab[7].opttype = RELOPT_TYPE_STRING; + di_relopt_tab[7].offset = offsetof(DummyIndexOptions, option_string_null_offset); } diff --git a/src/test/modules/dummy_index_am/expected/reloptions.out b/src/test/modules/dummy_index_am/expected/reloptions.out index c873a80bb7..ad1b2ea639 100644 --- a/src/test/modules/dummy_index_am/expected/reloptions.out +++ b/src/test/modules/dummy_index_am/expected/reloptions.out @@ -18,6 +18,8 @@ SET client_min_messages TO 'notice'; CREATE INDEX dummy_test_idx ON dummy_test_tab USING dummy_index_am (i) WITH ( option_bool = false, + option_ternary1, + option_ternary2 = off, option_int = 5, option_real = 3.1, option_enum = 'two', @@ -31,16 +33,20 @@ SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; unnest ------------------------ option_bool=false + option_ternary1=true + option_ternary2=off option_int=5 option_real=3.1 option_enum=two option_string_val=null option_string_null=val -(6 rows) +(8 rows) -- ALTER INDEX .. SET ALTER INDEX dummy_test_idx SET (option_int = 10); ALTER INDEX dummy_test_idx SET (option_bool = true); +ALTER INDEX dummy_test_idx SET (option_ternary1 = false); +ALTER INDEX dummy_test_idx SET (option_ternary2 = Do_Not_Know_YET); ALTER INDEX dummy_test_idx SET (option_real = 3.2); ALTER INDEX dummy_test_idx SET (option_string_val = 'val2'); ALTER INDEX dummy_test_idx SET (option_string_null = NULL); @@ -49,19 +55,23 @@ ALTER INDEX dummy_test_idx SET (option_enum = 'three'); ERROR: invalid value for enum option "option_enum": three DETAIL: Valid values are "one" and "two". SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; - unnest -------------------------- + unnest +--------------------------------- option_int=10 option_bool=true + option_ternary1=false + option_ternary2=do_not_know_yet option_real=3.2 option_string_val=val2 option_string_null=null option_enum=one -(6 rows) +(8 rows) -- ALTER INDEX .. RESET ALTER INDEX dummy_test_idx RESET (option_int); ALTER INDEX dummy_test_idx RESET (option_bool); +ALTER INDEX dummy_test_idx RESET (option_ternary1); +ALTER INDEX dummy_test_idx RESET (option_ternary2); ALTER INDEX dummy_test_idx RESET (option_real); ALTER INDEX dummy_test_idx RESET (option_enum); ALTER INDEX dummy_test_idx RESET (option_string_val); @@ -100,6 +110,26 @@ SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; (1 row) ALTER INDEX dummy_test_idx RESET (option_bool); +-- Ternary +ALTER INDEX dummy_test_idx SET (option_ternary1 = 4); -- error +ERROR: invalid value for ternary option "option_ternary1": 4 +ALTER INDEX dummy_test_idx SET (option_ternary1 = 1); -- ok, as true +ALTER INDEX dummy_test_idx SET (option_ternary1 = 3.4); -- error +ERROR: invalid value for ternary option "option_ternary1": 3.4 +ALTER INDEX dummy_test_idx SET (option_ternary1 = 'val4'); -- error +ERROR: invalid value for ternary option "option_ternary1": val4 +ALTER INDEX dummy_test_idx SET (option_ternary1 = 'do_not_know_yet'); -- error. Valid for ternary2 not for ternary1 +ERROR: invalid value for ternary option "option_ternary1": do_not_know_yet +ALTER INDEX dummy_test_idx SET (option_ternary2 = 'do_not_know_yet'); -- ok +SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; + unnest +--------------------------------- + option_ternary1=1 + option_ternary2=do_not_know_yet +(2 rows) + +ALTER INDEX dummy_test_idx RESET (option_ternary1); +ALTER INDEX dummy_test_idx RESET (option_ternary2); -- Float ALTER INDEX dummy_test_idx SET (option_real = 4); -- ok ALTER INDEX dummy_test_idx SET (option_real = true); -- error diff --git a/src/test/modules/dummy_index_am/sql/reloptions.sql b/src/test/modules/dummy_index_am/sql/reloptions.sql index 6749d763e6..123540905a 100644 --- a/src/test/modules/dummy_index_am/sql/reloptions.sql +++ b/src/test/modules/dummy_index_am/sql/reloptions.sql @@ -18,6 +18,8 @@ SET client_min_messages TO 'notice'; CREATE INDEX dummy_test_idx ON dummy_test_tab USING dummy_index_am (i) WITH ( option_bool = false, + option_ternary1, + option_ternary2 = off, option_int = 5, option_real = 3.1, option_enum = 'two', @@ -30,6 +32,8 @@ SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; -- ALTER INDEX .. SET ALTER INDEX dummy_test_idx SET (option_int = 10); ALTER INDEX dummy_test_idx SET (option_bool = true); +ALTER INDEX dummy_test_idx SET (option_ternary1 = false); +ALTER INDEX dummy_test_idx SET (option_ternary2 = Do_Not_Know_YET); ALTER INDEX dummy_test_idx SET (option_real = 3.2); ALTER INDEX dummy_test_idx SET (option_string_val = 'val2'); ALTER INDEX dummy_test_idx SET (option_string_null = NULL); @@ -40,6 +44,8 @@ SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; -- ALTER INDEX .. RESET ALTER INDEX dummy_test_idx RESET (option_int); ALTER INDEX dummy_test_idx RESET (option_bool); +ALTER INDEX dummy_test_idx RESET (option_ternary1); +ALTER INDEX dummy_test_idx RESET (option_ternary2); ALTER INDEX dummy_test_idx RESET (option_real); ALTER INDEX dummy_test_idx RESET (option_enum); ALTER INDEX dummy_test_idx RESET (option_string_val); @@ -60,6 +66,16 @@ ALTER INDEX dummy_test_idx SET (option_bool = 3.4); -- error ALTER INDEX dummy_test_idx SET (option_bool = 'val4'); -- error SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; ALTER INDEX dummy_test_idx RESET (option_bool); +-- Ternary +ALTER INDEX dummy_test_idx SET (option_ternary1 = 4); -- error +ALTER INDEX dummy_test_idx SET (option_ternary1 = 1); -- ok, as true +ALTER INDEX dummy_test_idx SET (option_ternary1 = 3.4); -- error +ALTER INDEX dummy_test_idx SET (option_ternary1 = 'val4'); -- error +ALTER INDEX dummy_test_idx SET (option_ternary1 = 'do_not_know_yet'); -- error. Valid for ternary2 not for ternary1 +ALTER INDEX dummy_test_idx SET (option_ternary2 = 'do_not_know_yet'); -- ok +SELECT unnest(reloptions) FROM pg_class WHERE relname = 'dummy_test_idx'; +ALTER INDEX dummy_test_idx RESET (option_ternary1); +ALTER INDEX dummy_test_idx RESET (option_ternary2); -- Float ALTER INDEX dummy_test_idx SET (option_real = 4); -- ok ALTER INDEX dummy_test_idx SET (option_real = true); -- error diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out index 1c99f79ab0..6e65cd5c3d 100644 --- a/src/test/regress/expected/reloptions.out +++ b/src/test/regress/expected/reloptions.out @@ -98,7 +98,7 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; {fillfactor=13,autovacuum_enabled=false} (1 row) --- Tests for future (FIXME) ternary options +-- Tests for ternary options -- behave as boolean option: accept unassigned name and truncated value DROP TABLE reloptions_test; CREATE TABLE reloptions_test(i INT) WITH (vacuum_truncate); @@ -116,7 +116,7 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; {vacuum_truncate=fals} (1 row) --- preferred "true" alias is used when storing +-- preferred "true" alias is stored in pg_class DROP TABLE reloptions_test; CREATE TABLE reloptions_test(i INT) WITH (vacuum_index_cleanup=on); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql index f5980dafcb..c99673db9e 100644 --- a/src/test/regress/sql/reloptions.sql +++ b/src/test/regress/sql/reloptions.sql @@ -59,7 +59,7 @@ UPDATE pg_class ALTER TABLE reloptions_test RESET (illegal_option); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; --- Tests for future (FIXME) ternary options +-- Tests for ternary options -- behave as boolean option: accept unassigned name and truncated value DROP TABLE reloptions_test; @@ -70,7 +70,7 @@ DROP TABLE reloptions_test; CREATE TABLE reloptions_test(i INT) WITH (vacuum_truncate=FaLS); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; --- preferred "true" alias is used when storing +-- preferred "true" alias is stored in pg_class DROP TABLE reloptions_test; CREATE TABLE reloptions_test(i INT) WITH (vacuum_index_cleanup=on); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; -- 2.39.2 --nextPart4155600.3ZeAukHxDK-- --nextPart4961315.687JKscXgg Content-Type: application/pgp-signature; name="signature.asc" Content-Description: This is a digitally signed message part. Content-Transfer-Encoding: 7Bit -----BEGIN PGP SIGNATURE----- iQEzBAABCgAdFiEE+sk3ebqQKlezKOi8PMbfuIHAGpgFAmjGgZ4ACgkQPMbfuIHA GpgHFgf/dwjUCCKw4wFDpFssAUVnClknwo+DYhgbni6DfBlLM8bjB0CWwZsNGUEv dGueFJY9q4+3YPon9sqb1KIqBZDtLaGj69U+K+ZtLkjOc8WV4VOmUyZuuSaZh9lw JFM+d8RZNTemYQ5Nc82YuF+4MDiiM3480vhAr412LMRd/6P4dk+vKDL2kopodhB+ xbZYtARolC/bZaMTla68L/4X5qidfsd4zd+QZjf8JXwv1mq2MZCuHvsuMMViWp3Z Hf7r4bj407BjEYH/UNpE70iWa65xDCHpRSOYJ6QKFxRnAEYBim6u3G7t85jnHz7O mVGvRJLeVMgh2l6TCxG7fSJGwh/Qrg== =Agrf -----END PGP SIGNATURE----- --nextPart4961315.687JKscXgg-- ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-09-04 16:28 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-01-16 10:02 [PATCH v3] Add assorted partition reporting functions amit <[email protected]> 2018-01-16 10:02 [PATCH] Add assorted partition reporting functions amit <[email protected]> 2018-01-16 10:02 [PATCH v6] Add assorted partition reporting functions amit <[email protected]> 2018-01-16 10:02 [PATCH v8] Add assorted partition reporting functions amit <[email protected]> 2018-01-16 10:02 [PATCH v2] Add assorted partition reporting functions amit <[email protected]> 2018-01-16 10:02 [PATCH v5] Add assorted partition reporting functions amit <[email protected]> 2018-01-16 10:02 [PATCH v4] Add assorted partition reporting functions amit <[email protected]> 2018-01-16 10:02 [PATCH v7] Add assorted partition reporting functions amit <[email protected]> 2025-09-04 16:28 [PATCH v2a 4/4] Extra tests Nikolay Shaplov <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox