Re: psql \d option list overloaded

Поиск
Список
Период
Сортировка
Искать

Re: psql \d option list overloaded

От:
Rod Taylor <pg@rbt.ca>
Дата:
> Anything other than simple, short commands is a waste, IMHO.  I can easily
> remember SHOW DATABASES and SHOW TABLES and DESC , because they 
> reflect
> my intensions directly and 'make sense'.

Can you remember how to get a list of indexes on a particular table? How
about a specific indexes build? I ask, because I constantly forgot both
of those (don't like FROM).

> 2) (using information schema ... little better)
> 
>     SELECT table_name FROM information_schema.tables WHERE table_schema 
> = 'public';
> 
> or ...
> 
> 3) like MySQL does it...
> 
>     SHOW TABLES;
> 
> Lemme think about which one I prefer ;-) Uh, Ok, I'm done thinking
> now. hehe.

I actually prefer #2 myself. It works on a number of databases aside
from just PostgreSQL. So, as a user who worked in a mixed environment it
was easier to remember.

But I get your point.

> 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

I agree with the simple SHOW TABLES command but disagree with:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]

I much prefer:

SELECT * FROM COLUMNS WHERE table LIKE '%tab%' AND database = 'billing';

It's not much longer, certainly more natural to those that know SQL, and
infinitely more useful since you can create result sets that the
programmer of SHOW hadn't considered. A perfect example is the addition
of the FULL clause in SHOW. The above select does not need additional
keywords for different formatting options as it can simply use "natural"
SQL styling.

Re: psql \d option list overloaded

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Greg Stark  writes:
> So for example:

> \describe table foo => \dt foo
> \describe index foo => \di foo
> \describe aggregate foo => \da foo
> \describe operator foo => \do foo

It doesn't seem to me that this buys much except verboseness, though.

ISTM there are three fundamental problems with \d and friends:
1. Some people have a hard time remembering the commands.2. Some people aren't using psql.3. psql keeps breaking across backend versions because the   needed commands change.

I don't see a lot of value in addressing just one of these problem
areas, when we could instead do something that addresses all three.
		regards, tom lane

Re: psql \d option list overloaded

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Peter Eisentraut  writes:
> Tom Lane wrote:
>> 2. Some people aren't using psql.

> I don't see why this is an issue.  People not using psql are either 
> using a GUI, which presumably supports plenty of "show" and "describe" 
> functionality, or they're writing their own program, in which case it 
> doesn't really matter how short or easy to remember the commands are.

But this interacts with point 3 (psql breaks on every new backend
version).  It's not desirable to have every GUI and large custom program
implementing its own set of metadata inquiry commands: they all have
to go through the same update pain as psql.  Perhaps if people start to
rely on information_schema for those things, life will get better,
but I'm unconvinced that will happen.  psql itself certainly hasn't
moved in that direction.
		regards, tom lane

psql \d option list overloaded

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
D. Dante Lorenso wrote:
> > When I started with PostgreSQL and MySQL, MySQL was far easier
> > to use
> 
> I started with MySQL and it WAS easier to use.  It was easier because
> the manual essentially reads:
> 
>    -- we didn't implement anything complicated that's why
>    -- we are fast.
> 
> The only SQL customizations that MySQL has that I really miss in PostgreSQL
> are the commands:
> 
> 	SHOW DATABASES;
> 	SHOW TABLES;
> 	DESC table;
> 
> That was ubber simple to do in MySQL.  To this day, I have trouble with
> that in PostgreSQL.  I'm constantly doing:
> 
> 	psql> \?
> 	psql> help;
> 	ERROR:  syntax error at or near "help" at character 1
> 	psql> \h
> 	...
> 	* damnit, that's not it...*
> 	psql> \?
> 	psql> \d
> 	* ok, now which flag do I use for tables vs functions..etc?*
> 
> I finally figure it out, I just end up forgetting again later.  I still
> have no clue how I'd find the same data without using psql.  In MySQL
> I can run those queries from PHP, PERL...etc.  I know you can find that
> data in system tables in PostgreSQL, but I don't wanna muck around with
> all that.  I just wanna do something as simple as MySQL.

[ Moved to hackers.]

I am starting to agree that our \d* handling is just too overloaded. 
Look at the option list from \?:Informational  \d [NAME]      describe table, index, sequence, or view  \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)                 list tables/indexes/sequences/views/system tables  \da [PATTERN]  list aggregate functions  \dc [PATTERN]  list conversions  \dC            list casts  \dd [PATTERN]  show comment for object  \dD [PATTERN]  list domains  \df [PATTERN]  list functions (add "+" for more detail)  \dg [PATTERN]  list groups  \dn [PATTERN]  list schemas  \do [NAME]     list operators  \dl            list large objects, same as \lo_list  \dp [PATTERN]  list table access privileges  \dT [PATTERN]  list data types (add "+" for more detail)  \du [PATTERN]  list users  \l             list all databases (add "+" for more detail)  \z [PATTERN]   list table access privileges (same as \dp)

Can anyone remember all those?  With the single-letter options, once the
list got too long, we started encouraging long option names.  It seems
the same is true of \d.

I like the idea of adding a new syntax to show that information using
simple SQL command syntax, and putting it in the backend so all
applications can access it.  I know we have information schema, and
maybe that can be used to make this simpler.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610) 359-1001 +  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: psql \d option list overloaded

От:
"Alex J. Avriette" <alex@posixnap.net>
Дата:
On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote:

> > I finally figure it out, I just end up forgetting again later.  I still
> > have no clue how I'd find the same data without using psql.  In MySQL
> > I can run those queries from PHP, PERL...etc.  I know you can find that
> > data in system tables in PostgreSQL, but I don't wanna muck around with
> > all that.  I just wanna do something as simple as MySQL.
> 
> [ Moved to hackers.]
> 
> I am starting to agree that our \d* handling is just too overloaded. 
> Look at the option list from \?:

> I like the idea of adding a new syntax to show that information using
> simple SQL command syntax, and putting it in the backend so all
> applications can access it.  I know we have information schema, and
> maybe that can be used to make this simpler.

Bruce, while I agree with you about \d (and all its children), as well
as the querying we talked about on irc, I disagree with the notion of a
"SHOW DATABASES" query. This is one of the things that irritates me
about mysql is the pseudo-sql that everyone has come to accept (through
learning sql on mysql) as "sql". Things such as the '#' comments, and
the various SHOW DATABASES, I feel, detract from the "look and feel" of
the database. That look and feel is one of the reasons I am so loyal to
postgres (and indeed why some people are so loyal to mysql).

It doesn't make sense to create pseudo-sql, when all you're abstracting
is function-macros. I think the backslash syntax is fine. If you really
wanted to change it, you might consider a different syntax for it. Many
of us are familiar with slash/bang/colon/backslash commands in
interfacing with the programs we use regularly (vi, shells, irc
clients). Why not a /functions as a long syntax for \df? Would there be
a direct problem using the forward slash as a command indicator? This
way you could give people like the original poster something they were
looking for, eg:

/functions
/databases

and what I was looking for:

/functions timestamp

It also allows us a lot more freedom in changing the syntax, as the
expression of the commands is english (or, pick your language). I seem
to recall Neil mentioning to me that was a problem with
internationalization, but that's over my head.

I don't have any particular allegiance to the forward slash over anything
else. My chief concern is that what we're abstracting here are macros, and
as such, they should not be treated as sql. Because they aren't sql. If
you want to find out how to show the databases in sql, use psql -E.

Alex

--
alex@posixnap.net
Alex J. Avriette, Solaris Systems Masseur
"I ... remain against the death penalty because I feel that eternal boredom with no hope of parole is a much worse punishment than just ending it all mercifully with that quiet needle." - Rachel Mills, NC Libertarian Gubernatorial Candidate

Re: psql \d option list overloaded

От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Bruce Momjian wrote:
> I am starting to agree that our \d* handling is just too overloaded.
> Look at the option list from \?:

> Can anyone remember all those?

Yes.

> I like the idea of adding a new syntax to show that information using
> simple SQL command syntax, and putting it in the backend so all
> applications can access it.  I know we have information schema, and
> maybe that can be used to make this simpler.

That's right.

Re: psql \d option list overloaded

От:
"Alex J. Avriette" <alex@posixnap.net>
Дата:
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 , because they 
> reflect
> my intensions directly and 'make sense'.

What makes sense to me in csh doesn't make sense in a bourne shell.
You can't expect all applications to work correctly. 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.

> Using the slash commands works if you are familiar with them ... sorta
> like 'ls' switches (I type 'ls -alF' without thinking about what those
> switches do because it's embedded in my head from years of repetition.
> Any other flags to 'ls', and I gotta go hit the man pages.)

So, hit the documentation when you can't find what you need. Postgres
has superb documentation. Or, as a counterpoint, ls -alF doesn't always
work on every operating system (such as old IRIX and old SunOS), and 
is sometimes even a shell builtin. The flawed assumption, again, is that
because /your/ environment supports something, that another environment
should.

> 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.

> 1) Using System Catalogs ... (from psql -E)
> 
>    SELECT n.nspname as "Schema",
>        c.relname as "Name",
>        CASE c.relkind

-snip-

>    ORDER BY 1,2;

If the point here was to show that it was a complex query, then the
only real reply is "This is why we have macros!". 

> 3) like MySQL does it...
> 
>    SHOW TABLES;

Should postgres also support the '#' comment? What other non-sql
sqlisms should we support?

> 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.

Furthermore, databases are not designed for "newbies" to jump right in
with both feet. They are designed to be robust and stable. Additionally,
some SQL compliance is nice. After that, you work on features. 

Changing the interface so that you or others don't have to read the 
documentation smacks of laziness. 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?

> 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*. 

> 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. It is intended to be there for people
to use interactively. 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.

Alex

--
alex@posixnap.net
Alex J. Avriette, Unix Systems Engineer
"It's computationally FEROCIOUS." - Steve Jobs, discussing 64-bit computing

Re: psql \d option list overloaded

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
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 , because they 
> > reflect
> > my intensions directly and 'make sense'.
> 
> What makes sense to me in csh doesn't make sense in a bourne shell.
> You can't expect all applications to work correctly. 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.

OK, I will drop the idea.  Thanks.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610) 359-1001 +  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: psql \d option list overloaded

От:
Tommi Maekitalo <t.maekitalo@epgmbh.de>
Дата:
Am Sonntag, 4. Januar 2004 20:13 schrieb Alex J. Avriette:
> On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote:
> > > I finally figure it out, I just end up forgetting again later.  I still
...
>
> /functions
> /databases
>
...

Long options sounds really good. It is like GNU-tools. A single - for single 
character options and a double -- for long options.

Ah - a single \ for short options in postgresql and a double \\ for long? What 
do you think?


-- 
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de

Re: psql \d option list overloaded

От:
Tommi Maekitalo <t.maekitalo@epgmbh.de>
Дата:
Hi,

>
> 2) (using information schema ... little better)
>
>     SELECT table_name FROM information_schema.tables WHERE table_schema
> = 'public';
>
> or ...
>
...

I just looked at the information_schema. It is a very nice feature, but 
difficult to use in psql.

I just wanted to see, what I can find here. After trying and rtfm I ended in 
'\d information_schema.*'. I get a very large page wich is quite unreadable. 
'\d' is normally very usable.

It would be better not to show the view-definition.

What if \d on views just show the column, type and attribute. \d+ would show 
the full view-definition.


Tommi

-- 
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de

Re: psql \d option list overloaded

От:
Kevin Brown <kevin@sysexperts.com>
Дата:
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 , 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

Re: psql \d option list overloaded

От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Kevin Brown wrote:
> 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.

What is wrong with

SELECT * FROM information_schema.tables;

?  If it's too much to type, put information_schema in the path.  This 
syntax has the advantage that you can use qualifications and other SQL 
features.  And you can build customized views on top of it.  Does SHOW 
TABLES or whatever it might be called support that?

Re: psql \d option list overloaded

От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Tom Lane wrote:
> 	2. Some people aren't using psql.

I don't see why this is an issue.  People not using psql are either 
using a GUI, which presumably supports plenty of "show" and "describe" 
functionality, or they're writing their own program, in which case it 
doesn't really matter how short or easy to remember the commands are.

Re: psql \d option list overloaded

От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Dennis Björklund wrote:
> > What is wrong with
> >
> > SELECT * FROM information_schema.tables;
>
> The result is very hard to read since it's so much of it (try column
> instead of tables). The \xx commands do some nice formatting you
> don't get from the above.

This is an interesting point to remember for those that are advocating 
pushing psql's queries into the backend.  psql's queries are optimized 
for monospaced text screens of limited size.  Unless someone else is 
writing a command-line client, there would be little reuse effect, 
because any given application will have different display requirements.

(Another problem with pushing psql's queries into the backend is that 
much of the output that psql makes is not a single table.  Sometimes 
there is more than one table, or the information is in the table 
footers.  It'd be quite complicated to make the backend produce those 
kinds of displays.)

Re: psql \d option list overloaded

От:
Robert Treat <xzilla@users.sourceforge.net>
Дата:
On Saturday 10 January 2004 19:16, Jon Jensen wrote:
> On Sat, 10 Jan 2004, Tom Lane wrote:
> > ISTM there are three fundamental problems with \d and friends:
> >
> > 	1. Some people have a hard time remembering the commands.
> > 	2. Some people aren't using psql.
> > 	3. psql keeps breaking across backend versions because the
> > 	   needed commands change.
> >
> > I don't see a lot of value in addressing just one of these problem
> > areas, when we could instead do something that addresses all three.
>
> I agree, at least for #2 and #3. But I just don't understand #1. Anything
> is hard to remember when you're just starting to learn it. But it's still
> faster to type \?  then \dt than it is to type "show tables". And
> "show tables" is hard (relatively speaking) for me to remember because I'm
> used to psql's way of doing things, since I mostly use it.
>

I'd second this point; I've certainly stumbled over the "show" syntax when 
trying to get anything other than tables in mysql.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: psql \d option list overloaded

От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Tom Lane wrote:
> But this interacts with point 3 (psql breaks on every new backend
> version).  It's not desirable to have every GUI and large custom
> program implementing its own set of metadata inquiry commands: they
> all have to go through the same update pain as psql.  Perhaps if
> people start to rely on information_schema for those things, life
> will get better, but I'm unconvinced that will happen.  psql itself
> certainly hasn't moved in that direction.

IIRC, the two killers in psql compatibility have been outer joins and 
schemas.  I don't see how we could have avoided that, except with 
highly specialized and static (parameter-less) commands.  There have 
been additional minor issues, but I suppose we could have avoided those 
if we had cared to do so at all.

Several people have in the past proposed to keep psql backward 
compatible, even if only by means of

if (version =x) {  ...
}
else if (version = y) {  ...
}

(which would be fine by me), but apparently no one has felt pressed 
enough yet.

Re: psql \d option list overloaded

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:

I have added this psql backslash discussion to TODO.detail.

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Tom Lane wrote:
> > But this interacts with point 3 (psql breaks on every new backend
> > version).  It's not desirable to have every GUI and large custom
> > program implementing its own set of metadata inquiry commands: they
> > all have to go through the same update pain as psql.  Perhaps if
> > people start to rely on information_schema for those things, life
> > will get better, but I'm unconvinced that will happen.  psql itself
> > certainly hasn't moved in that direction.
> 
> IIRC, the two killers in psql compatibility have been outer joins and 
> schemas.  I don't see how we could have avoided that, except with 
> highly specialized and static (parameter-less) commands.  There have 
> been additional minor issues, but I suppose we could have avoided those 
> if we had cared to do so at all.
> 
> Several people have in the past proposed to keep psql backward 
> compatible, even if only by means of
> 
> if (version =x) {
>    ...
> }
> else if (version = y) {
>    ...
> }
> 
> (which would be fine by me), but apparently no one has felt pressed 
> enough yet.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610) 359-1001 +  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: psql \d option list overloaded

От:
Mark Kirkwood <markir@paradise.net.nz>
Дата:
Couldn't agree more - syntax like

SHOW TABLES;

is inituitive and somehow "right" - [chuckles] - Mysql does not have 
*everything* wrong!

regards

Mark

Bruce Momjian wrote:

>I like the idea of adding a new syntax to show that information using
>simple SQL command syntax, and putting it in the backend so all
>applications can access it.  I know we have information schema, and
>maybe that can be used to make this simpler.
>
>  
>

Re: psql \d option list overloaded

От:
"D. Dante Lorenso" <dante@lorenso.com>
Дата:
Alex J. Avriette wrote:

>On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote:
>  
>
>>>I finally figure it out, I just end up forgetting again later.  I still
>>>have no clue how I'd find the same data without using psql.  In MySQL
>>>I can run those queries from PHP, PERL...etc.  I know you can find that
>>>data in system tables in PostgreSQL, but I don't wanna muck around with
>>>all that.  I just wanna do something as simple as MySQL.
>>>      
>>>
>>[ Moved to hackers.]
>>
>>I am starting to agree that our \d* handling is just too overloaded. 
>>Look at the option list from \?:
>>    
>>
>>I like the idea of adding a new syntax to show that information using
>>simple SQL command syntax, and putting it in the backend so all
>>applications can access it.  I know we have information schema, and
>>maybe that can be used to make this simpler.
>>    
>>
>Bruce, while I agree with you about \d (and all its children), as well
>as the querying we talked about on irc, I disagree with the notion of a
>"SHOW DATABASES" query. This is one of the things that irritates me
>about mysql is the pseudo-sql that everyone has come to accept ... It doesn't 
>make sense to create pseudo-sql, when all you're abstracting is function-macros...
>
Anything other than simple, short commands is a waste, IMHO.  I can easily
remember SHOW DATABASES and SHOW TABLES and DESC , because they 
reflect
my intensions directly and 'make sense'.

Using the slash commands works if you are familiar with them ... sorta
like 'ls' switches (I type 'ls -alF' without thinking about what those
switches do because it's embedded in my head from years of repetition.
Any other flags to 'ls', and I gotta go hit the man pages.)

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!

1) Using System Catalogs ... (from psql -E)
   SELECT n.nspname as "Schema",       c.relname as "Name",       CASE c.relkind           WHEN 'r' THEN 'table'           WHEN 'v' THEN 'view'           WHEN 'i' THEN 'index'           WHEN 'S' THEN 'sequence'           WHEN 's' THEN 'special' END as "Type",       u.usename as "Owner"   FROM pg_catalog.pg_class c       LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace   WHERE c.relkind IN ('r','')       AND n.nspname NOT IN ('pg_catalog', 'pg_toast')       AND pg_catalog.pg_table_is_visible(c.oid)   ORDER BY 1,2;

or ...

2) (using information schema ... little better)
   SELECT table_name FROM information_schema.tables WHERE table_schema 
= 'public';

or ...

3) like MySQL does it...
   SHOW TABLES;

Lemme think about which one I prefer ;-) Uh, Ok, I'm done thinking
now. hehe.

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
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 ?

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.

Dante

Re: psql \d option list overloaded

От:
Thomas Swan <tswan@idigx.com>
Дата:
Bruce Momjian wrote:

>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 , because they 
>>>reflect
>>>my intensions directly and 'make sense'.
>>>      
>>>
>>What makes sense to me in csh doesn't make sense in a bourne shell.
>>You can't expect all applications to work correctly. 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.
>>    
>>
>
>OK, I will drop the idea.  Thanks.
>
>  
>
Bruce,

The idea is not without merit.   What you are looking at is a way to get
this information as a query without having to know all the intricasies
of all the pg_* internals or duplicating complex queries.   "psql -E"
shows you just how tricky this is.  Secondly, if this information
changes in a release, then the end user has to rewrite all of the
queries to work.   Being able to issue a query to the dbms and get the
information as a normal SQL result makes sense and is definately convenient.

The \d* commands work from psql but not from anywhere else.    Try
getting the information from a PHP script by sending a "\dS" query.   It
doesn't work.   If the same queries were stored in the backend and
referenced by psql and also could be referenced by other scripts, this
would be a good thing and keep the work centralized.   If the queries
were in the backend, the psql users could keep the \dS command but it
would call an internal function or execute a queried stored in the
system tables.


One option is to get the information via a function like

SELECT * FROM pg_info('tables');
SELECT * FROM pg_info('indexes');


"psql -E" would show the same query being executed for "\dt"

Another option if no one wanted a language construct, perhaps one option
would be to store the queries themselves in a table like pg_queries. 
This also has the advantage of exposing the queries used so that they
can used as examples for other purposes.

+------------+------------------------------------------+
|pg_info_type|pg_query                                  |
+------------+------------------------------------------+
|tables      |SELECT n.nspname as "Schema",  c.relname  |
|            |as "Name", CASE c.relkind WHEN 'r' THEN   |
|            |'table' WHEN 'v' THEN 'view' WHEN 'i' THEN| 
|            |'index' WHEN 'S' THEN 'sequence' WHEN 's' |
|            |THEN 'special' END as "Type", u.usename as|
|            |"Owner" FROM pg_catalog.pg_class c LEFT   |
|            |JOIN pg_catalog.pg_user u ON u.usesysid = |
|            |c.relowner LEFT JOIN                      |
|            |pg_catalog.pg_namespace n ON n.oid =      |
|            |c.relnamespace WHERE c.relkind IN ('r','')|
|            |AND n.nspname NOT IN ('pg_catalog',       |
|            |'pg_toast') AND                           |
|            |pg_catalog.pg_table_is_visible(c.oid)     |
|            |ORDER BY 1,2;                             |
+------------+------------------------------------------+
|indexes     |SELECT n.nspname as "Schema", c.relname as|
|            |"Name", CASE c.relkind WHEN 'r' THEN      |
|            |'table' WHEN 'v' THEN 'view' WHEN 'i' THEN| 
|            |'index' WHEN 'S' THEN 'sequence' WHEN 's' |
|            |THEN 'special' END as "Type", u.usename as|
|            |"Owner", c2.relname as "Table" FROM       |
|            |pg_catalog.pg_class c JOIN                |
|            |pg_catalog.pg_index i ON i.indexrelid =   |
|            |c.oid JOIN pg_catalog.pg_class c2 ON      |
|            |i.indrelid = c2.oid LEFT JOIN             |
|            |pg_catalog.pg_user u ON u.usesysid =      |
|            |c.relowner LEFT JOIN                      |
|            |pg_catalog.pg_namespace n ON n.oid =      |
|            |c.relnamespace WHERE c.relkind IN ('i','')|
|            |AND n.nspname NOT IN ('pg_catalog',       |
|            |'pg_toast') AND                           | 
|            |pg_catalog.pg_table_is_visible(c.oid)     |
|            |ORDER BY 1,2;                             |
+------------+------------------------------------------+


Again, this is just food for thought.  Perhaps it is a way to satisfy
both arguments.

Thomas

Re: psql \d option list overloaded

От:
Andrew Dunstan <andrew@dunslane.net>
Дата:


Thomas Swan wrote:

>
>The \d* commands work from psql but not from anywhere else.    Try
>getting the information from a PHP script by sending a "\dS" query.   It
>doesn't work.   If the same queries were stored in the backend and
>referenced by psql and also could be referenced by other scripts, this
>would be a good thing and keep the work centralized.   If the queries
>were in the backend, the psql users could keep the \dS command but it
>would call an internal function or execute a queried stored in the
>system tables.
>
>
>  
>


I just independently had this idea, so I like it :-)


cheers

andrew

Re: psql \d option list overloaded

От:
Greg Stark <gsstark@mit.edu>
Дата:

Dennis Björklund  writes:

> I would rather have long commands so one can write
> 
> \describe_table foo

I would think it would be better to keep everything under a single command and
have a 1-1 correspondence to \d. Ie, just add a long form syntax following the
existing \d. \d would become just an obvious set of abbreviations. 

So for example:

\describe table foo => \dt foo
\describe index foo => \di foo
\describe aggregate foo => \da foo
\describe operator foo => \do foo

...

-- 
greg

Re: psql \d option list overloaded

От:
"William ZHANG" <uniware_at_zedware_dot_org@antispam.com>
Дата:
I think moving the \d and simliar features in psql
to SQL is a good idea. That will make the features
available in any client library. As for the syntax,
maybe a investigation is needed.

Re: psql \d option list overloaded

От:
Dennis Björklund <db@zigo.dhs.org>
Дата:
On Sat, 10 Jan 2004, Peter Eisentraut wrote:

> > 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.
> 
> What is wrong with
> 
> SELECT * FROM information_schema.tables;

The result is very hard to read since it's so much of it (try column
instead of tables). The \xx commands do some nice formatting you don't 
get from the above.

I would rather have long commands so one can write

\describe_table foo

and have the tab completion work for these of course (only for the long 
commands, the \dt and such does not belong in completion).

The information schema is nice, but it's not what I want to use at the 
prompt to view the content of the database.

-- 
/Dennis Björklund

Re: psql \d option list overloaded

От:
Dennis Bjorklund <db@zigo.dhs.org>
Дата:
On Sun, 11 Jan 2004, Peter Eisentraut wrote:

> Another problem with pushing psql's queries into the backend is that 
> much of the output that psql makes is not a single table.  Sometimes 
> there is more than one table, or the information is in the table 
> footers.

Yes, pushing the \xx commands into the server makes no sense to me at all.  

The commands in psql are very specific for psql. I don't see why you ever
want to do SHOW TABLES except at the command line in psql. If your
application wants to find all tables in the database, then we have the
standard sql way, which is the information schema.

The argument that "show tables" is easier to remember then \dt might be 
true, but to me that just means that we should make psql better by adding 
\describe_table and such, not to push psql code into the server.

Making a couple of views that are pg specific to make it easier to get
information out could be good however. The information schema does not
always contain all information one might want. Making specialised SQL
commands for it I'm not in favor of at all.

-- 
/Dennis Björklund

Re: psql \d option list overloaded

От:
Jon Jensen <jon@endpoint.com>
Дата:
On Sat, 10 Jan 2004, Tom Lane wrote:

> ISTM there are three fundamental problems with \d and friends:
> 
> 	1. Some people have a hard time remembering the commands.
> 	2. Some people aren't using psql.
> 	3. psql keeps breaking across backend versions because the
> 	   needed commands change.
> 
> I don't see a lot of value in addressing just one of these problem
> areas, when we could instead do something that addresses all three.

I agree, at least for #2 and #3. But I just don't understand #1. Anything
is hard to remember when you're just starting to learn it. But it's still
faster to type \?  then \dt than it is to type "show tables". And
"show tables" is hard (relatively speaking) for me to remember because I'm
used to psql's way of doing things, since I mostly use it.

Jon
FAQ