Re: Are we losing momentum?

On 21 Apr 2003, Neil Conway wrote:

> On Mon, 2003-04-21 at 11:30, scott.marlowe wrote:
> > 'show tables' is SQL, and can be run from a script, with the output
> > parsed.
>
> But "select * from pg_tables" (or the equivalent query on the
> information schemas) is SQL, can be run from a script, and can be parsed
> by a client application.

But it's not an answer. In psql we have the \ commands, which I love. In
a client side app, select * from pg_tables is just the beginning. You've
got to join that to pg_class and jump through quite a few hoops.

For instance, a \d on a simple table in my database produces this much SQL
in the backend:

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='profile'
**************************

********* QUERY **********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'profile'
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = 'profile' AND c.oid = d.adrelid AND d.adnum = 1
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'profile' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid
AND NOT i.indisunique ORDER BY c2.relname
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'profile' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid
AND i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'profile' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid
AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

Yet there is no equivalent materialized view that puts the data together
for the user. I don't know about you, but show table tablename is a bit
easier to grasp for beginners than the above sequence of SQL statements.

> > But, how do I write an app to ask such questions easily? psql -E is not
> > the easiest and most intuitive way to learn how to get the data into a
> > structure for parsing in a client side app.
>
> You're conflating two distinct issues: (1) providing an interface for
> CLI use by the DBA (2) providing an API for programmer use in
> applications.

Why are those two seperate issues? Why can't the same answer be easily
and readily available to both the DBA and the programmer? Why does one
have to first use psql -E to figure out the queries needed then figure out
which ones to use and not use etc...? I'm not saying the \ commands are
bad, I'm saying they're implemented in the wrong place. Having \ in the
psql monitor is fine. But it should really be hitting views in the
background where possible.

> If you think the existing system catalogs are not sufficiently
> intuitive, then we should fix that problem properly (for example,
> through better documentation), not by copying some ad-hoc syntax from
> another RDBMS.

I don't care what MySQL does. Period. But, I do think Postgresql has a
high learning curve because so much of it is hidden from beginners.

Better documentation won't fix this issue. The real issue here is that
psql has a facility (\ commands) that isn't present in the rest of
postgresql, and really should be. psql shouldn't be the only interface
that allows you to easily see how tables are put together etc...

> If you think the existing CLI interface (\d etc.) is not sufficiently
> intuitive (which has been what a couple people in this thread have
> argued), I don't see what that has to do with client side applications
> or parsing the output.

No, I like the psql interface. It's intuitive to me and has been since
day one. It's the lack of intuition on the application side that bothers
me.