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

> On Oct 12, 2021, at 10:54 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Tue, Oct 12, 2021 at 1:18 PM 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.
>
> I guess that's not that bad. Why did we end up with the behavior that
> the current comment describes this way?
>
> "(Additional dots in the name portion are not treated as special.)"
>
> I thought there was some reason why it needed to work that way.

We're not talking about the parsing of string literals, but rather about the parsing of shell-style patterns. The primary caller of this logic is processSQLNamePattern(), which expects only a relname or a (schema,relname) pair, not database names nor anything else.

The pattern myschema.my.*table is not a three-part pattern, but a two part pattern, with a literal schema name and a relation name pattern. In v14 it can be seen to work as follows:

\d pg_toast.pg_.oast_2619
TOAST table "pg_toast.pg_toast_2619"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
Owning table: "pg_catalog.pg_statistic"
Indexes:
"pg_toast_2619_index" PRIMARY KEY, btree (chunk_id, chunk_seq)

\d pg_toast.pg_.*_2619
TOAST table "pg_toast.pg_toast_2619"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
Owning table: "pg_catalog.pg_statistic"
Indexes:
"pg_toast_2619_index" PRIMARY KEY, btree (chunk_id, chunk_seq)

In v13, neither of those matched anything (which is defensible, I guess) but the following did match, which is really nuts:

+CREATE SCHEMA g_;
+CREATE TABLE g_.oast_2619 (i integer);
+\d pg_toast..g_.oast_2619
+ Table "g_.oast_2619"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |

The behavior Justin reported in the original complaint was \d regresion.public.bit_defaults, which gets handled as schema =~ /^(regresion)$/ and relname =~ /^(public.bit_defaults)$/. That gives no results for him, but I tend to think no results is defensible.

Apparently, this behavior breaks an old bug, and we need to restore the old bug and then debate this behavioral change for v15. I'd rather people had engaged in the discussion about this feature during the v14 cycle, since this patch was posted and reviewed on -hackers, and I don't recall anybody complaining about it.

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