Date: Fri, 6 Jan 2023 16:36:51 -0800
From: Nathan Bossart <nathandbossart@gmail.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Maxim Orlov <orlovmg@gmail.com>, Andrew Dunstan <andrew@dunslane.net>,
	Michael Paquier <michael@paquier.xyz>, Tom Lane <tgl@sss.pgh.pa.us>,
	Isaac Morland <isaac.morland@gmail.com>,
	Justin Pryzby <pryzby@telsasoft.com>,
	Pavel Luzanov <p.luzanov@postgrespro.ru>,
	pgsql-hackers@postgresql.org
Subject: Re: add \dpS to psql
Message-ID: <20230107003651.GA789289@nathanxps13>
References: <45224.1670476523@sss.pgh.pa.us>
 <20221208171503.GA2113@nathanxps13>
 <Y5K7MxuH7Bi+zU5V@paquier.xyz>
 <20221209184055.GA59417@nathanxps13>
 <20221209184411.GB59417@nathanxps13>
 <09d09155-7ed0-29e2-c51b-e5cce7e68bf0@dunslane.net>
 <20221212171822.GA202606@nathanxps13>
 <CACG=ezY=8r8730tK04tb-gEa-xOVueXw70_o8VWe=HkzceqNhA@mail.gmail.com>
 <20221228212612.GA4010186@nathanxps13>
 <CAEZATCWDMjxEVKmSj3QD+Expo4YALMN3dxZS-UwS7wqbyBXtWw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAEZATCWDMjxEVKmSj3QD+Expo4YALMN3dxZS-UwS7wqbyBXtWw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20230107003651.GA789289%40nathanxps13>
Precedence: bulk

On Fri, Jan 06, 2023 at 06:52:33PM +0000, Dean Rasheed wrote:
> Looking this over this, I have a couple of comments:

Thanks for reviewing.

> Firstly, I think it should allow \zS in the same fashion as \dpS,
> since \z is an alias for \dp, so the 2 should be kept in sync.

That seems reasonable to me.

> Secondly, I don't think the following is the right SQL clause to use
> in the absence of "S":
> 
>     if (!showSystem && !pattern)
>         appendPQExpBufferStr(&buf, "AND n.nspname !~ '^pg_'\n");
> 
> I know that's the condition it used before, but the problem with using
> that now is that it will cause temporary relations to be excluded
> unless the "S" modifier is used, which goes against the expectation
> that "S" just causes system relations to be included. Also, it fails
> to exclude information_schema relations, if that happens to be on the
> user's search_path.
> 
> So I think we should use the same SQL clauses as every other psql
> command that supports "S", namely:
> 
>     if (!showSystem && !pattern)
>         appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
>                              "      AND n.nspname <> 'information_schema'\n");

Good catch.  I should have noticed this.  The deleted comment mentions that
the system/temp tables normally aren't very interesting from a permissions
perspective, so perhaps there is an argument for always excluding temp
tables without a pattern.  After all, \dp always excludes indexes and TOAST
tables.  However, it looks like \dt includes temp tables, and I didn't see
any other meta-commands that excluded them.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com