Re: psql display of foreign keys

On 2019-Feb-27, Michael Paquier wrote:

> 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.

Eh, of course -- note that the psql query I added does use
pg_partition_root, it's just that it is not useful *all by itself*.

> 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.

Hmm, true.

> 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.

Yeah, done.

> 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().

You're right, done.

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

Well. I tried this scenario
create table t1 (a int);
create table t11 () inherits (t1);
create table t2 (b int);
create table t111() inherits (t1, t2);

and the result I get from my new function is not good:
alvherre=# select * from pg_partition_ancestors('t111');
relid
-------
t111
t1
(2 filas)

it should have listed t2 too, but it doesn't. Since these functions
aren't supposed to work on legacy inheritance anyway, I think the right
action is to return the empty set. In the current version I just do
what pg_partition_tree does, but I think we should adjust that behavior.
I'll start a new thread about that.

> 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).

Exactly -- that's the whole point. We need to list all FKs that are
applicable to the partition, indicating which relation is the one where
the FK generates, and without polluting the output with countless
"internal" pg_constraint rows. The output psql presents for the PK-side
relation when it's partitioned, with my patch to support that, is quite
ugly when there are many partitions.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services