Re: [HACKERS] Are we losing momentum? - Mailing list pgsql-patches

> >> I think the idea has some merit; although I wonder whether it
> >> wouldn't be smarter to put the code in the backend so that you
> >> don't need a parser in psql.  The SHOW code could fall back to
> >> looking at these possibilities after it fails to find a match to
> >> a GUC variable name.
>
> > Well, I think that the backend should be kept clean of MySQL's
> > nastiness.
>
> Keep in mind though that there was already talk of migrating most of
> the \d functionality to the backend (primarily as a way of
> decoupling psql from catalog version changes).  If we were to do
> that, it would make good sense to make it accessible via SHOW as
> well.  IMHO anyway.

:-/ Yeah, I've been following that from a distance and I'm not so wild
about that.  I really like that the information_schema has been
integrated into the base, but translating the SHOW commands into
SELECTs from information_schema on the backend seems like a bad idea
unless its going to be done abstract enough via some kind of rewrite
engine that allows users to program the database to translate their
verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be
kinda fun.

Getting back to SHOW, what do you want to show or not show?  Does the
backend show what's most user friendly?  If that's the case, do you
only show tables that a user has SELECT access to?  Does SHOW return
tuples like a SELECT?  What if a SHOW statement doesn't show what the
user is interested in (view definitions)?  How about when those view
definitions get really long and hard to visually see on a terminal
screen?  There's no select list available in the SHOW syntax to limit
out excessive bits.

While adding the ability to set MYSQL_MODE as something that a user
could set in their .psqlrc, I thought it'd be the ideal progression to
do a few things:

1) change the \d commands to the appropriate SELECT from the
   information_schema.  Doing this'll go a long way toward keeping the
   structure of the database contained in the database and psql
   independent.

2) Set a few tunables that specify the select list for the SELECTs
   from the information_schema that way a user can specify what they
   see/don't see.

3) SHOW is syntactic user goo that makes MySQL users feel happy and
   should be in the user interface.  Because SHOW is a user interface
   nicety, real admins that over see database users could change
   users' .psqlrc files to specify the select list that the user/site
   wants, which could possibly be even the entire query.

Hrm, how's this for a more concise argument:

Pushing SHOW/\d into the backend is a bad idea. The backend is a
relational database, not a user interface.  The information_schema.*
tables/views are the SQL sanctioned interface that the backend
provides.  How a user interfaces with the database/information_schema
is something that should be left up to the user interface program
(psql) and not pushed into the backend.  If a user wants to type "SHOW
TABLES LIKE p" instead of "\dt p*", so be it, but that's a user
interface concern, not an SQL concern.  The SQL way of getting the
same data as "SHOW TABLES" is via SELECTing from the
information_schema schema.  Implementing SQL commands in the backend
to make up for MySQL's inability to be forward thinking and
consequently hack in a syntax to wrap around their system catalogs for
newbie DB users is bad juju.  By the same token, doesn't mean
PostgreSQL can't provide the same lovey dovey interface that new users
expect, it should, however mean that the backend should be left alone
to do what it specializes in (being an SQL conformant relational DB)
and that the user interface (psql in this case) should be left alone
to implement what SHOW TABLES really means.

Keep in mind, that the only time that the SHOW commands are used, from
what I've been able to ascertain, is when DBAs are in psql and doing
basic admin work and exploring/creating their corner of the universe.
Anyone who's seriously trying to write a tool to inspect the database
knows PostgreSQL reasonably well and uses SELECT + the system
catalogs.  The target audience for a SHOW syntax isn't the power DBAs
or people writing interfaces to examine PostgreSQL, it's the newbie
creating a table for a hack project via the CLI (psql).  Allowing
users to customize the meaning of the \d/SHOW commands would make psql
much more powerful than it currently is and would address many of
these usability concerns.  I'm now thinking that psql should intercept
all non-standard SQL calls (bits not starting with SELECT, UPDATE,
INSERT, ALTER, etc) and translate them into the appropriate SQL.
Having a generic mechanism for doing this would make psql
significantly cleaner.

Anyway, I'll rest on this topic until I hear whether or not folks
would rather have this done in psql or on the backend, but I'd like to
get this in place somewhere so that I can stop reworking bits from
MySQL to PostgreSQL.  If it's determined that the bits should be done
in psql, I'll gladly finish things up, clean things up, add the docs,
move things over to use the information_schema, and if folks would
like, add the appropriate functionality that'll allow folks to
configure the \d commands/SHOW via their .psqlrc.

-sc

--
Sean Chittenden