Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gV584-0004fJ-SG for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Dec 2018 01:40:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gV582-0000AY-Us for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Dec 2018 01:40:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gV581-0000AP-9E for pgsql-hackers@lists.postgresql.org; Fri, 07 Dec 2018 01:40:30 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gV57u-00028K-Qc for pgsql-hackers@postgresql.org; Fri, 07 Dec 2018 01:40:28 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id C7A2621B4F; Thu, 6 Dec 2018 20:40:21 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Thu, 06 Dec 2018 20:40:21 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:mime-version:content-type; s= fm3; bh=buD43uhIQBN7lFFkrwP2Bi5YJIvKH56f+zV6KW+GPYc=; b=gdFk4Li5 iKDKHT8HJ3vRmH/Fyktgy5Hr8QYuKo1KX4JqNRgeSey6LZF0//q1fUPCugbEIOGD fl3on3fM6jTIelkWL4Bj7bbhcUSDHqXd1U0nGbMhXxlg5bBG9QIuOG3QMGSrPwHS SPStx8vLcWhmApP0FQAxgIotgtIkS/NZmRWKS4MAWNVis8O3rNUg10WyxodmhydW jWlG9L2C/4o9qQYk8yoKlw2rBzmR/i3griPYMzfWePX5QkgJZpifM67zARSgyNV5 xIke6QcTDTt4GaGgz1AlWUJC7Q7fBEfSudhAT3UlNmS7BhOV3eJR82hQCOWnf//3 HxkT+NnVCk16Aw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:message-id :mime-version:subject:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; bh=buD43uhIQBN7lFFkrwP2Bi5YJIvKH 56f+zV6KW+GPYc=; b=Kglj0EKDuI7rsaV4U4D0GAh71s2dZQyy0kNiSBsUz9/SZ xIvCtLsFQfBcunXI96k0YrqR2bcGeJLWNgyV3+KS03sY4pzkFyACisWVg1PTf7u0 SmjJNDCuccOobFRoKG6cTqiE0t2e1aZ6WlefLTTqZjsE2AOaC8KaovtVSDZBrLk2 R6EgiD/GdZLcUjoe1isj2EfcBFzYQYqAOIYirkkRU2yt+9GRv6zIUgmjdFfMOGJn FUQDXb9c5V0IEaaaPHf9n9xxqpV/wMvLgM04VhZ3ojfKFYw5IVBB9tE8Up6my8HI g0+X51OTuMYhm6y88kshlovKAsG+ebShbmHojmYQg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedtkedrudefkedgfeekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfquhhtnecuuegrihhlohhuthemucef tddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfghrlhcuvffnffculddvfe dmnecujfgurhepfffhvffukfggtggusehgtderredttddunecuhfhrohhmpefoihgthhgr vghlucfrrghquhhivghruceomhhitghhrggvlhesphgrqhhuihgvrhdrgiihiieqnecuff homhgrihhnpehpohhsthhgrhdrvghspdhpohhsthhgrhgvshhqlhdrohhrghenucfkphep iedurddvudehrddufeejrdduiedvnecurfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthh grvghlsehprghquhhivghrrdighiiinecuvehluhhsthgvrhfuihiivgeptd X-ME-Proxy: Received: from paquier.xyz (c137162.net61215.cablenet.ne.jp [61.215.137.162]) by mail.messagingengine.com (Postfix) with ESMTPA id BCAFE102A0; Thu, 6 Dec 2018 20:40:18 -0500 (EST) Date: Fri, 7 Dec 2018 10:40:15 +0900 From: Michael Paquier To: Postgres hackers Cc: Amit Langote , Alvaro Herrera Subject: Add pg_partition_root to get top-most parent of a partition tree Message-ID: <20181207014015.GP2407@paquier.xyz> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="f0Ums9VvOMUG7syy" Content-Disposition: inline List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --f0Ums9VvOMUG7syy Content-Type: multipart/mixed; boundary="3gk1bTGVZuaU9V5/" Content-Disposition: inline --3gk1bTGVZuaU9V5/ Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Hi all, =C1lvaro has given faced a use case where it would be useful to have a function which is able to return the top-most parent of a partition tree: https://postgr.es/m/20181204184159.eue3wlchqrkh4vsc@alvherre.pgsql This has been mentioned as well on the thread where was discussed pg_partition_tree, but it got shaved from the committed patch as many things happened when discussing the thing. Attached is a patch to do the work, which includes documentation and tests. An argument could be made to include the top-most parent as part of pg_partition_tree, but it feels more natural to me to have a separate function. This makes sure to handle invalid relations by returning NULL, and it generates an error for incorrect relkind. I have included as well a fix for the recent crash on pg_partition_tree I have reported, but let's discuss the crash on its thread here: https://www.postgresql.org/message-id/20181207010406.GO2407@paquier.xyz The bug fix would most likely get committed first, and I'll rebase this patch as need be. I am adding this patch to the CF of January. I think that Amit should also be marked as a co-author of this patch, as that's inspired from what has been submitted previously, still I have no reused the code. Thanks, -- Michael --3gk1bTGVZuaU9V5/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="partition-root-v1.patch" Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b3336ea9be..dbec132188 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20270,6 +20270,17 @@ postgres=3D# SELECT * FROM pg_walfile_name_offset(= pg_stop_backup()); their partitions, and so on. + + + pg_partition_root + pg_partition_root(regclass) + + regclass + + Return the top-most parent of a partition tree for the given + partitioned table or partitioned index. + + diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt= /partitionfuncs.c index 78dd2b542b..611a33d0e1 100644 --- a/src/backend/utils/adt/partitionfuncs.c +++ b/src/backend/utils/adt/partitionfuncs.c @@ -23,7 +23,38 @@ #include "funcapi.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" +#include "utils/syscache.h" =20 +/* + * Perform several checks on a relation on which is extracted some + * information related to its partition tree. Returns false if the + * relation cannot be processed, in which case it is up to the caller + * to decide what to do instead of an error. + */ +static bool +check_rel_for_partition_info(Oid relid) +{ + char relkind; + + /* Check if relation exists */ + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(relid))) + return false; + + relkind =3D get_rel_relkind(relid); + + /* Only allow relation types that can appear in partition trees. */ + if (relkind !=3D RELKIND_RELATION && + relkind !=3D RELKIND_FOREIGN_TABLE && + relkind !=3D RELKIND_INDEX && + relkind !=3D RELKIND_PARTITIONED_TABLE && + relkind !=3D RELKIND_PARTITIONED_INDEX) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table, a foreign table, or an index", + get_rel_name(relid)))); + + return true; +} =20 /* * pg_partition_tree @@ -38,20 +69,11 @@ pg_partition_tree(PG_FUNCTION_ARGS) { #define PG_PARTITION_TREE_COLS 4 Oid rootrelid =3D PG_GETARG_OID(0); - char relkind =3D get_rel_relkind(rootrelid); FuncCallContext *funcctx; ListCell **next; =20 - /* Only allow relation types that can appear in partition trees. */ - if (relkind !=3D RELKIND_RELATION && - relkind !=3D RELKIND_FOREIGN_TABLE && - relkind !=3D RELKIND_INDEX && - relkind !=3D RELKIND_PARTITIONED_TABLE && - relkind !=3D RELKIND_PARTITIONED_INDEX) - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table, a foreign table, or an index", - get_rel_name(rootrelid)))); + if (!check_rel_for_partition_info(rootrelid)) + PG_RETURN_NULL(); =20 /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) @@ -152,3 +174,39 @@ pg_partition_tree(PG_FUNCTION_ARGS) /* done when there are no more elements left */ SRF_RETURN_DONE(funcctx); } + +/* + * pg_partition_root + * + * For the given relation part of a partition tree, return its top-most + * root parent. + */ +Datum +pg_partition_root(PG_FUNCTION_ARGS) +{ + Oid relid =3D PG_GETARG_OID(0); + Oid rootrelid; + List *ancestors; + + if (!check_rel_for_partition_info(relid)) + PG_RETURN_NULL(); + + /* + * If the relation is not a partition, return itself as a result. + */ + if (!get_rel_relispartition(relid)) + PG_RETURN_OID(relid); + + /* Fetch the top-most parent */ + ancestors =3D get_partition_ancestors(relid); + rootrelid =3D llast_oid(ancestors); + list_free(ancestors); + + /* + * If the relation is actually a partition, 'rootrelid' has been set to + * the OID of the root table in the partition tree. It should be a valid + * valid per the previous check for partition leaf above. + */ + Assert(OidIsValid(rootrelid)); + PG_RETURN_OID(rootrelid); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.= dat index 034a41eb55..6817a027c2 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10048,4 +10048,9 @@ proargnames =3D> '{rootrelid,relid,parentrelid,isleaf,level}', prosrc =3D> 'pg_partition_tree' }, =20 +# function to get the top-most partition root parent +{ oid =3D> '3424', descr =3D> 'get top-most partition root parent', + proname =3D> 'pg_partition_root', prorettype =3D> 'regclass', + proargtypes =3D> 'regclass', prosrc =3D> 'pg_partition_root' }, + ] diff --git a/src/test/regress/expected/partition_info.out b/src/test/regres= s/expected/partition_info.out index 6b116125e6..cee741aefe 100644 --- a/src/test/regress/expected/partition_info.out +++ b/src/test/regress/expected/partition_info.out @@ -6,6 +6,24 @@ SELECT * FROM pg_partition_tree(NULL); -------+-------------+--------+------- (0 rows) =20 +SELECT * FROM pg_partition_tree(0); + relid | parentrelid | isleaf | level=20 +-------+-------------+--------+------- + | | | =20 +(1 row) + +SELECT pg_partition_root(NULL); + pg_partition_root=20 +------------------- +=20 +(1 row) + +SELECT pg_partition_root(0); + pg_partition_root=20 +------------------- +=20 +(1 row) + -- Test table partition trees CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a); CREATE TABLE ptif_test0 PARTITION OF ptif_test @@ -29,67 +47,67 @@ ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test= 11_index; CREATE INDEX ptif_test2_index ON ptif_test2 (a); ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index; -- List all tables members of the tree -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test'); - relid | parentrelid | level | isleaf=20 --------------+-------------+-------+-------- - ptif_test | | 0 | f - ptif_test0 | ptif_test | 1 | f - ptif_test1 | ptif_test | 1 | f - ptif_test2 | ptif_test | 1 | t - ptif_test01 | ptif_test0 | 2 | t - ptif_test11 | ptif_test1 | 2 | t + relid | parentrelid | level | isleaf | pg_partition_root=20 +-------------+-------------+-------+--------+------------------- + ptif_test | | 0 | f | ptif_test + ptif_test0 | ptif_test | 1 | f | ptif_test + ptif_test1 | ptif_test | 1 | f | ptif_test + ptif_test2 | ptif_test | 1 | t | ptif_test + ptif_test01 | ptif_test0 | 2 | t | ptif_test + ptif_test11 | ptif_test1 | 2 | t | ptif_test (6 rows) =20 -- List tables from an intermediate level -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test0') p JOIN pg_class c ON (p.relid =3D c.oid); - relid | parentrelid | level | isleaf=20 --------------+-------------+-------+-------- - ptif_test0 | ptif_test | 0 | f - ptif_test01 | ptif_test0 | 1 | t + relid | parentrelid | level | isleaf | pg_partition_root=20 +-------------+-------------+-------+--------+------------------- + ptif_test0 | ptif_test | 0 | f | ptif_test + ptif_test01 | ptif_test0 | 1 | t | ptif_test (2 rows) =20 -- List from leaf table -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test01') p JOIN pg_class c ON (p.relid =3D c.oid); - relid | parentrelid | level | isleaf=20 --------------+-------------+-------+-------- - ptif_test01 | ptif_test0 | 0 | t + relid | parentrelid | level | isleaf | pg_partition_root=20 +-------------+-------------+-------+--------+------------------- + ptif_test01 | ptif_test0 | 0 | t | ptif_test (1 row) =20 -- List all indexes members of the tree -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test_index'); - relid | parentrelid | level | isleaf=20 --------------------+------------------+-------+-------- - ptif_test_index | | 0 | f - ptif_test0_index | ptif_test_index | 1 | f - ptif_test1_index | ptif_test_index | 1 | f - ptif_test2_index | ptif_test_index | 1 | t - ptif_test01_index | ptif_test0_index | 2 | t - ptif_test11_index | ptif_test1_index | 2 | t + relid | parentrelid | level | isleaf | pg_partition_root= =20 +-------------------+------------------+-------+--------+------------------- + ptif_test_index | | 0 | f | ptif_test_index + ptif_test0_index | ptif_test_index | 1 | f | ptif_test_index + ptif_test1_index | ptif_test_index | 1 | f | ptif_test_index + ptif_test2_index | ptif_test_index | 1 | t | ptif_test_index + ptif_test01_index | ptif_test0_index | 2 | t | ptif_test_index + ptif_test11_index | ptif_test1_index | 2 | t | ptif_test_index (6 rows) =20 -- List indexes from an intermediate level -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test0_index') p JOIN pg_class c ON (p.relid =3D c.oid); - relid | parentrelid | level | isleaf=20 --------------------+------------------+-------+-------- - ptif_test0_index | ptif_test_index | 0 | f - ptif_test01_index | ptif_test0_index | 1 | t + relid | parentrelid | level | isleaf | pg_partition_root= =20 +-------------------+------------------+-------+--------+------------------- + ptif_test0_index | ptif_test_index | 0 | f | ptif_test_index + ptif_test01_index | ptif_test0_index | 1 | t | ptif_test_index (2 rows) =20 -- List from leaf index -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test01_index') p JOIN pg_class c ON (p.relid =3D c.oid); - relid | parentrelid | level | isleaf=20 --------------------+------------------+-------+-------- - ptif_test01_index | ptif_test0_index | 0 | t + relid | parentrelid | level | isleaf | pg_partition_root= =20 +-------------------+------------------+-------+--------+------------------- + ptif_test01_index | ptif_test0_index | 0 | t | ptif_test_index (1 row) =20 DROP TABLE ptif_test; @@ -102,6 +120,12 @@ SELECT relid, parentrelid, level, isleaf ptif_normal_table | | 0 | t (1 row) =20 +SELECT pg_partition_root('ptif_normal_table'); + pg_partition_root=20 +------------------- + ptif_normal_table +(1 row) + DROP TABLE ptif_normal_table; -- Views and materialized viewS cannot be part of a partition tree. CREATE VIEW ptif_test_view AS SELECT 1; @@ -110,5 +134,9 @@ SELECT * FROM pg_partition_tree('ptif_test_view'); ERROR: "ptif_test_view" is not a table, a foreign table, or an index SELECT * FROM pg_partition_tree('ptif_test_matview'); ERROR: "ptif_test_matview" is not a table, a foreign table, or an index +SELECT pg_partition_root('ptif_test_view'); +ERROR: "ptif_test_view" is not a table, a foreign table, or an index +SELECT pg_partition_root('ptif_test_matview'); +ERROR: "ptif_test_matview" is not a table, a foreign table, or an index DROP VIEW ptif_test_view; DROP MATERIALIZED VIEW ptif_test_matview; diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql= /partition_info.sql index 5a76f22b05..f855d26174 100644 --- a/src/test/regress/sql/partition_info.sql +++ b/src/test/regress/sql/partition_info.sql @@ -2,6 +2,9 @@ -- Tests for pg_partition_tree -- SELECT * FROM pg_partition_tree(NULL); +SELECT * FROM pg_partition_tree(0); +SELECT pg_partition_root(NULL); +SELECT pg_partition_root(0); =20 -- Test table partition trees CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a); @@ -28,26 +31,26 @@ CREATE INDEX ptif_test2_index ON ptif_test2 (a); ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index; =20 -- List all tables members of the tree -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test'); -- List tables from an intermediate level -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test0') p JOIN pg_class c ON (p.relid =3D c.oid); -- List from leaf table -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test01') p JOIN pg_class c ON (p.relid =3D c.oid); =20 -- List all indexes members of the tree -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test_index'); -- List indexes from an intermediate level -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test0_index') p JOIN pg_class c ON (p.relid =3D c.oid); -- List from leaf index -SELECT relid, parentrelid, level, isleaf +SELECT relid, parentrelid, level, isleaf, pg_partition_root(relid) FROM pg_partition_tree('ptif_test01_index') p JOIN pg_class c ON (p.relid =3D c.oid); =20 @@ -57,6 +60,7 @@ DROP TABLE ptif_test; CREATE TABLE ptif_normal_table(a int); SELECT relid, parentrelid, level, isleaf FROM pg_partition_tree('ptif_normal_table'); +SELECT pg_partition_root('ptif_normal_table'); DROP TABLE ptif_normal_table; =20 -- Views and materialized viewS cannot be part of a partition tree. @@ -64,5 +68,7 @@ CREATE VIEW ptif_test_view AS SELECT 1; CREATE MATERIALIZED VIEW ptif_test_matview AS SELECT 1; SELECT * FROM pg_partition_tree('ptif_test_view'); SELECT * FROM pg_partition_tree('ptif_test_matview'); +SELECT pg_partition_root('ptif_test_view'); +SELECT pg_partition_root('ptif_test_matview'); DROP VIEW ptif_test_view; DROP MATERIALIZED VIEW ptif_test_matview; --3gk1bTGVZuaU9V5/-- --f0Ums9VvOMUG7syy Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAlwJz38ACgkQnvQgOdby QH2bVxAAm3zqgrQy9yyJOAAYIp6ZfoCbXfMjso4Do8oqCMWvLaI8o6AMrA3wkaGj NSBJC6Vi7UiUhKlz4k7GAKwFTDn3MUY2FTmi5GDD8LFfvTv+BNcHK/DzV+9/T2xI +KIZnCgG1g4kOu3YXoWxB02U9IU+atImcjogiSiS1snrdCIMbOWxulNj/NFIflio dwNchkxH8dzMz2wBW4frccgsgL6SfoqtRklyw0X6ks3t3UNxwJXX5w4ac2v4P8/P x/ucWE4S+1dRyD455ijqm9sNcoAI1+dKtziThTKCQdGt2FPLsS9Izn+07tfkNPcT IF9mHuy7iPcA9bLk0R9opVJkNZmV7/+0lIn/JFcLG4eARX+h2RDlXrhU1FFSMHjO oNBOhU93RwPXQzY4rEsJAToQLqxAMRhC8VyaSqw9PykFjUr1CBZat7ae/qhnESuw KehXV9Hc8AhIS2/xzij7nA2KNPJ3XeXIabaJh1z8SFsOX6E3FBNdi6AyQHxrFG59 dKJuRdfWN2Fjr6wUNJYcq58FYgdS+vVwNc7EtM576ZrTqT+EDxvFPKR6YGh60PvA i9YORHan1eIlw0Uz0f/9UsRkGnkwzg20A0xxd9eTiNxdgMjXpzpFGInFyJlvqnog Bv1gvdtp1zDVM/p+DSST8q0bz82g4i7SeTfCOi8kISpC1oki0hI= =vgVv -----END PGP SIGNATURE----- --f0Ums9VvOMUG7syy--