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