Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g7Xym-0007sj-PO for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Oct 2018 03:37:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g7Xyk-00070E-8D for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Oct 2018 03:37:38 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g7Xyi-000707-0M for pgsql-hackers@lists.postgresql.org; Wed, 03 Oct 2018 03:37:37 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g7XyY-0005eP-My for pgsql-hackers@postgresql.org; Wed, 03 Oct 2018 03:37:34 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 98D7821B4C; Tue, 2 Oct 2018 23:37:25 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Tue, 02 Oct 2018 23:37:25 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; bh=5eObIC1VS8XVkQVCMolRHXlDZGYGa4de2JA6sJ8wqXU=; b=E0pR2E/x 5eQtNNHOvLXn+IyUBd8cUwZMSfRst5k/EDNcMszEkd4oKU5JJ7MAxKeEya0lmPpF BTbcn1od5Smlp+F7pMzyZGlnG2i46vUUIQi034sqpZxODUc3HTHBCZOEkIm1z/Cw 2JALjyZlQ/aQlN7fT8QKFRhK8gw+YXTv59Q+HWhlYpyFMFwAwdWvIpSP+7J2K+3i Qryzf+DMmt+zdbQySSBHAoAj4EWUrWN1prM9HAAny2he+54MVB1KO0wnpyry1l5G We+yZHwqkwOrtqurGATmAUZyb48mZ89smkqpfpoZiLwh2STGSZtXUznHbtC6RQOu mrKh5n80Ng4bxg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; bh=5eObIC1VS8XVkQVCMolRHXlDZGYGa 4de2JA6sJ8wqXU=; b=X+y2oTeGbNog9sqtkmzeSAe+DGFCMVqDveSDNLlEGOx6Y qiBU41r3YFLzGEEga15iDLdREhTIcYoV+pwRPZKAUn05oj/0ub7OXquLEiLj5X2O A4jju9o4jxiTTwH+EeUL8lwbzK5V9STODuI5Tbc2qklolygwIN+XOwgfZFCZgB6g dR/nZJPzl4xMBPvOh+2kTJk7RKK1MfCyjRmEofCsiti9PeUI9PQMyv9AwAcpPTV4 jsFDtfoXuOo2h3EvgtnZxU0Ol7mizp4k6ep6gi/VH1dsC2EHvuGGInoF0fXwxGmD c4WAL5tWBi4IEaQRWCNvKyQbQ5xPqoRhQ80Nsur8g== X-ME-Sender: X-ME-Proxy: Received: from paquier.xyz (c137162.net61215.cablenet.ne.jp [61.215.137.162]) by mail.messagingengine.com (Postfix) with ESMTPA id D7062102DE; Tue, 2 Oct 2018 23:37:21 -0400 (EDT) Date: Wed, 3 Oct 2018 12:37:18 +0900 From: Michael Paquier To: Amit Langote Cc: Thomas Munro , Jesper Pedersen , Robert Haas , Jeevan Ladhe , Pg Hackers Subject: Re: partition tree inspection functions Message-ID: <20181003033718.GD2609@paquier.xyz> References: <28d6f8eb-afda-1bfd-141d-ca489cffb0ee@lab.ntt.co.jp> <7f2e6674-9196-9fe7-1405-368bafd64198@redhat.com> <5e91cf39-4727-903a-779c-7b7b081ac7e9@lab.ntt.co.jp> <8cd38eb0-91b8-3c3d-e0b2-e387b51c96b5@lab.ntt.co.jp> <20181001060330.GJ5982@paquier.xyz> <20181001062743.GA11712@paquier.xyz> <97131b6c-fc07-f5e6-eefd-bd5ff3f96f8d@lab.ntt.co.jp> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="AsxXAMtlQ5JHofzM" Content-Disposition: inline In-Reply-To: <97131b6c-fc07-f5e6-eefd-bd5ff3f96f8d@lab.ntt.co.jp> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --AsxXAMtlQ5JHofzM Content-Type: multipart/mixed; boundary="RhUH2Ysw6aD5utA4" Content-Disposition: inline --RhUH2Ysw6aD5utA4 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Oct 01, 2018 at 04:27:57PM +0900, Amit Langote wrote: > Yeah, maybe there is no reason to delay proceeding with > pg_partition_children which provides a useful functionality. So, I have been looking at your patch, and there are a couple of things which could be improved. Putting the new function pg_partition_children() in partition.c is a bad idea I think. So instead I think that we should put that in a different location, say utils/adt/partitionfuncs.c. + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "relid", + REGCLASSOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parentid", + REGCLASSOID, -1, 0); REGCLASSOID is used mainly for casting, so instead let's use OIDOID like any other system function. + values[2] = psprintf("%d", level); + values[3] = psprintf("%c", relkind == RELKIND_PARTITIONED_TABLE ? + 'f' : + 't'); Using Datum objects is more elegant in this context. isleaf is not particularly useful as it can just be fetched with a join on pg_class/relkind. So I think that we had better remove it. I have cleaned up a bit tests, removing duplicates and most of the things which touched the size of relations to have something more portable. We could have a flavor using a relation name in input with qualified names handled properly (see pg_get_viewdef_name for example), not sure if that's really mandatory so I left that out. I have also added some comments here and there. The docs could be worded a bit better still. My result is the patch attached. What do you think? -- Michael --RhUH2Ysw6aD5utA4 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="partition-tree-func.patch" Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9a7f683658..d41c09b68b 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20197,6 +20197,46 @@ postgres=3D# SELECT * FROM pg_walfile_name_offset(= pg_stop_backup()); The function returns the number of new collation objects it created. =20 + + Partitioning Information Functions + + + Name Return Type Descripti= on + + + =20 + + pg_partition_tree(oid) + setof record + + List information about a partition tree for the given partitioned + table, consisting of one row for each partition in a tree. The + information available is the OID of the partition, the OID of its + immediate partitioned table, and its level in the hierarchy, + beginning at 0 for the top-most parent, and + incremented by 1 for each level up. + + + + +
+ + + To check the total size of the data contained in + measurement table described in + , one could us= e the + following query: + + + +=3D# SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size + FROM pg_partition_tree('measurement'); + total_size=20 +------------ + 24 kB +(1 row) + + =20 diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index 4b35dbb8bb..132ec7620c 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -20,8 +20,8 @@ OBJS =3D acl.o amutils.o arrayfuncs.o array_expanded.o ar= ray_selfuncs.o \ jsonfuncs.o like.o lockfuncs.o mac.o mac8.o misc.o nabstime.o name.o \ network.o network_gist.o network_selfuncs.o network_spgist.o \ numeric.o numutils.o oid.o oracle_compat.o \ - orderedsetaggs.o pg_locale.o pg_lsn.o pg_upgrade_support.o \ - pgstatfuncs.o \ + orderedsetaggs.o partitionfuncs.o pg_locale.o pg_lsn.o \ + pg_upgrade_support.o pgstatfuncs.o \ pseudotypes.o quote.o rangetypes.o rangetypes_gist.o \ rangetypes_selfuncs.o rangetypes_spgist.o rangetypes_typanalyze.o \ regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \ diff --git a/src/backend/utils/adt/partitionfuncs.c b/src/backend/utils/adt= /partitionfuncs.c new file mode 100644 index 0000000000..fc0a904967 --- /dev/null +++ b/src/backend/utils/adt/partitionfuncs.c @@ -0,0 +1,128 @@ +/*------------------------------------------------------------------------- + * + * partitionfuncs.c + * Functions for accessing partitioning data + * + * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/utils/adt/partitionfuncs.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/htup_details.h" +#include "catalog/partition.h" +#include "catalog/pg_inherits.h" +#include "catalog/pg_type.h" +#include "funcapi.h" +#include "utils/fmgrprotos.h" + +/* + * pg_partition_tree + * + * Produce a view with one row per member of a partition tree, beginning + * from the top-most parent given by the caller. This gives information + * about each partition, its immediate partitioned parent and its level in + * the hierarchy. + */ +Datum +pg_partition_tree(PG_FUNCTION_ARGS) +{ +#define PG_PARTITION_TREE_COLS 3 + Oid rootrelid =3D PG_GETARG_OID(0); + FuncCallContext *funcctx; + ListCell **next; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcxt; + TupleDesc tupdesc; + List *partitions; + + /* create a function context for cross-call persistence */ + funcctx =3D SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcxt =3D MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + partitions =3D find_all_inheritors(rootrelid, NoLock, NULL); + + tupdesc =3D CreateTemplateTupleDesc(PG_PARTITION_TREE_COLS, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "relid", + OIDOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parentid", + OIDOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "level", + INT4OID, -1, 0); + + funcctx->tuple_desc =3D BlessTupleDesc(tupdesc); + + /* allocate memory for user context */ + next =3D (ListCell **) palloc(sizeof(ListCell *)); + *next =3D list_head(partitions); + funcctx->user_fctx =3D (void *) next; + + MemoryContextSwitchTo(oldcxt); + } + + /* stuff done on every call of the function */ + funcctx =3D SRF_PERCALL_SETUP(); + next =3D (ListCell **) funcctx->user_fctx; + + if (*next !=3D NULL) + { + Datum values[PG_PARTITION_TREE_COLS]; + bool nulls[PG_PARTITION_TREE_COLS]; + HeapTuple tuple; + Oid relid =3D lfirst_oid(*next); + List *ancestors =3D get_partition_ancestors(lfirst_oid(*next)); + Oid parentid =3D InvalidOid; + int level =3D 0; + Datum result; + ListCell *lc; + + /* + * Form tuple with appropriate data. + */ + MemSet(nulls, 0, sizeof(nulls)); + MemSet(values, 0, sizeof(values)); + + /* relid */ + values[0] =3D ObjectIdGetDatum(relid); + + /* parentid */ + if (ancestors !=3D NIL) + parentid =3D linitial_oid(ancestors); + if (OidIsValid(parentid)) + values[1] =3D ObjectIdGetDatum(parentid); + else + nulls[1] =3D true; + + /* level */ + if (relid !=3D rootrelid) + { + foreach(lc, ancestors) + { + level++; + if (lfirst_oid(lc) =3D=3D rootrelid) + break; + } + } + values[2] =3D Int32GetDatum(level); + + *next =3D lnext(*next); + + tuple =3D heap_form_tuple(funcctx->tuple_desc, values, nulls); + result =3D HeapTupleGetDatum(tuple); + SRF_RETURN_NEXT(funcctx, result); + } + + /* done when there are no more elements left */ + SRF_RETURN_DONE(funcctx); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.= dat index 8e4145f42b..5525ad427b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10206,4 +10206,13 @@ proisstrict =3D> 'f', prorettype =3D> 'bool', proargtypes =3D> 'oid int4= int4 any', proargmodes =3D> '{i,i,i,v}', prosrc =3D> 'satisfies_hash_partition' }, =20 +# information about a partition tree +{ oid =3D> '3423', descr =3D> 'view partition tree tables', + proname =3D> 'pg_partition_tree', prorows =3D> '1000', proretset =3D> 't= ', + provolatile =3D> 'v', prorettype =3D> 'record', proargtypes =3D> 'oid', + proallargtypes =3D> '{oid,oid,oid,int4}', + proargmodes =3D> '{i,o,o,o}', + proargnames =3D> '{rootrelid,relid,parentrelid,level}', + prosrc =3D> 'pg_partition_tree' } + ] diff --git a/src/test/regress/expected/partition_info.out b/src/test/regres= s/expected/partition_info.out new file mode 100644 index 0000000000..fed50420b5 --- /dev/null +++ b/src/test/regress/expected/partition_info.out @@ -0,0 +1,52 @@ +-- +-- Tests for pg_partition_tree +-- +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; +-- all tables in the tree with some size information +SELECT relid::regclass, parentrelid::regclass, level, + pg_relation_size(relid) =3D 0 AS is_empty + FROM pg_partition_tree('ptif_test'::regclass); + relid | parentrelid | level | is_empty=20 +-------------+-------------+-------+---------- + ptif_test | | 0 | t + ptif_test0 | ptif_test | 1 | t + ptif_test1 | ptif_test | 1 | t + ptif_test2 | ptif_test | 1 | f + ptif_test01 | ptif_test0 | 2 | f + ptif_test11 | ptif_test1 | 2 | f +(6 rows) + +-- children of the main tree +SELECT relid::regclass, parentrelid::regclass, level + FROM pg_partition_tree('ptif_test0'::regclass); + relid | parentrelid | level=20 +-------------+-------------+------- + ptif_test0 | ptif_test | 0 + ptif_test01 | ptif_test0 | 1 +(2 rows) + +SELECT relid::regclass, parentrelid::regclass, level + FROM pg_partition_tree('ptif_test01'::regclass); + relid | parentrelid | level=20 +-------------+-------------+------- + ptif_test01 | ptif_test0 | 0 +(1 row) + +-- this results in NULL, as there are no level 1 partitions of a leaf part= ition +SELECT sum(pg_relation_size(relid)) AS total_size + FROM pg_partition_tree('ptif_test01'::regclass) WHERE level =3D 1; + total_size=20 +------------ + =20 +(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 rangefu= ncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune reloptions hash_part indexin= g partition_aggregate +test: identity partition_join partition_prune reloptions hash_part indexin= g partition_aggregate partition_info =20 # 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_sch= edule 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..2ad75882b0 --- /dev/null +++ b/src/test/regress/sql/partition_info.sql @@ -0,0 +1,30 @@ +-- +-- Tests for pg_partition_tree +-- +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; + +-- all tables in the tree with some size information +SELECT relid::regclass, parentrelid::regclass, level, + pg_relation_size(relid) =3D 0 AS is_empty + FROM pg_partition_tree('ptif_test'::regclass); + +-- children of the main tree +SELECT relid::regclass, parentrelid::regclass, level + FROM pg_partition_tree('ptif_test0'::regclass); +SELECT relid::regclass, parentrelid::regclass, level + FROM pg_partition_tree('ptif_test01'::regclass); + +-- this results in NULL, as there are no level 1 partitions of a leaf part= ition +SELECT sum(pg_relation_size(relid)) AS total_size + FROM pg_partition_tree('ptif_test01'::regclass) WHERE level =3D 1; + +DROP TABLE ptif_test; --RhUH2Ysw6aD5utA4-- --AsxXAMtlQ5JHofzM Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAlu0OW4ACgkQnvQgOdby QH1sHxAAnLyXnTv+UJLPtmN/a2UimVujqj9kRPTAlrdR9qos82lrvmqbpVkNZzFR 1VrcjEt/2kXU/OTHa37BoX4wljAWxACVtNLmXU7toUp2Wkh2ZFVB+X1skahF2vlC No7cGCiw1nVA+CTsz0mwh5dQYXOH/qcX4MV2CHps8lOYoqrZAor4kb9uo7+A4BGs Hs3eXjwEVd4oeNXXM3olR/GEB1kB4md/PhO3Gzg0Pdd6sV8XV6IfEj+4lDddH4Ue qfcg2MTboMTgqDA2PoFehd20nISCt7SnSO7VYG0/EBx0EA9Enmyup/QgpzRRg4qv iAFgcRIlhb4MQu4DA5ZS5n94PsvEGPQ40PuNwcdhUYSbJUqr5+1EfcAQ4ZhLbzXH pdhJd4OuuAYT8HdbhtTEks6lOpy0plleR1I/9guPtQ5x/3pr4nlD043/+kOMI5dv Y9CI57smMbvhIeHcqInbCYmmIdIjUG7wXMFtbcIn10t6gD+NPyuiwo0Fqn6wrPPZ 40aWql/cMkl+cRV2DOt1mkDmUhKfXYmRFvh86+5EX10ErUPOLnQKAlnZNcotNjv9 A/PW/ZiAeTqTqvUdGpyFhBuQLfVQBI7HI2+uT68B/zamaQ2JUNCHys1VT2hy9tMh q9equmIocJH3q1MziO9UiIpkUFkpbmcmlwhfDIL+Q2ZkDDf0T20= =UMAj -----END PGP SIGNATURE----- --AsxXAMtlQ5JHofzM--