Re: psql display of foreign keys

On Tue, Feb 26, 2019 at 07:27:57PM -0300, Alvaro Herrera wrote:
> Thanks for committing pg_partition_root ... but it turns out to be
> useless for this purpose.

Well, what's done is done. The thing is useful by itself in my
opinion.

> It turns out that we need to obtain the list
> of *ancestors* of the table being displayed, which pg_partition_tree
> does not easily give you. So I ended up adding yet another auxiliary
> function, pg_partition_ancestors, which in its current formulation
> returns just a lits of OIDs of ancestor tables. This seems generally
> useful, and can be used in conjunction with pg_partition_tree():
>
> alvherre=# select t.* from pg_partition_tree(pg_partition_root('pk11')) t
> join pg_partition_ancestors('pk11', true) a on (t.relid = a.relid);
> relid | parentrelid | isleaf | level
> -------+-------------+--------+-------
> pk | | f | 0
> pk1 | pk | f | 1
> pk11 | pk1 | t | 2
> (3 filas)
>
> (A small tweak is to change the return type from OID to regclass.
> Docbook additions missing also.)

In the second patch, pg_partition_ancestors always sets include_self
to true. What's the use case you have in mind to set it to false? In
the other existing functions we always include the argument itself, so
we may want to keep things consistent.

I think that you should make the function return a record of regclass
elements instead of OIDs to be consistent. This could save casts for
the callers.

Adding the self-member at the beginning of the record set is more
consistent with the order of the results returned by
get_partition_ancestors().

It would be nice to add some tests in partition_info.sql for tables
and indexes (both work).

> Anyway, given this function, it's possible to fix the psql display to be
> as I showed previously. Patches attached.

+ " FROM pg_constraint, pg_partition_ancestors('%s', 't')\n"
+ " WHERE confrelid = relid AND contype = 'f' AND conparentid = 0\n"
A JOIN would have been cleaner in my opinion, but feel free with the
style you think is more adapted.

For the meaning of using pg_partition_ancestors, I see... Not only do
you want to show the foreign keys defined in the top-most parent, but
also these defined in intermediate layers. That makes sense. Using
only pg_partition_root would have been enough to show FKs in the
top-most parent, but the intermediate ones would be missed (using only
pg_partition_root() would miss the FKs fk_partitioned_fk_5_a_fkey1 and
fk_partitioned_fk_5_a_fkey when doing "\d fk_partitioned_fk_5_1" based
on the test set).
--
Michael