Date: Fri, 22 Mar 2019 12:55:38 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Add pg_partition_root to display top-most parent of a
 partition
Message-ID: <20190322035538.GO20192@paquier.xyz>
References: <E1grthW-00078n-Az@gemulon.postgresql.org>
 <20190322032612.GA323@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="phbq2bkSb+hZnunM"
Content-Disposition: inline
In-Reply-To: <20190322032612.GA323@alvherre.pgsql>
Precedence: bulk


--phbq2bkSb+hZnunM
Content-Type: multipart/mixed; boundary="rUztinBX/EQDJOOk"
Content-Disposition: inline


--rUztinBX/EQDJOOk
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, Mar 22, 2019 at 12:26:12AM -0300, Alvaro Herrera wrote:
> I noticed days ago that if you call pg_partition_root on the topmost
> partitioned table, the server crashes :-)

It's when you think that the thing is actually done that another issue
pops up.  The attached fixes the issue, I suggest to return the
top-most parent as result if the input is the top-most parent itself.
Returning NULL does not make sense as in this case the relation can be
part of a partition tree.
--
Michael

--rUztinBX/EQDJOOk
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="partition-root-fix.patch"
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt=
/partitionfuncs.c
index 98c8ef77f1..ff21cd3783 100644
--- a/src/backend/utils/adt/partitionfuncs.c
+++ b/src/backend/utils/adt/partitionfuncs.c
@@ -191,6 +191,14 @@ pg_partition_root(PG_FUNCTION_ARGS)
=20
 	/* Fetch the top-most parent */
 	ancestors =3D get_partition_ancestors(relid);
+
+	/*
+	 * If the listed partition is already the top-most parent, just
+	 * return itself.
+	 */
+	if (ancestors =3D=3D NIL)
+		PG_RETURN_OID(relid);
+
 	rootrelid =3D llast_oid(ancestors);
 	list_free(ancestors);
=20
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regres=
s/expected/partition_info.out
index 92aa6abe3f..42b6bc77ca 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -46,6 +46,31 @@ CREATE TABLE ptif_test2 PARTITION OF ptif_test
 -- This partitioned table should remain with no partitions.
 CREATE TABLE ptif_test3 PARTITION OF ptif_test
   FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+-- Test pg_partition_root for tables
+SELECT pg_partition_root('ptif_test');
+ pg_partition_root=20
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test0');
+ pg_partition_root=20
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test01');
+ pg_partition_root=20
+-------------------
+ ptif_test
+(1 row)
+
+SELECT pg_partition_root('ptif_test3');
+ pg_partition_root=20
+-------------------
+ ptif_test
+(1 row)
+
 -- Test index partition tree
 CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
 CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
@@ -60,6 +85,31 @@ CREATE INDEX ptif_test2_index ON ptif_test2 (a);
 ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;
 CREATE INDEX ptif_test3_index ON ptif_test3 (a);
 ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index;
+-- Test pg_partition_root for indexes
+SELECT pg_partition_root('ptif_test_index');
+ pg_partition_root=20
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test0_index');
+ pg_partition_root=20
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test01_index');
+ pg_partition_root=20
+-------------------
+ ptif_test_index
+(1 row)
+
+SELECT pg_partition_root('ptif_test3_index');
+ pg_partition_root=20
+-------------------
+ ptif_test_index
+(1 row)
+
 -- List all tables members of the tree
 SELECT relid, parentrelid, level, isleaf
   FROM pg_partition_tree('ptif_test');
diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql=
/partition_info.sql
index adaca23232..b5060bec7f 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -22,6 +22,12 @@ CREATE TABLE ptif_test2 PARTITION OF ptif_test
 CREATE TABLE ptif_test3 PARTITION OF ptif_test
   FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
=20
+-- Test pg_partition_root for tables
+SELECT pg_partition_root('ptif_test');
+SELECT pg_partition_root('ptif_test0');
+SELECT pg_partition_root('ptif_test01');
+SELECT pg_partition_root('ptif_test3');
+
 -- Test index partition tree
 CREATE INDEX ptif_test_index ON ONLY ptif_test (a);
 CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
@@ -37,6 +43,12 @@ ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_=
index;
 CREATE INDEX ptif_test3_index ON ptif_test3 (a);
 ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index;
=20
+-- Test pg_partition_root for indexes
+SELECT pg_partition_root('ptif_test_index');
+SELECT pg_partition_root('ptif_test0_index');
+SELECT pg_partition_root('ptif_test01_index');
+SELECT pg_partition_root('ptif_test3_index');
+
 -- List all tables members of the tree
 SELECT relid, parentrelid, level, isleaf
   FROM pg_partition_tree('ptif_test');

--rUztinBX/EQDJOOk--

--phbq2bkSb+hZnunM
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAlyUXLoACgkQnvQgOdby
QH048A/+Ode3omOOH+imVmml2GbegzTJVVPab3J5HJpjGCC3hv+c7uG5a3gGHYSe
iO1hDwdZbtse6S7ei9/kDNqyHwfZO6wxNRtR/waSeoTM2wfcbqCMp/pPcYyYPz/D
E/e/fbt874gtcZH90bToy6sYkf9XfQ34zr87xJKHvoyo3tyIgKItLHHIFrWieR8S
kqTfstTAjOk6levpm2Ej3d5bJ03YIApdnbd8AwmUhRnJl0gLFMbbvug/WQcZF26G
m48XXMVRHN08ZpP7XrUnf+JlMqqQNsFE91abcyCSuMqqkZFnN9JQvxba3wU0xgqL
0x13WcvUykPYFc8kRAAwHDnZSUZ7GuFL0JXYPvHt9re9v+tpWJaBAwbtQHafcTXD
oAUbYNNCF3spv1oSoFBUoKQ4oB47ozO5LlpA3Gqg0nNHxJp3fGH15u/RJ7p40p0r
A12MMqCaELAH0c8lqU1GBb9IFY4I2tmNm43MRjwiKvHfLze4Q6WKt62kE8IksTwA
fPTx1oKvsYdungba7F7bBY55tdnUXPNmKsukNJ4B1o/RSL6PU72y1fLt3Xz7oKw9
/rrD3z5Q1V9q9dWFG8FDLtPN3kG7XnAs6q08xTkKwiEaVHHt5nDfdea76hAInNe5
rnSHvu1v4ShkBeK1hYUH1m8dm0uaFA4lq7+OKino1F9VpBDoEhg=
=JQjj
-----END PGP SIGNATURE-----

--phbq2bkSb+hZnunM--