Re: psql display of foreign keys

On 2018-Dec-04, Tom Lane wrote:

> ... this patch breaks the expectation set at the top of describe.c:
>
> * Support for the various \d ("describe") commands. Note that the current
> * expectation is that all functions in this file will succeed when working
> * with servers of versions 7.4 and up. It's okay to omit irrelevant
> * information for an old server, but not to fail outright.

Fixed in the attached.

> Do you really need WITH RECURSIVE for this?

I don't see any other way, but I'm open to ideas.

> If so, I'd suggest applying it only when relkind ==
> RELKIND_PARTITIONED_TABLE, so that the case doesn't happen in servers
> too old to have WITH. That's probably a win performance-wise anyway,
> as I have no doubt that the performance of this query is awful
> compared to what it replaces, so we don't really want to use it if we
> don't have to.

The query to display foreign keys on the current table can continue to
use the old, fast version when the table is not a partition (I had to
add the relispartition column to another query for this to work). But I
cannot use the old version for the query that searches for FKs
referencing the current table, because the table for which
partitionedness matters is the other one. (The WITH version is only
used for servers that can have foreign keys on partitioned tables, viz.
11).

I spent a few minutes trying to think of a way of determining which
query to use at SQL-execution time -- two CTEs, one of which would be
short-circuited ... but I couldn't figure out how. I also tried to use
the new pg_partition_tree() function, but it's useless for this purpose
because it roots at its argument table, and there doesn't seem to be a
convenient way to obtain the topmost ancestor.

v2 attached.

Many thanks for reviewing.

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