From: Michael Paquier <michael@paquier.xyz>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: partition tree inspection functions
Date: Thu, 28 Jun 2018 20:30:35 +0900
Message-ID: <20180628113035.GA1716@paquier.xyz> (raw)
In-Reply-To: <d3dfbc48-ebfe-79ed-3a44-f81880e6aba6@2ndquadrant.com>
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>
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: michael@paquier.xyz, peter.eisentraut@2ndquadrant.com, Langote_Amit_f8@lab.ntt.co.jp
Subject: Re: partition tree inspection functions
In-Reply-To: <20180628113035.GA1716@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox