Date: Thu, 28 Jun 2018 20:30:35 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
	Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: partition tree inspection functions
Message-ID: <20180628113035.GA1716@paquier.xyz>
References: <8d00e51a-9a51-ad02-d53e-ba6bf50b2e52@lab.ntt.co.jp>
 <b24e77c4-3ac1-7c2e-9359-629385578175@2ndquadrant.com>
 <e0762082-09fc-030d-15e5-ee450d63b440@lab.ntt.co.jp>
 <d3dfbc48-ebfe-79ed-3a44-f81880e6aba6@2ndquadrant.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="3V7upXqbjpZ4EhLz"
Content-Disposition: inline
In-Reply-To: <d3dfbc48-ebfe-79ed-3a44-f81880e6aba6@2ndquadrant.com>
Precedence: bulk


--3V7upXqbjpZ4EhLz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, Jun 28, 2018 at 12:37:14PM +0200, Peter Eisentraut wrote:
> I'm thinking, an SQL query might be more efficient if you want to
> qualify the query further.  For example, give me all tables in this tree
> that match '2018'.  If you wrote your functions as SQL-language
> functions, the optimizer could perhaps inline them and optimize them
> further.

Are you thinking about SQL functions here?  Here is an example of query
able to fetch an entire partition tree.
WITH RECURSIVE partition_info
          (relid,
           relname,
           relsize,
           relispartition,
           relkind) AS (
        SELECT oid AS relid,
               relname,
               pg_relation_size(oid) AS relsize,
               relispartition,
               relkind
        FROM pg_catalog.pg_class
	WHERE relname = 'your_parent_table_name_here' AND
	      relkind = 'p'
      UNION ALL
        SELECT
             c.oid AS relid,
             c.relname AS relname,
             pg_relation_size(c.oid) AS relsize,
             c.relispartition AS relispartition,
             c.relkind AS relkind
        FROM partition_info AS p,
             pg_catalog.pg_inherits AS i,
             pg_catalog.pg_class AS c
        WHERE p.relid = i.inhparent AND
             c.oid = i.inhrelid AND
             c.relispartition
      )
    SELECT * FROM partition_info;

Getting the direct parent is immediate, and getting the top-most parent
would be rather similar to that.  Not much elegant in my opinion, but
that's mainly a matter of taste?
--
Michael

--3V7upXqbjpZ4EhLz
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAls0xtsACgkQnvQgOdby
QH1j6g//YZVnowAkpIv9+YTCPkYvIRTcz6CWoAcNvi/xpOcTWN6yN7VYOZuoS9zt
U/f7tWAa1nqM3mWxnyfk+05FwJfnH5JmRionK3pCX+jncCxDwEuPLnc8S2SuQyXR
30clRh8E/xVtzWx77euoSjiv/Vxc47cJe3xd1Ij+EUL61yaSEYZnMOmLv+jqWiRB
ksBOMSSDq+/4FJ6FwrZRELDOsNqYhWR9sMdmPRboLKis1c/YCVxmHMXETV6jvuFR
N2mAL4MSMIHc7hmzPbiC/KbWn0FFeDrzzZ1q080DCEhbLQA/1gP94lOppsvuEgeq
l0UldbJ08yFvXyOgTCCZyLn2kO+J8TGkHelacM0GtLKpjwVgho8Ay87HPaby/VlO
iYXuRyDFA0UEFGQXoSBs3SN/BeTXddZf0+cylt86zpX67sGXN+lR+C/lEL2xP9Ae
QJro2Em7o4y4o5rCaB2qQRPPwdB4WlMMZVNizI/hNu7+NYCuRiAdE08t6TcfTlFp
38g/b+SOG9Orhmpq97BfmTN5gsWpe+ES+N9P8ZGhl9Whh8oDGaeCZnuZB/FbKfkM
SUQtxDGUBNgvxgxH3LgMI9lexhZlMWxBaPmyCUErxArqoi+K1A9lGVPpRIJTSC5k
Tmve73seHhwFTiTTMQsBds2Mme/bTR9P/36T2JnGnUQH2tggOXk=
=b8gC
-----END PGP SIGNATURE-----

--3V7upXqbjpZ4EhLz--