Re: SHOW TABLES

On Sat, Jul 17, 2010 at 5:30 PM, Stefan Kaltenbrunner
<stefan(at)kaltenbrunner(dot)cc> wrote:
> On 07/17/2010 04:02 PM, Robert Haas wrote:
>> On Jul 16, 2010, at 11:02 PM, Stephen Frost<sfrost(at)snowman(dot)net>  wrote:
>>> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>>>>
>>>> Why must the backslash commands be more powerful than any alternative
>>>> we might come up with?
>>>
>>> Because they encode alot of information in a character- something which
>>> is next to impossible to do in "english".
>>
>> I don't think that "terse" and "powerful" are the same thing. One of my
>> beefs with the backslash commands is that the syntax is not cleanly
>> extensible.  We have S and + as postfix modifiers, and that's fairly
>> comprehensible, but as soon as you think about going much further with it,
>> it starts to seem like alphabet soup.
>>
>> In fact, we're pretty close to alphabet soup already. Without looking at
>> the help, what does \db do?  What are the commands to list casts,
>> conversions, and comments, respectively?  What syntax would you propose for
>> a backslash command to list comments, but only those on a certain object
>> type?  If you don't think we should have a backslash command for that, can
>> you write an SQL query that lists comments on built-in aggregates in less
>> than two minutes?  How many people do you think can do it at all?
>>
>> I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward
>> in usability.
>
> uh oh - that actually sounds like a big step backwards to me - it's
> inventing extremely verbose pseudo english syntax for something that we
> currently do with a trivial and easy to remember backslash command.

Which trivial and easy-to-remember backslash command is that?

> Do we really need to invent a completely new language for this?
> Once you extend that syntax to what you are proposing (ie provide a way to
> filter like "LIST COMMENTS ON SYSTEM AGGREGATES WITH NUMERIC INPUT") you
> basically reinvented a query language - ever heard of SQL or QUEL?

Uhm, yes! I'm not going to say the SQL is the epitome of language
design, but actually I've done a fair amount of work on a database
product that uses it heavily - and I rather like it, on the whole. I
notice that you didn't actually answer any of the points that I raised
in the "alphabet soup" paragraph above. I don't think there's
anything WRONG with letting "\dFp" show text search dictionaries and
"\dfwS+" list system window functions with additional detail - but I'd
like an alternative that emphasizes ease of remembering over brevity,
works in every client, and can be extended in whatever reasonable ways
the community decides are worth having.

When we committed the patch to add extensible options to EXPLAIN, I
didn't know exactly what options we were going to end up with - but I
knew that somebody else would think up use cases for the new
functionality, and so it proved. In 9.0 we have EXPLAIN (BUFFERS),
something which would have gotten shot down if it had necessitated the
use of the old syntax, due to keyword proliferation, and it wouldn't
surprise me if additional options get added in the future. In the
same way, I'm not exactly sure how far and in what direction we might
decide to extend the syntax of any query-language-based routines to
list or describe database objects - but I'm almost positive that the
current requirement that they be able to expressed as a backslash
command is limiting what we can do.

I'd like to be able to list comments on objects of a particular type.
And, yeah, I'd like to be able to list all the aggregates that take a
numeric argument, or all the functions that take, say, an argument of
type internal. Right now, this is an ENORMOUS pain in the neck. I
usually end up running psql -c '<some backslash command>' | grep |
awk ... or something like that. I have no idea what Windows users do.
I'm sure it's possible to write a query to do it, but it's not
anything approaching easy. All of this talk about backslash commands
being powerful rings totally hollow for me. For ordinary, day to day
tasks like listing all my tables, or looking at the details of a
particular table, they're great. I use them all the time and would
still use them even if some other syntax were available. But there is
no reasonable way to pass options to them, and that to me is a pretty
major drawback.

> I'm not sure where to draw the line but implementing a proper shortcut
> interface for cammands is something taht should be done on the client side
> because not every client is the same and the needs of psql might be
> radically different from any other client (like pgadmin or a fancy Web 2.0
> AJAX thingy - those will likely always use custom catalog queries).
> Maybe a differnet way to look at the whole thing is to reconsider our own
> catalogs (anyone remember newsysview?) and add a bunch of views to abstract
> away most of the current complexity for these usecases?

Our previous experiments in this area haven't been wildly successful.
For example, we have pg_tables, but suppose you want to augment the
results with the size of each table. To handle this in a schema-safe
manner, you need the OID, which isn't available, so you're back to
querying against pg_class directly. The selection of columns in
pg_tables is pretty random anyway - I dunno why someone thinks that
hasindexes, hasrules, and hastriggers are more interesting than any
other property of the table you might care to query. I'm not opposed
to some further experimentation in this area, but I'm not convinced
it's going to get us very far. What's your proposal?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company