From: David Fetter <david@fetter.org>
To: Rafia Sabih <rafia.pghackers@gmail.com>
Cc: Fabien COELHO <coelho@cri.ensmp.fr>
Cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH v1] Show whether tables are logged in \dt+
Date: Sat, 27 Apr 2019 06:18:49 +0200
Message-ID: <20190427041849.GL28936@fetter.org> (raw)
In-Reply-To: <CA+FpmFeurtDiutfh2yv0T4S=zY0iegcSStR-5xJ9H7vP2XKQsg@mail.gmail.com>
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>
On Fri, Apr 26, 2019 at 04:22:18PM +0200, Rafia Sabih wrote:
> On Fri, 26 Apr 2019 at 14:49, Rafia Sabih <rafia.pghackers@gmail.com> wrote:
> >
> > On Wed, 24 Apr 2019 at 10:30, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> > >
> > >
> > > Hello David,
> > >
> > > >>> I noticed that there wasn't a bulk way to see table logged-ness in psql,
> > > >>> so I made it part of \dt+.
> > > >>
> > > >> Applies, compiles, works for me.
> > > >>
> > > >> ISTM That temporary-ness is not shown either. Maybe the persistence column
> > > >> should be shown as is?
> > > >
> > > > Temporariness added, but not raw.
> > >
> > > Ok, it is better like this way.
> > >
> > > >> Tests?
> > > >
> > > > Included, but they're not stable for temp tables. I'm a little stumped
> > > > as to how to either stabilize them or test some other way.
> > >
> > > Hmmm. First there is the username which appears, so there should be a
> > > dedicated user for the test.
> > >
> > > I'm unsure how to work around the temporary schema number, which is
> > > undeterministic with parallel execution it. I'm afraid the only viable
> > > approach is not to show temporary tables, too bad:-(
> > >
> > > >> Doc?
> > > >
> > > > What further documentation does it need?
> > >
> > > Indeed, there is no precise doc, so nothing to update :-)/:-(
> > >
> > >
> > > Maybe you could consider adding a case for prior 9.1 version, something
> > > like:
> > > ... case c.relistemp then 'temporary' else 'permanent' end as ...
> > >
> > >
> > I was reviewing this patch and found a bug,
> >
> > create table t (i int);
> > create index idx on t(i);
> > \di+
> > psql: print.c:3452: printQuery: Assertion `opt->translate_columns ==
> > ((void *)0) || opt->n_translate_columns >= cont.ncolumns' failed.
>
> Looking into this further, apparently the position of
>
> if (verbose)
> {
> + /*
> + * Show whether the table is permanent, temporary, or unlogged.
> + */
> + if (pset.sversion >= 91000)
> + 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"));
>
> is not right, it is being called for indexes with verbose option also.
> There should be an extra check for it being not called for index case.
> Something like,
> if (verbose)
> {
> /*
> * Show whether the table is permanent, temporary, or unlogged.
> */
> if (!showIndexes)
> if (pset.sversion >= 91000)
> 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"));
>
> Not sure, how do modify it in a more neat way.
I suspect that as this may get a little messier, but I've made it
fairly neat short of a major refactor.
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/donateAttachments:v3-0001-Show-detailed-table-persistence-in-dt.patch (text/x-diff, 4.4K) download
From c5bd317b9904fe017afa5489b11a3cbae6e80f3b 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 v3] 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.cindex ee00c5da08..620e7d69bf 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,17 +3680,32 @@ 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"));+ if (!showIndexes)+ {+ if (pset.sversion >= 91000)+ 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+ 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\"",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.outindex 35856bffdd..f4f468804f 100644--- a/src/test/regress/expected/psql.out+++ b/src/test/regress/expected/psql.out@@ -4729,3 +4729,16 @@ drop schema testpart;
set search_path to default;
set role to default;
drop role testrole_partitioning;
+create table foo(id integer);+create temp table tfoo(id integer);+create unlogged table ufoo(id integer);+\dt+ *.*foo+ List of relations+ Schema | Name | Type | Owner | Persistence | Size | Description +-----------+------+-------+---------+-------------+---------+-------------+ pg_temp_3 | tfoo | table | shackle | temporary | 0 bytes | + public | foo | table | shackle | permanent | 0 bytes | + public | ufoo | table | shackle | unlogged | 0 bytes | +(3 rows)++drop table foo, tfoo, ufoo;diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sqlindex 78f4b5d7d5..c9ad6ffd9c 100644--- a/src/test/regress/sql/psql.sql+++ b/src/test/regress/sql/psql.sql@@ -1115,3 +1115,10 @@ set search_path to default;
set role to default;
drop role testrole_partitioning;
++set search_path = public, pg_temp;+create table foo(id integer);+create temp table tfoo(id integer);+create unlogged table ufoo(id integer);+\dt+ *.*foo+drop table foo, tfoo, ufoo;--------------2.20.1--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: david@fetter.org, rafia.pghackers@gmail.com, coelho@cri.ensmp.fr
Subject: Re: [PATCH v1] Show whether tables are logged in \dt+
In-Reply-To: <20190427041849.GL28936@fetter.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox