Date: Mon, 25 Mar 2019 22:57:29 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
	Michael Paquier <michael@paquier.xyz>,
	Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: psql display of foreign keys
Message-ID: <20190326015729.GA3118@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="cNdxnHkX5QqsyA0e"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20190325223852.GA2815@alvherre.pgsql>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


--cNdxnHkX5QqsyA0e
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2019-Mar-25, Alvaro Herrera wrote:

> v9 attached; this one's final AFAICT.

Actually, I propose this fixup.  It doesn't change the current output,
but of course it affects how this works with my patch in
https://postgr.es/m/20190321215420.GA22766@alvherre.pgsql
The v9 patch does not show anything for the partitions of the referenced
partitioned table; with this one it shows like this

-- verify psql behaves sanely
\d droppk
       Partitioned table "regress_fk.droppk"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
Partition key: RANGE (a)
Indexes:
    "droppk_pkey" PRIMARY KEY, btree (a)
Referenced by:
    TABLE "dropfk" CONSTRAINT "dropfk_a_fkey" FOREIGN KEY (a) REFERENCES droppk(a)
Number of partitions: 3 (Use \d+ to list them.)

\d droppk21
            Table "regress_fk.droppk21"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
Partition of: droppk2 FOR VALUES FROM (1000) TO (1400)
Indexes:
    "droppk21_pkey" PRIMARY KEY, btree (a)
Referenced by:
    TABLE "dropfk" CONSTRAINT "dropfk_a_fkey" FOREIGN KEY (a) REFERENCES droppk(a)

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

--cNdxnHkX5QqsyA0e
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="fixup.patch"

commit 12642d9ff66a2661d4f707cb15b0c3de6a9e1d4e
Author:     Alvaro Herrera <alvherre@alvh.no-ip.org>
AuthorDate: Mon Mar 25 22:50:59 2019 -0300
CommitDate: Mon Mar 25 22:51:15 2019 -0300

    fixup

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index fc404bbaab8..91f2306734e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2489,11 +2489,12 @@ describeOneTableDetails(const char *schemaname,
 				printfPQExpBuffer(&buf,
 								  "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
 								  "       pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
-								  "  FROM pg_catalog.pg_constraint c LEFT JOIN \n"
-								  "       pg_catalog.pg_partition_ancestors(c.confrelid) ON (true)\n"
-								  " WHERE confrelid = '%s' AND contype = 'f' AND conparentid = 0\n"
+								  "  FROM pg_catalog.pg_constraint c\n"
+								  " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
+								  "                     UNION ALL VALUES (regclass '%s'))\n"
+								  "       AND contype = 'f' AND conparentid = 0\n"
 								  "ORDER BY conname;",
-								  oid);
+								  oid, oid);
 			}
 			else
 			{

--cNdxnHkX5QqsyA0e--