Re: add \dpS to psql

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