Re: [HACKERS] psql \d option list overloaded
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
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql \d option list overloaded
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
Re: [HACKERS] psql \d option list overloaded
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] psql \d option list overloaded
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] psql \d option list overloaded
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 \? CR 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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
Re: [HACKERS] psql \d option list overloaded
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? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql \d option list overloaded
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] psql \d option list overloaded
Dennis Björklund [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql \d option list overloaded
Greg Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql \d option list overloaded
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 \? CR 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] psql \d option list overloaded
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] psql \d option list overloaded
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.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] psql \d option list overloaded
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 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.
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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] psql \d option list overloaded
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql \d option list overloaded
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. leibnitz-mode I just independently had this idea, so I like it :-) /liebnitz-mode cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] psql \d option list overloaded
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql \d option list overloaded
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 table, 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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql \d option list overloaded
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'. 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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] psql \d option list overloaded
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] psql \d option list overloaded
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. 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 -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Engineer It's computationally FEROCIOUS. - Steve Jobs, discussing 64-bit computing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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. 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql \d option list overloaded
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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] psql \d option list overloaded
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] psql \d option list overloaded
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 -- [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
