Re: pg14 psql broke \d datname.nspname.relname

> On Oct 12, 2021, at 10:18 AM, Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>
> Here is a WIP patch that restores the old behavior, just so you can eyeball how large it is. (It passes check-world and I've read it over once, but I'm not ready to stand by this as correct quite yet.) I need to add a regression test to make sure this behavior is not accidentally changed in the future, and will repost after doing so.

I wasn't thinking critically enough about how psql handles \d when I accepted Justin's initial characterization of the bug. The psql client has never thought about the stuff to the left of the schema name as a database name, even if some users thought about it that way. It also doesn't think about the pattern as a literal string.

The psql client's interpretation of the pattern is a bit of a chimera, following shell glob patterns for some things and POSIX regex rules for others. The reason for that is shell glob stuff gets transliterated into the corresponding POSIX syntax, but non-shell-glob stuff is left in tact, with the one outlier being dots, which have a very special interpretation. The interpretation of a dot as meaning "match one character" is not a shell glob rule but a regex one, and one that psql never supported because it split the pattern on all dots and threw away stuff to the left. There was therefore never an opportunity for an unquoted dot to make it through to the POSIX regular expression for processing. For other regex type stuff, it happily passed it through to the POSIX regex, so that the following examples work even though they contain non-shell-glob regex stuff:

v13=# create table ababab (i integer);
CREATE TABLE

v13=# \dt (ab){3}
List of relations
Schema | Name | Type | Owner
--------+--------+-------+-------------
public | ababab | table | mark.dilger
(1 row)

v13=# \dt pg_catalog.pg_clas{1,2}
List of relations
Schema | Name | Type | Owner
------------+----------+-------+-------------
pg_catalog | pg_class | table | mark.dilger

v13=# \dt pg_catalog.pg_[am]{1,3}
List of relations
Schema | Name | Type | Owner
------------+-------+-------+-------------
pg_catalog | pg_am | table | mark.dilger
(1 row)

Splitting the pattern on all the dots and throwing away any additional leftmost fields is a bug, and when you stop doing that, passing additional dots through to the POSIX regular expression for processing is the most natural thing to do. This is, in fact, how v14 works. It is a bit debatable whether treating the first dot as a separator and the additional dots as stuff to be passed through is the right thing, so we could call the v14 behavior a mis-feature, but it's not as clearcut as the discussion upthread suggested. Reverting to v13 behavior seems wrong, but I'm now uncertain how to proceed.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company