agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Alvaro Herrera <[email protected]>
Subject: [PATCH v4] pg_partition_ancestors
Date: Tue, 26 Feb 2019 17:05:24 -0300
---
doc/src/sgml/func.sgml | 10 +++
src/backend/utils/adt/partitionfuncs.c | 49 +++++++++++++
src/include/catalog/pg_proc.dat | 5 ++
src/test/regress/expected/partition_info.out | 72 +++++++++++++++++++-
src/test/regress/sql/partition_info.sql | 18 ++++-
5 files changed, 151 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 86ff4e5c9e2..ee3962a6c92 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20274,6 +20274,16 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
their partitions, and so on.
</entry>
</row>
+ <row>
+ <entry>
+ <indexterm><primary>pg_partition_ancestors</primary></indexterm>
+ <literal><function>pg_partition_ancestors(<type>regclass</type>)</function></literal>
+ </entry>
+ <entry><type>setof regclass</type></entry>
+ <entry>
+ List the ancestor relations of the given partition.
+ </entry>
+ </row>
<row>
<entry>
<indexterm><primary>pg_partition_root</primary></indexterm>
diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt/partitionfuncs.c
index ffd66b64394..2e7bf01106b 100644
--- a/src/backend/utils/adt/partitionfuncs.c
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -208,3 +208,52 @@ pg_partition_root(PG_FUNCTION_ARGS)
Assert(OidIsValid(rootrelid));
PG_RETURN_OID(rootrelid);
}
+
+/*
+ * pg_partition_ancestors
+ *
+ * Produces a view with one row per ancestor of the given partition,
+ * including the input relation itself.
+ */
+Datum
+pg_partition_ancestors(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ FuncCallContext *funcctx;
+ ListCell **next;
+
+ if (!check_rel_can_be_partition(relid))
+ PG_RETURN_NULL();
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcxt;
+ List *ancestors;
+
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ oldcxt = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ ancestors = get_partition_ancestors(relid);
+ ancestors = lcons_oid(relid, ancestors);
+
+ next = (ListCell **) palloc(sizeof(ListCell *));
+ *next = list_head(ancestors);
+ funcctx->user_fctx = (void *) next;
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ next = (ListCell **) funcctx->user_fctx;
+
+ if (*next != NULL)
+ {
+ Oid relid = lfirst_oid(*next);
+
+ *next = lnext(*next);
+ SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(relid));
+ }
+
+ SRF_RETURN_DONE(funcctx);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a4e173b4846..5bb56b2c639 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10528,6 +10528,11 @@
proargmodes => '{i,o,o,o,o}',
proargnames => '{rootrelid,relid,parentrelid,isleaf,level}',
prosrc => 'pg_partition_tree' },
+{ oid => '3425', descr => 'view ancestors of the partition',
+ proname => 'pg_partition_ancestors', prorows => '10', proretset => 't',
+ provolatile => 'v', prorettype => 'regclass', proargtypes => 'regclass',
+ proallargtypes => '{regclass,regclass}', proargmodes => '{i,o}',
+ proargnames => '{partitionid,relid}', prosrc => 'pg_partition_ancestors' },
# function to get the top-most partition root parent
{ oid => '3424', descr => 'get top-most partition root parent',
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
index a884df976fd..48d92024485 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -78,6 +78,21 @@ SELECT relid, parentrelid, level, isleaf
ptif_test01 | ptif_test0 | 0 | t
(1 row)
+-- List all ancestors of root and leaf tables
+SELECT * FROM pg_partition_ancestors('ptif_test01');
+ relid
+-------------
+ ptif_test01
+ ptif_test0
+ ptif_test
+(3 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_test');
+ relid
+-----------
+ ptif_test
+(1 row)
+
-- List all members using pg_partition_root with leaf table reference
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
@@ -138,6 +153,21 @@ SELECT relid, parentrelid, level, isleaf
ptif_test11_index | ptif_test1_index | 2 | t
(6 rows)
+-- List all ancestors of root and leaf indexes
+SELECT * FROM pg_partition_ancestors('ptif_test01_index');
+ relid
+-------------------
+ ptif_test01_index
+ ptif_test0_index
+ ptif_test_index
+(3 rows)
+
+SELECT * FROM pg_partition_ancestors('ptif_test_index');
+ relid
+-----------------
+ ptif_test_index
+(1 row)
+
DROP TABLE ptif_test;
-- Table that is not part of any partition tree is the only member listed.
CREATE TABLE ptif_normal_table(a int);
@@ -148,6 +178,12 @@ SELECT relid, parentrelid, level, isleaf
ptif_normal_table | | 0 | t
(1 row)
+SELECT * FROM pg_partition_ancestors('ptif_normal_table');
+ relid
+-------------------
+ ptif_normal_table
+(1 row)
+
SELECT pg_partition_root('ptif_normal_table');
pg_partition_root
-------------------
@@ -157,9 +193,11 @@ SELECT pg_partition_root('ptif_normal_table');
DROP TABLE ptif_normal_table;
-- Various partitioning-related functions return NULL if passed relations
-- of types that cannot be part of a partition tree; for example, views,
--- materialized views, etc.
+-- materialized views, legacy inheritance children, etc.
CREATE VIEW ptif_test_view AS SELECT 1;
CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
+CREATE TABLE ptif_li_parent ();
+CREATE TABLE ptif_li_child () INHERITS (ptif_li_parent);
SELECT * FROM pg_partition_tree('ptif_test_view');
relid | parentrelid | isleaf | level
-------+-------------+--------+-------
@@ -172,6 +210,31 @@ SELECT * FROM pg_partition_tree('ptif_test_matview');
| | |
(1 row)
+SELECT * FROM pg_partition_tree('ptif_li_child');
+ relid | parentrelid | isleaf | level
+---------------+----------------+--------+-------
+ ptif_li_child | ptif_li_parent | t | 0
+(1 row)
+
+SELECT * FROM pg_partition_ancestors('ptif_test_view');
+ relid
+-------
+
+(1 row)
+
+SELECT * FROM pg_partition_ancestors('ptif_test_matview');
+ relid
+-------
+
+(1 row)
+
+SELECT * FROM pg_partition_ancestors('ptif_li_child');
+ relid
+----------------
+ ptif_li_child
+ ptif_li_parent
+(2 rows)
+
SELECT pg_partition_root('ptif_test_view');
pg_partition_root
-------------------
@@ -184,5 +247,12 @@ SELECT pg_partition_root('ptif_test_matview');
(1 row)
+SELECT pg_partition_root('ptif_li_child');
+ pg_partition_root
+-------------------
+ ptif_li_child
+(1 row)
+
DROP VIEW ptif_test_view;
DROP MATERIALIZED VIEW ptif_test_matview;
+DROP TABLE ptif_li_parent, ptif_li_child;
diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql
index 119b90afe45..d5b57372dcf 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -41,6 +41,9 @@ SELECT relid, parentrelid, level, isleaf
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_test01') p
JOIN pg_class c ON (p.relid = c.oid);
+-- List all ancestors of root and leaf tables
+SELECT * FROM pg_partition_ancestors('ptif_test01');
+SELECT * FROM pg_partition_ancestors('ptif_test');
-- List all members using pg_partition_root with leaf table reference
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree(pg_partition_root('ptif_test01')) p
@@ -61,24 +64,35 @@ SELECT relid, parentrelid, level, isleaf
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree(pg_partition_root('ptif_test01_index')) p
JOIN pg_class c ON (p.relid = c.oid);
-
+-- List all ancestors of root and leaf indexes
+SELECT * FROM pg_partition_ancestors('ptif_test01_index');
+SELECT * FROM pg_partition_ancestors('ptif_test_index');
DROP TABLE ptif_test;
-- Table that is not part of any partition tree is the only member listed.
CREATE TABLE ptif_normal_table(a int);
SELECT relid, parentrelid, level, isleaf
FROM pg_partition_tree('ptif_normal_table');
+SELECT * FROM pg_partition_ancestors('ptif_normal_table');
SELECT pg_partition_root('ptif_normal_table');
DROP TABLE ptif_normal_table;
-- Various partitioning-related functions return NULL if passed relations
-- of types that cannot be part of a partition tree; for example, views,
--- materialized views, etc.
+-- materialized views, legacy inheritance children, etc.
CREATE VIEW ptif_test_view AS SELECT 1;
CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1;
+CREATE TABLE ptif_li_parent ();
+CREATE TABLE ptif_li_child () INHERITS (ptif_li_parent);
SELECT * FROM pg_partition_tree('ptif_test_view');
SELECT * FROM pg_partition_tree('ptif_test_matview');
+SELECT * FROM pg_partition_tree('ptif_li_child');
+SELECT * FROM pg_partition_ancestors('ptif_test_view');
+SELECT * FROM pg_partition_ancestors('ptif_test_matview');
+SELECT * FROM pg_partition_ancestors('ptif_li_child');
SELECT pg_partition_root('ptif_test_view');
SELECT pg_partition_root('ptif_test_matview');
+SELECT pg_partition_root('ptif_li_child');
DROP VIEW ptif_test_view;
DROP MATERIALIZED VIEW ptif_test_matview;
+DROP TABLE ptif_li_parent, ptif_li_child;
--
2.17.1
--wac7ysb48OaltWcw--
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v4] pg_partition_ancestors
In-Reply-To: <no-message-id-1883546@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox