Re: psql display of foreign keys

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: psql display of foreign keys
Date: 2019-02-27 18:37:23
Message-ID: 20190227183723.GA10032@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v4-0001-pg_partition_ancestors.patch text/x-diff 9.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-02-27 18:48:08 Re: pg_partition_tree crashes for a non-defined relation
Previous Message Christophe Pettus 2019-02-27 18:22:51 Re: Remove Deprecated Exclusive Backup Mode