Date: Sun, 28 Apr 2019 17:15:41 +0200
From: David Fetter <david@fetter.org>
To: Fabien COELHO <coelho@cri.ensmp.fr>
Cc: Rafia Sabih <rafia.pghackers@gmail.com>,
	PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: [PATCH v5] Show detailed table persistence in \dt+
Message-ID: <20190428151541.GQ28936@fetter.org>
References: <20190423005642.GZ28936@fetter.org>
 <alpine.DEB.2.21.1904230656210.29102@lancre>
 <20190424062623.GF28936@fetter.org>
 <alpine.DEB.2.21.1904240949080.27327@lancre>
 <CA+FpmFdx2aDtyiVD2av9+QAunA1L5uscm3dL1SFbcOs8-W678g@mail.gmail.com>
 <CA+FpmFeurtDiutfh2yv0T4S=zY0iegcSStR-5xJ9H7vP2XKQsg@mail.gmail.com>
 <20190427041849.GL28936@fetter.org>
 <alpine.DEB.2.21.1904270844180.29865@lancre>
 <20190427174330.GN28936@fetter.org>
 <alpine.DEB.2.21.1904272214030.29865@lancre>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="cDtQGJ/EJIRf/Cpq"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <alpine.DEB.2.21.1904272214030.29865@lancre>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


--cDtQGJ/EJIRf/Cpq
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Sat, Apr 27, 2019 at 10:38:50PM +0200, Fabien COELHO wrote:
> 
> Hello David,
> 
> Patch applies. There seems to be a compilation issue:
> 
>  describe.c:5974:1: error: expected declaration or statement at end of
>  input
>   }

This is in brown paper bag territory. Fixed.

> > I think the way forward is to test this with TAP rather than the
> > fixed-string method.
> 
> Ok.

I've sent a separate patch extracted from the one you sent which adds
stdin to our TAP testing infrastructure. I hope it lands so it'll be
simpler to add these tests in a future version of the patch.

> > Checks removed while I figure out a new TAP test.
> 
> > > I only have packages down to pg 9.3, so I could not test prior 9.1.
> > > By looking at the online documentation, is seems that relistemp
> > > appears in pg 8.4, so the corresponding extraction should be guarded
> > > by this version.  Before that, temporary objects existed but were
> > > identified indirectly, possibly because they were stored in a
> > > temporary schema. I suggest not to try to address cases prior 8.4.
> > 
> > Done.
> 
> After some checking, I think that there is an issue with the version
> numbers:
>  - 9.1 is 90100, not 91000
>  - 8.4 is 80400, not 84000

Another brown paper bag, now fixed.

> Also, it seems that describes builds queries with uppercase
> keywords, so probably the new additions should follow that style:
> case -> CASE (and also when then else end as…)

Done.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--cDtQGJ/EJIRf/Cpq
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v5-0001-Show-detailed-table-persistence-in-dt.patch"

From 3baf2d98dd699b3b04c2d3ac038dedfc9369cef7 Mon Sep 17 00:00:00 2001
From: David Fetter <david.fetter@onelogin.com>
Date: Mon, 22 Apr 2019 17:50:48 -0700
Subject: [PATCH v5] Show detailed table persistence in \dt+
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.20.1"

This is a multi-part message in MIME format.
--------------2.20.1
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


\d would show this for individual tables, but there wasn't an
overarching view of all tables. Now, there is.

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ee00c5da08..8b205f6e37 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3631,7 +3631,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, false, true, false, false, false, false};
+	static const bool translate_columns[] = {false, false, true, false, false, false, false, false};
 
 	/* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
 	if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
@@ -3680,22 +3680,37 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	if (verbose)
 	{
 		/*
-		 * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate
-		 * size of a table, including FSM, VM and TOAST tables.
+		 * Show whether a table is permanent, temporary, or unlogged.
+		 * Indexes are not, as of this writing, tables.
 		 */
-		if (pset.sversion >= 90000)
-			appendPQExpBuffer(&buf,
-							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"",
-							  gettext_noop("Size"));
-		else if (pset.sversion >= 80100)
-			appendPQExpBuffer(&buf,
-							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
-							  gettext_noop("Size"));
-
-		appendPQExpBuffer(&buf,
-						  ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
-						  gettext_noop("Description"));
+		if (!showIndexes)
+		{
+			if (pset.sversion >= 90100)
+				appendPQExpBuffer(&buf,
+								  ",\n  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' ELSE 'unknown' END as \"%s\"",
+								  gettext_noop("Persistence"));
+			else if (pset.sversion >= 80400)
+				appendPQExpBuffer(&buf,
+								  ",\n CASE WHEN c.relistemp THEN 'temporary' ELSE 'permanent' END as \"%s\"",
+								  gettext_noop("Persistence"));
+		}
 	}
+	/*
+	 * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate
+	 * size of a table, including FSM, VM and TOAST tables.
+	 */
+	if (pset.sversion >= 90000)
+		appendPQExpBuffer(&buf,
+						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"",
+						  gettext_noop("Size"));
+	else if (pset.sversion >= 80100)
+		appendPQExpBuffer(&buf,
+						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
+						  gettext_noop("Size"));
+
+	appendPQExpBuffer(&buf,
+					  ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
+					  gettext_noop("Description"));
 
 	appendPQExpBufferStr(&buf,
 						 "\nFROM pg_catalog.pg_class c"

--------------2.20.1--


--cDtQGJ/EJIRf/Cpq--