Re: Schemas: status report, call for developers

Tom Lane wrote:
> psql's \d command hasn't the foggiest idea that there might now be more
> than one pg_class entry with the same relname. It needs to be taught
> about that --- but even before that, we need to work out schema-aware
> definitions of the wildcard expansion rules for psql's backslash
> commands that accept wildcarded names. In the above example, probably
> "\d mytab" should have said "no such table" --- because neither foo nor
> bar were in my search path, so I should not see them unless I give a
> qualified name (eg, "\d foo.mytab" or "\d bar.mytab").

(and also in mail to Bill Cunningham)
> My gut feeling is that "\d mytab" should tell you about the same
> table that "select * from mytab" would find. Anything else is
> probably noise to you --

General consistency with SELECT behaviour sounds right to me.

> For commands
> that accept wildcard patterns, what should happen --- should "\z my*"
> find these tables, if they're not in my search path? Is "\z f*.my*"
> sensible to support? I dunno yet.

My digestive organs tell me: an unqualified wildcard pattern should
stick to the search path; the search path should only be overridden
when the user explicitly provides a wildcard pattern for schema names.
This would be consistent with the behaviour of \d etc., i.e.
"\d mytab" should look for 'mytab' in the current search path;
"\dt my*" should look for tables beginning with "my" in the current
search path; "\dt f*.my*" would look for same in all schemas beginning
with "f"; and "\dt *.my*" would look in all schemas.

Problem: "wildcard pattern" is a bit of a misnomer, the relevant
commands take regular expressions, which means the dot in "\z f*.my*"
won't necessarily be the dot in "\z foo.mytab" - it would have to
be written "\z f*\\.my*". Though technically correct this
strikes me as counterintuitive, especially with the double escaping
(once for psql, once for the regex literal).

An alternative solution would be to allow the pattern matching
commands to accept either one ("\z my*") or two ("\z f* my*") regular
expressions; in the latter case the first regex is for the schema name,
the second for the object name. However, doing away with the dot altogether
is also counterintuitive and breaks with the usual schema denotation.

Proposal: in "wildcard" slash commands drop regular expressions and
use LIKE for pattern matching. This would enable commands such as
"\z f%.my%". (Would this mean major breakage? Is there an installed
base of scripts which rely on psql slash commands and regular expressions?)
I can't personally recall ever having needed to use a regular expression
any more complex than the wildcard pattern matching which could be implemented
just as well with LIKE. (Anyone requiring regular expression matching could
still create appropriate views).

Question - which output format is preferable?:

schema_test=# \z
Access privileges for database "schema_test"
Schema | Object | Access privileges
--------+--------+-------------------
public | bar |
foo | bar |
(2 rows)

or

schema_test=# \z
Access privileges for database "schema_test"
Object | Access privileges
------------+-------------------
public.bar |
foo.bar |
(2 rows)

> If you've got time to work on fixing frontend code, or even helping
> to work out definitional questions like these (...)

Hmm, time for "ask not what your database can do for you but what
you can do for your database". I'm willing to put my keyboard where
my mouth is and take on psql once any outstanding questions are
cleared up, if noone better qualified than me comes
forward and provided someone takes a critical look at anything I do.

Yours

Ian Barwick