On 2019-Feb-27, Michael Paquier wrote:
> On Tue, Feb 26, 2019 at 07:27:57PM -0300, Alvaro Herrera wrote:
> > Thanks for committing pg_partition_root ... but it turns out to be
> > useless for this purpose.
>
> Well, what's done is done. The thing is useful by itself in my
> opinion.
Eh, of course -- note that the psql query I added does use
pg_partition_root, it's just that it is not useful *all by itself*.
> In the second patch, pg_partition_ancestors always sets include_self
> to true. What's the use case you have in mind to set it to false? In
> the other existing functions we always include the argument itself, so
> we may want to keep things consistent.
Hmm, true.
> I think that you should make the function return a record of regclass
> elements instead of OIDs to be consistent. This could save casts for
> the callers.
Yeah, done.
> Adding the self-member at the beginning of the record set is more
> consistent with the order of the results returned by
> get_partition_ancestors().
You're right, done.
> It would be nice to add some tests in partition_info.sql for tables
> and indexes (both work).
Well. I tried this scenario
create table t1 (a int);
create table t11 () inherits (t1);
create table t2 (b int);
create table t111() inherits (t1, t2);
and the result I get from my new function is not good:
alvherre=# select * from pg_partition_ancestors('t111');
relid
-------
t111
t1
(2 filas)
it should have listed t2 too, but it doesn't. Since these functions
aren't supposed to work on legacy inheritance anyway, I think the right
action is to return the empty set. In the current version I just do
what pg_partition_tree does, but I think we should adjust that behavior.
I'll start a new thread about that.
> For the meaning of using pg_partition_ancestors, I see... Not only do
> you want to show the foreign keys defined in the top-most parent, but
> also these defined in intermediate layers. That makes sense. Using
> only pg_partition_root would have been enough to show FKs in the
> top-most parent, but the intermediate ones would be missed (using only
> pg_partition_root() would miss the FKs fk_partitioned_fk_5_a_fkey1 and
> fk_partitioned_fk_5_a_fkey when doing "\d fk_partitioned_fk_5_1" based
> on the test set).
Exactly -- that's the whole point. We need to list all FKs that are
applicable to the partition, indicating which relation is the one where
the FK generates, and without polluting the output with countless
"internal" pg_constraint rows. The output psql presents for the PK-side
relation when it's partitioned, with my patch to support that, is quite
ugly when there are many partitions.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 9e5cc595ea952763fdcd879ec19f3cbff82edae6 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Tue, 26 Feb 2019 17:05:24 -0300
Subject: [PATCH v4] pg_partition_ancestors
---
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
