On Thu, Jan 18, 2018 at 06:54:18PM +0900, Amit Langote wrote: > I think having pg_partition_root() and pg_partition_parent() will give > users enough to get useful views as follows:
So... pg_partition_root() gives you access to the highest relation in the hierarchy, and pg_partition_parent() gives you access to the direct parent. > drop table p; > create table p (a int) partition by list (a); > create table p123 partition of p for values in (1, 2, 3) partition by list (a); > create table p12 partition of p1 for values in (1, 2) partition by list (a); > create table p12 partition of p123 for values in (1, 2) partition by list (a); > create table p1 partition of p12 for values in (1); > create table p2 partition of p12 for values in (2); > create table p3 partition of p123 for values in (3); You need to reorder those queries, the creation of the first p12 would fail as p1 does not exist at this point. Wouldn't also a pg_partition_tree() be useful? You could shape it as a function which returns all regclass partitions in the tree as unique entries. Combined with pg_partition_parent() it can be powerful as it returns NULL for the partition at the top of the tree. So I think that we could live without pg_partition_root(). At the end, let's design something which makes unnecessary the use of WITH RECURSIVE when looking at a full partition tree to ease the user's life. Documentation, as well as regression tests, would be welcome :) -- Michael
signature.asc
Description: PGP signature