Re: psql \d option list overloaded

Alex J. Avriette wrote:
> On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote:
> 
> > Anything other than simple, short commands is a waste, IMHO.  I can easily
> > remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they 
> > reflect
> > my intensions directly and 'make sense'.
> 
> What makes sense to me in csh doesn't make sense in a bourne shell.

And yet, bash has !$ and job control just like csh, even though they're
not standard Bourne-shell features.

It's not a bad thing to adopt good ideas from other projects.

> You can't expect all applications to work correctly. 

You can't expect this anyway, at least when dealing with cross-database
applications.  The intersection of the SQL feature sets across all the
major database engines is pretty limited -- small enough that you'll
almost certainly end up using something database-specific when attempting
to do anything truly nontrivial.

> I'd like to second
> Peter's "yep" when asked if he could remember all the various \d*
> commands. It really comes down to whether you're trying. New software
> (even though you may have been using it for a year) requires some
> adjustment.

This is true, but it's no argument against implementing "show
databases", "show tables", and "describe".

Every database engine is different, but in the case of PG it makes sense
to adopt the best methods we can find.  A consistent and easy to
remember way of showing the various entities in psql (at the very least)
would be of great advantage.  It's something that MySQL gets right.  As
it turns out, we already have "SHOW" in psql and it's used for something
else.  So we might instead use something else (e.g. "VIEW") instead.

Either way, a single command that takes as its argument the type of entity
you want to see would be extremely useful, and much easier to remember
than what we currently have -- because the names of the entities that
are available are already well-defined and are likely known to the user
already.

> > What's more important is the ability to use these commands from any
> > interface not just 'psql' client.  I think 'psql' already has the slash
> > commands.  No need to create NEW slash commands there...
> > 
> > >If you want to find out how to show the databases in sql, use psql -E.
> > > 
> > >
> > Have you actually done that?  OMG!
> 
> Yes, I do it frequently. You may notice a recent post of mine used
> exactly that output.

Now do it from within psql.

It's \l, as it turns out.  This violates the principle of least surprise
because psql generally uses \d* to show entities.

> > 3) like MySQL does it...
> > 
> >    SHOW TABLES;
> 
> Should postgres also support the '#' comment? What other non-sql
> sqlisms should we support?

PG already has a number of PG-specific features.  Adding more,
*especially* if they happen to be compatible with other databases, isn't
going to hurt much.

No, the thing to worry about here is whether or not these commands
("SHOW", for instance) will appear in the SQL spec and will have a
completely different meaning from the meaning in PG.  Also of concern is
that "SHOW" is already reserved and used for something else.  We'd have
to use something other than "SHOW" for the purpose being discussed.

> > There's something to be said about the 'SHOW'and 'DESC' sql-extensions
> > added into MySQL.  Newbies can really 'get' it quickly.  It's what really
> 
> I would argue that these are not "sql extensions" at all. If you like, I
> can go over the source to verify this myself, but my guess is that MySQL
> is doing exactly what postgres is doing, and evaluating this as a macro.

No, they are built into MySQL's backend parser.  You can easily verify
this by executing these commands from within Perl or Python.  They
return a table just like any other SQL command that returns data.

> Furthermore, databases are not designed for "newbies" to jump right in
> with both feet. They are designed to be robust and stable. 

Now this is ludicrous.  Yes, they're designed to be robust and stable,
but that has absolutely nothing to do with how easy they are to use.

> Additionally,
> some SQL compliance is nice. After that, you work on features. 

If we were talking about something that went against the SQL standard
then I would agree with you.  But we're talking about something that,
as far as I know, isn't in the SQL standard at all.  Implementing it
won't make us noncompliant with the SQL standard any more than the
implementation of CREATE INDEX has.

> Changing the interface so that you or others don't have to read the 
> documentation smacks of laziness. 

Really?  One could make the same argument for standards of any kind,
yes?  :-)

> Somebody like Bruce, Peter, or Tom (or indeed somebody else) is going
> to waste yet more time making things like this available to somebody
> who probably won't read any of the other documentation either, and will
> wind up on irc pestering somebody like myself, Dave, or Neil. Why is
> this progress?

It's progress because it will keep those people from pestering someone
in the know about how to show the available databases, or how to
describe a table.

> > sold me on MySQL when I first learned it.  For me, it's like:
> > 
> >    'dir' in DOS,
> >    'ls' in Unix
> >    'SHOW' in MySQL
> >    ??? in PostgreSQL ?
> 
> We've been over this. It's \d*. 

For listing databases it's \l.  Not exactly consistent with the rest of
the related psql commands.

> > Sure, with time as my database needs grew and I matured as a developer,
> > I eventually gained more respect for PostgreSQL and have made the switch
> > even without this feature, but to this day, I really think MySQL *did it
> > right* with those extensions.  You can't become a PostgreSQL guru without
> > being a newbie first.  I vote we make it easier for newbies.
> 
> What really frightens me here is that I know of several applications (shudder,
> "LAMP" applications) which use the output of "show tables" or other of your
> "extensions." The problem with this is precisely that it /isn't/ sql, and it
> can't be supported as a static command. 

Of course not.  But applications which rely on information such as that
provided by "show tables" will typically not be possible to write while
adhering to the feature intersection of all major databases anyway.

> It is intended to be there for people
> to use interactively. 

Nonsense.  It's there to be used.  Whether it's used interactively or
not is irrelevant.  The command provides useful information.  But see
below.

> Making "pseudo sql" will encourage more developers to
> (and I'd apologize for this if it weren't true) code in Postgres the same
> lazy way they code in MySQL.

This is a strawman argument, although I understand your concern here.
To be honest, for application development I'd much rather see people
use information_schema, but that's only because information_schema is
in the SQL standard and as such should be the preferred way to retrieve
the information that the "SHOW" commands in MySQL return.  That said,
the inclusion of information_schema is a very recent development on the
PostgreSQL side of things, and doesn't even exist on some other major
databases such as MSSQL.

Of course, a PG equivalent to MySQL's "show" would be an even more
recent development...  :-)


-- 
Kevin Brown                          kevin@sysexperts.com