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

> On Oct 13, 2021, at 6:24 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> 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.
>
> But not this part, or at least not entirely.
>
> If we pass the dots through to the POSIX regular expression, we can
> only do that either for the table name or the schema name, not both -

Agreed.

> either the first or last dot must mark the boundary between the two.
> That means that you can't use all the same regexy things for one as
> you can for the other, which is a strange system.

The closest analogy is how regular expressions consider \1 \2 .. \9 as backreferences, but \10 \11 ... are dependent on context: "A multi-digit sequence not starting with a zero is taken as a back reference if it comes after a suitable subexpression (i.e., the number is in the legal range for a back reference), and otherwise is taken as octal." Taking a dot as a separator if it can be taken that way, and as a regex character otherwise, is not totally out of line with existing precedent. On the other hand, the backreference vs. octal precedent is not one I particularly like.

> I knew that your
> patch made it do that, and I committed it that way because I didn't
> think it really mattered, and also because the whole system is already
> pretty strange, so what's one more bit of strangeness?
>
> I think there are at least 3 defensible behaviors here:
>
> 1. Leave it like it is. If there is more than one dot, the extra ones
> are part of one of the regex-glob thingies.
>
> 2. If there is more than one dot, error! Tell the user they messed up.

I don't like the backward compatibility issues with this one. Justin's use of database.schema.relname will work up until v14 (by throwing away the database part), then draw an error in v14, then (assuming we support the database portion in v15 onward) start working again.

> 3. If there are exactly two dots, treat it as db-schema-user. Accept
> it if the dbname matches the current db, and otherwise say we can't
> access the named db. If there are more than two dots, then (a) it's an
> error as in (2) or (b) the extra ones become part of the regex-glob
> thingies as in (2).

3a is a bit strange, when considered in the context of patterns. If db1, db2, and db3 all exist and each have a table foo.bar, and psql is connected to db1, how should the command \d db?.foo.bar behave? We have no problem with db1.foo.bar, but we do have problems with the other two. If the answer is to complain about the databases that are unconnected, consider what happens if the user writes this in a script when only db1 exists, and later the script stops working because somebody created database db2. Maybe that's not completely horrible, but surely it is less than ideal.

3b is what pg_amcheck does. It accepts database.schema.relname, and it will complain if no matching database/schema/relation can be found (unless --no-strict-names was given.)

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