Re: [HACKERS] SHOW TABLES
Guillaume Lelarge írta: Le 15/07/2010 17:48, Joshua D. Drake a écrit : On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. And would you add the complete syntax? I mean: SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] I'm wondering what one can do with the [FROM db_name] clause :) I think it's related to making this work: SELECT * FROM db.schema.table; Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Tue, Sep 21, 2010 at 4:52 AM, Boszormenyi Zoltan z...@cybertec.at wrote: I think it's related to making this work: SELECT * FROM db.schema.table; Which is a non-starter, I think. Every function in the system that thinks an OID uniquely identifies a database object would need to modified, or else you'd need unique indices that can span tables in multiple different databases. It would also require blowing a massive hole in the isolation wall between databases, and reengineering of every place that thinks a backend can be connected to only one database at a time. None of which would be good for either code stability or performance. The only way I can imagine making this work is if any references of that type got treated like foreign tables: spawn a helper backend connected to the correct DB (failing if you haven't permissions), and then stream the tuples back to the main backend from there. Considering the amount of effort that would be required for the amount of benefit you'd actually derive from it, I doubt anyone is likely to tackle this any time soon... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Robert Haas wrote: On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: 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 am concerned that implementing a command syntax to show complex output like above effectively means re-implementing a subset of SQL, and that subset will never be as flexible. That's a reasonable concern, but I don't have a better idea. Do you? Sorry for the late reply. If we are going to end up recreating SQL, we might as well just keep the backslash mess we have, or tell them to use SQL for the complex queries. My point was that we might find that what we cook up is as complex as what we have now. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Mon, 2010-08-09 at 17:42 -0400, Bruce Momjian wrote:
Robert Haas wrote:
On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian br...@momjian.us wrote:
Robert Haas wrote:
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 am concerned that implementing a command syntax to show complex output
like above effectively means re-implementing a subset of SQL, and that
subset will never be as flexible.
That's a reasonable concern, but I don't have a better idea. Do you?
Sorry for the late reply. If we are going to end up recreating SQL, we
might as well just keep the backslash mess we have, or tell them to use
SQL for the complex queries. My point was that we might find that what
we cook up is as complex as what we have now.
My proposal is that SHOW TABLES returns exactly the same output as \d
yet works the same from every interface.
I have no intention of designing or writing what Robert proposes above
and he is welcome to do that, but its clearly a different requirement.
SHOW TABLES is a simple command with a simple purpose: helping newbies
by putting obvious commands in their way that do useful things. The
simple goal I've outlined for SHOW TABLES has nothing whatsoever to do
with inventing what appears to be a new flexible catalog metadata
language that is hard to implement, non-standard and poorly understood
except by only a few people.
Currently, \d runs this SQL
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,
pg_catalog.pg_get_userbyid(c.relowner) as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname 'pg_catalog'
AND n.nspname 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
I would make this into a server view, and then make SHOW TABLES a
synonym for SELECT * FROM pg_show_backslash_d. I'd probably work out a
better name for the view also. It's mostly just refactoring.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Mon, Aug 9, 2010 at 5:42 PM, Bruce Momjian br...@momjian.us wrote: Sorry for the late reply. If we are going to end up recreating SQL, we might as well just keep the backslash mess we have, or tell them to use SQL for the complex queries. My point was that we might find that what we cook up is as complex as what we have now. I think that would require malice aforethought. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Kevin Grittner wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: On 07/18/2010 08:58 PM, Andres Freund wrote: I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? exactly my thoughts - but as I said earlier maybe this is actually an opportunity to look at newsysviews again? I can't picture anything which could be done with views which would allow me to issue one statement and see everything of interest about a table (etc.). You know: tablespace, owner, permissions, columns, primary key, foreign keys, check constraints, exclusion constraints, ancestor tables, child tables, and whatever interesting features I missed or we later add. Other products allow that to be generated server-side, so that it is available to any and all clients. I think we should join the crowd in this respect. Consider if the server-side description comes to the client unformatted, then that format is going to changes as Postgres adds features, and that might really make the output useless except for raw display purposes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Bruce Momjian br...@momjian.us wrote: Kevin Grittner wrote: I can't picture anything which could be done with views which would allow me to issue one statement and see everything of interest about a table (etc.). You know: tablespace, owner, permissions, columns, primary key, foreign keys, check constraints, exclusion constraints, ancestor tables, child tables, and whatever interesting features I missed or we later add. Other products allow that to be generated server-side, so that it is available to any and all clients. I think we should join the crowd in this respect. Consider if the server-side description comes to the client unformatted, then that format is going to changes as Postgres adds features, and that might really make the output useless except for raw display purposes. Yeah, sending it back as unformatted text would be horrible. See this post: http://archives.postgresql.org/pgsql-hackers/2010-07/msg00738.php If you follow the link there, you will see examples of another product returning up to 11 result sets and a few INFO level messages in response to a single sp_help objectname. If it's technically feasible for PostgreSQL to do something like that, it would absolutely rock. Result sets have enough structure to them to be able to write code dealing with such behavior with relative confidence. For more naive ramblings by me on the topic, see: http://archives.postgresql.org/pgsql-hackers/2010-07/msg00752.php http://archives.postgresql.org/pgsql-hackers/2010-07/msg00892.php http://archives.postgresql.org/pgsql-hackers/2010-07/msg00909.php Nobody has yet come out and said that these ideas *aren't* technically feasible, so I continue to live with my dreams intact. So far -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 1. \d isn't exactly the most intuitive thing ever Seems fairly mnemomic to me (d=describe) and it packs a *lot* of information into a single letter (see below). Things that are done often should have short keystrokes, and not require learning Yet Another Meta-Language. And it's pretty clear that we have been heading into some increasingly cryptic bits of fruit salad of \dfzb+-meta-bucky-alt-foo No arguments there, but that's the nature of the beast. I don't think it's as bad as is made out, however, as \d covers 99% of everyday usage and certainly the show tables that started this thread. Having SHOW THIS and SHOW THAT which are a bit more readily guessed would be somewhat nice. I'm not sure why easily guessed is thrown out in this thread as such a great thing. To achieve that goal, we simply need the help system that has been proposed many times: entering in SHOW anything gives you a quick rundown of the backslash system. As far as SHOW THIS, there is a big difference from a plain \dt and \d tablename. The former could be emulated quite easily with a SHOW command (although even our \dt prints out more information than mysql's SHOW TABLES), but the latter includes a crazy amount of information that would lead to quite a large SHOW... statement. Also, if it were made a server-side thing, how would you return things like indexes on a table in a SRF? Have a meta-column describing what the other columns represent? Ugly. information_schema doesn't have some useful things that we'd like ait to have ... Alas, I don't see a good way to improve on this :-( newsysviews seems the way out of that particular mess. I'm also not particularly opposed to adding new views or columns to information_schema. We would still support the standard by having all the required views and columns. The \? commands are *solely* for psql, and it would be nice to have the Improvement work on server side so it's not only usable with the one client. Agreed, but is there some other command-line client? If it's not command-line, free-form SQL typing, it inevitably already has support for querying the catalogs built in. At least, every GUI, app, and driver I can think of does. I've seen too many QA scripts that do awk parsing of output of psql \d commands that are vulnerable to all kinds of awfulness. They should be querying information_schema. I'd sure like to be able to write queries that *don't* involve array smashing or using grep on \z output to analyze object permissions. Yeah, that would be a better information_schema. :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007191011 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkxEXS0ACgkQvJuQZxSWSshLKwCffkfe0T3tELInxRqG7yCDS5Vr Ku8AoLUtOu7tTplGZZLPOEuDfKHt+EEm =Oubu -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Robert Haas (robertmh...@gmail.com) wrote: I think LIST COMMENTS ON SYSTEM AGGREGATES would be an epic step forward in usability. Perhaps. But it would behoove you to come up with a less er... arcane example. I've been using Postgres a long time, and I can count the number of times I've needed to see comments on system aggregates on my hand. With at least four fingers left over. ... 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. ... I don't know that I'd necessarily remember all those any better, and would certainly not enjoy typing out: LIST TEST SEARCH DICTIONARIES I don't have to remember \dFp - all I have to remember is \?. For the more common ones that I use day to day and don't have to look up (\d \dt \df \l etc.) the advantage of a two or three character string is strong. (There is some devil's advocate in there - a standard cross client (and dare I say it, cross RDBMS?) way would be nice) ... 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. Well, there's the rub. You're arguing this from a hacker's persepective, while the SHOW syntax seems to be overwhelmingly agreed upon to be either helpful for clueless noobs, or some nice syntactic sugar for average users. 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? Yep, agreed. Now, if we can just agree to put information_schema in the default search_path, because nobody enjoys having to type out information_schema... - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007191021 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkxEYDgACgkQvJuQZxSWSsikFwCdGo88Ehdcm8OHi2+VxISTG60Y b9sAoLsetxcpdMSconsCwj+3Xa1fCCzo =3aM1 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Kevin Grittner wrote: Any solution which only works within psql isn't a solution for a large part of the problem space people are trying to address. One important goal is that if someone spends a day to whip up a GUI query tool (as I did when I first started working in Java), it's easy to get displays like we get from the psql backslash commands (as it was in Sybase, which is what we were using at the time, through sp_help and related stored procedures). I don't agree that this is an important goal. Certainly someone writing a GUI (or a new driver) should be expected to be familiar with the system catalogs. Moreover, a GUI relies on an underlying driver, and every driver should already be providing things like a list of tables natively. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007191030 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkxEYZ0ACgkQvJuQZxSWSsiIlQCfdXDgTqletVez/r+pKHY4EcW6 QAsAoPLUmblzN2aNEw5DveHEav3XyB/K =TGq1 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Stephen Frost sfr...@snowman.net wrote: You think that the users of the libpq() interface (or even the protocol itself) are going to handle getting \dt-type output back somehow..? If you look back in the thread, you'll see that I admitted my ignorance of whether this could be properly implemented in the back end without a protocol change. Ignorance being bliss, I can revel in the dreams of *having* such a feature without being dragged down by the potential pain of its implementation. ;-) I know, though, that the JDBC spec supports such things -- you can keep pulling ResultSet objects off the wire, each with its own distinct set of columns. (That is, each ResultSet has its own ResultSetMetaData which specifies how many columns that particular ResultSet has, what the column names are, what the data type is for each column, etc. Each ResultSet returned from a response stream for a request can be entirely different in all of these characteristics.) As what, a single-column result of type text? No, that would be horrible. That has been mentioned as a possibility, and it makes me shudder. And then they'll use non-fixed-width fonts, undoubtably, which means the results will end up looking rather ugly, even if we put in the effort to format the results. With, for example, Sybase's sp_help, each result set can be listed any way the client chooses -- I've seen it put into character format like the psql \d commands, I've seen each result set put into a table for brower-based query tools, and I've seen each result set put into a JTable for Java Swing applications. If a client gets back a series of result sets, the sky is the limit. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: I know, though, that the JDBC spec supports such things -- you can keep pulling ResultSet objects off the wire, each with its own distinct set of columns. (That is, each ResultSet has its own ResultSetMetaData which specifies how many columns that particular ResultSet has, what the column names are, what the data type is for each column, etc. Each ResultSet returned from a response stream for a request can be entirely different in all of these characteristics.) I'm pretty sure we don't have the ability to support that at either the libpq or the protocol level today, but in general I do think it's a good idea and would be good to support. To be honest, I seem to recall someone talking about working on it (or perhaps it's on the TODO?). In any case, long way there from here. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
On Mon, Jul 19, 2010 at 10:25 AM, Greg Sabino Mullane g...@turnstep.com wrote: 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. ... I don't know that I'd necessarily remember all those any better, and would certainly not enjoy typing out: LIST TEST SEARCH DICTIONARIES I don't have to remember \dFp - all I have to remember is \?. For the more common ones that I use day to day and don't have to look up (\d \dt \df \l etc.) the advantage of a two or three character string is strong. I don't think anyone is proposing getting rid of backslash commands. That would be nuts. What's being proposed is to try to create a better way to list objects, a way that involves some server-side support so that clients don't need to muck about with system catalogs quite so much. I like psql as well as anyone, but saying that it's easy to do this stuff because you can use \? to get the appropriate backslash command seems to me to be missing the point. Suppose you regularly use PGadmin to access the database, or some other graphical client, and you want to find a query to list the comments on every item in the database. Good luck! You'll need to figure out how to use psql, discover that it has a -E switch (of which I was ignorant for my first 10 years of using PostgreSQL), and get the query out of there. Then you'll find that the query psql uses is more than 50 lines long and also wrong: it omits half the object types. Woohoo! And even supposing that you fix the query, there's no guarantee that it won't be wrong again when PG version X+1 comes out. Take a look at describe.c. It's riddled with special cases for particular PG versions, special cases that must be replicated in every other client that wants to work with multiple PG versions. So, basically, our advice to anyone who wants a simple, portable way to list objects of particular types in a cross-PG version compatible way is - copy the logic in describe.c, adapt it to your application, and update it every time a new major release comes out. Is that really the best we can do, and do we really think that's adequate? 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. Well, there's the rub. You're arguing this from a hacker's persepective, while the SHOW syntax seems to be overwhelmingly agreed upon to be either helpful for clueless noobs, or some nice syntactic sugar for average users. I use the darn database, too. The machinations I've gone through to get some of the information are ridiculously complex. As Larry Wall one said, a good programming language should make simple things simple and complicated things possible; so, I don't believe that having a simple interface and a powerful interface are mutually exclusive goals. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Mon, Jul 19, 2010 at 02:12:19PM -, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 1. \d isn't exactly the most intuitive thing ever Seems fairly mnemomic to me (d=describe) and it packs a *lot* of information into a single letter (see below). Things that are done often should have short keystrokes, and not require learning Yet Another Meta-Language. And it's pretty clear that we have been heading into some increasingly cryptic bits of fruit salad of \dfzb+-meta-bucky-alt-foo No arguments there, but that's the nature of the beast. I don't think it's as bad as is made out, however, as \d covers 99% of everyday usage and certainly the show tables that started this thread. It covers 0% of cases where people are not using psql. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Mon, 2010-07-19 at 10:23 -0700, David Fetter wrote: On Mon, Jul 19, 2010 at 02:12:19PM -, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 1. \d isn't exactly the most intuitive thing ever Seems fairly mnemomic to me (d=describe) and it packs a *lot* of information into a single letter (see below). Things that are done often should have short keystrokes, and not require learning Yet Another Meta-Language. And it's pretty clear that we have been heading into some increasingly cryptic bits of fruit salad of \dfzb+-meta-bucky-alt-foo No arguments there, but that's the nature of the beast. I don't think it's as bad as is made out, however, as \d covers 99% of everyday usage and certainly the show tables that started this thread. It covers 0% of cases where people are not using psql. Which is probably 85% of our users. (No I have no actual metric) Every single corp I have walked into is using at least PgAdmin as well. Until this project as a whole recognizes that for the majority to the populace the command line is dead; we will continue to have these arguments for no apparent reason but to make sure spam filters are still reading our emails. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 David Fetter wrote: No arguments there, but that's the nature of the beast. I don't think it's as bad as is made out, however, as \d covers 99% of everyday usage and certainly the show tables that started this thread. It covers 0% of cases where people are not using psql. Yes, and everything else already has a show tables. See for example, PPA: http://phppgadmin.sourceforge.net/images/4.png - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007191342 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkxEj4kACgkQvJuQZxSWSshrwgCg65eIziE2SW8XhdTSHwVMzxnm ynIAoLPOc0yuKyrE2kaaJFq5UiDb45Nd =veva -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Mon, Jul 19, 2010 at 09:31:06AM -0500, Kevin Grittner wrote: Stephen Frost sfr...@snowman.net wrote: You think that the users of the libpq() interface (or even the protocol itself) are going to handle getting \dt-type output back somehow..? If you look back in the thread, you'll see that I admitted my ignorance of whether this could be properly implemented in the back end without a protocol change. Ignorance being bliss, I can revel in the dreams of *having* such a feature without being dragged down by the potential pain of its implementation. ;-) I know, though, that the JDBC spec supports such things -- you can keep pulling ResultSet objects off the wire, each with its own distinct set of columns. (That is, each ResultSet has its own ResultSetMetaData which specifies how many columns that particular ResultSet has, what the column names are, what the data type is for each column, etc. Each ResultSet returned from a response stream for a request can be entirely different in all of these characteristics.) Would something like this do? Thanks to Andrew Gierth for helping me figure out how to get this working :) CREATE OR REPLACE FUNCTION multi_result() RETURNS SETOF REFCURSOR LANGUAGE plpgsql AS $$ DECLARE r RECORD; ref REFCURSOR; BEGIN FOR r IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'information_schema' LOOP ref := 'multi_result_' || quote_ident(r.table_name); OPEN ref FOR EXECUTE 'SELECT * FROM information_schema.' || quote_ident(r.table_name); /* Not really needed. */ RETURN NEXT ref; ref := NULL; END LOOP; RETURN; END; $$; BEGIN; SELECT * FROM multi_result(); FETCH FORWARD ALL FROM multi_result_views; ROLLBACK; As what, a single-column result of type text? No, that would be horrible. That has been mentioned as a +1 on the shuddering. Add also nausea. :P And then they'll use non-fixed-width fonts, undoubtably, which means the results will end up looking rather ugly, even if we put in the effort to format the results. With, for example, Sybase's sp_help, each result set can be listed any way the client chooses -- I've seen it put into character format like the psql \d commands, I've seen each result set put into a table for brower-based query tools, and I've seen each result set put into a JTable for Java Swing applications. If a client gets back a series of result sets, the sky is the limit. Ad astra per PostgreSQL! Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
David Fetter da...@fetter.org wrote: Would something like this do? Thanks to Andrew Gierth for helping me figure out how to get this working :) CREATE OR REPLACE FUNCTION multi_result() RETURNS SETOF REFCURSOR With appropriate tweaks to JDBC and the other drivers, this would cover a lot of ground. You might be able to cover the last little bit by returning a SETOF some record with (at least) three columns, one of which would be filled in each row: REFCURSOR (for a result set), INTEGER (for a row count), and something which could carry an object which would map to a SQLWarning (which can be used with SQLSTATE '0' to deliver informational text or '01xxx' for actual warnings). A JDBC execute request (as opposed to executeUpdate or executeQuery) may get back any combination of the above in an ordered fashion. Essentially, this meta result set would need to be hidden within the Statement object. http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/Statement.html#execute%28java.lang.String%29 http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/Statement.html#getWarnings%28%29 -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Hi, Le 18 juil. 2010 à 05:41, Robert Haas a écrit : On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian br...@momjian.us wrote: I am concerned that implementing a command syntax to show complex output like above effectively means re-implementing a subset of SQL, and that subset will never be as flexible. That's a reasonable concern, but I don't have a better idea. Do you? I think that SHOW could be some syntax sugar atop the current rewrite rules system. I mean it would be implemented by means of parametrized views. It could be that SQL only SRFs could do a better job at it. In both cases the idea is that we should be able to write SELECT like statements. SHOW TABLE foo; SHOW TABLES WHERE tablename ~ 'foo'; SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; The last one has an english like trick using ANY rather than ALL, but that's just for the bikesheding of it, and would list all tables with both a date and a time column. The trick is there because if you want the attributes to show up you're after enhancing the SHOW TABLE query, not the SHOW TABLES one. So what we'd need first is a series of named queries, which I think psql provides for. Then some technique to have them available both as plain and easy usage and in full SQL. I think the rewrite system is meant to allow that, I'm not sure if using views or pure SQL SRFs is better, in both cases the rewritten query has to provide arguments placeholders: if a VIEW, that's a WHERE clause, if a SRF, any number of named arguments. Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. And some other tools also? Your syntax also forgets that maybe I only need a subset of the information. I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Dimitri Fontaine dfonta...@hi-media.com wrote: So what we'd need first is a series of named queries, which I think psql provides for. Any solution which only works within psql isn't a solution for a large part of the problem space people are trying to address. One important goal is that if someone spends a day to whip up a GUI query tool (as I did when I first started working in Java), it's easy to get displays like we get from the psql backslash commands (as it was in Sybase, which is what we were using at the time, through sp_help and related stored procedures). While the four DBAs use psql heavily, the twenty-some programmers and the business analysts all use various GUI tools which either tie in to their normal environments (for example, eclipse) or are web based hacks which probably didn't take much more effort than the above-mentioned GUI hack which I used for about ten years. Backslash commands do them no good whatsoever, nor will any solution which requires psql. It would be nice if when I display information about a table or some other database object, I could copy from my psql session, paste it into an email, and they could replicate the behavior in squirrel (or whatever the heck else they happen to be running). -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund and...@anarazel.de wrote: On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. And some other tools also? Your syntax also forgets that maybe I only need a subset of the information. I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? Andres Do you have an alternative suggestion for emulating SHOW SCHEMAS SHOW TABLES DESC object? Make a user friendly interface is not easy, but it sure as heck is important. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 07/18/2010 08:58 PM, Andres Freund wrote: On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. And some other tools also? Your syntax also forgets that maybe I only need a subset of the information. I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? exactly my thoughts - but as I said earlier maybe this is actually an opportunity to look at newsysviews again? Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Hi, On Sunday 18 July 2010 21:02:59 Rob Wultsch wrote: On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund and...@anarazel.de wrote: On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. And some other tools also? Your syntax also forgets that maybe I only need a subset of the information. I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? Do you have an alternative suggestion for emulating SHOW SCHEMAS SHOW TABLES DESC object? I personally still fail to see the point of emulating it. Maybe building a short wrapper pointing to the docs or whatever. But thats not the point. Providing an easy wrapper is something I could agree without much problems (as it doesnt touch me). But starting several new toplevel commands which do not give everything (i.e. the ability to selectively use columns) but still want to provide a more or less complete query language and should be sensibly usable in subqueries et al - thats another thing. That would involve significant parts of the gram.y, some parts of the parse analysis and the executor for not enough benefit compared to the significant cost. Make a user friendly interface is not easy, but it sure as heck is important. From my pov making it easier to query the system (either through functions or views) is a worthwile goal though, dont misunderstand me. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: On 07/18/2010 08:58 PM, Andres Freund wrote: I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? exactly my thoughts - but as I said earlier maybe this is actually an opportunity to look at newsysviews again? I can't picture anything which could be done with views which would allow me to issue one statement and see everything of interest about a table (etc.). You know: tablespace, owner, permissions, columns, primary key, foreign keys, check constraints, exclusion constraints, ancestor tables, child tables, and whatever interesting features I missed or we later add. Other products allow that to be generated server-side, so that it is available to any and all clients. I think we should join the crowd in this respect. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Le 18 juil. 2010 à 21:00, Kevin Grittner a écrit : Dimitri Fontaine dfonta...@hi-media.com wrote: So what we'd need first is a series of named queries, which I think psql provides for. Any solution which only works within psql isn't a solution for a large part of the problem space people are trying to address. Exactly. It's all about having it in the backend, in an easy to share format. But what kind of facilities are we talking about? For me, those catalog queries psql already implements. I don't think we should offer \d or whatever in the backend as is, but the queries that \d uses should be a SHOW object away. Now this subthread is about having a hard coded facility or the full blown SQL atop. -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Le 18 juil. 2010 à 20:58, Andres Freund a écrit : On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... There are two questions here I think, really. First is about having meta-data queries in the backend, and we want that because we want it to be easy for everybody to have access to those, whether they choose to use psql or whatever else. Second is about why having SHOW be usable as if it where a real SQL query? That's because it's been said that people will certainly want to go further away using the facility. And now they want full SQL. So it seems to me we're now trying to catch 2 birds with a single 'SHOW' stone. -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Le 18 juil. 2010 à 21:21, Andres Freund a écrit : Providing an easy wrapper is something I could agree without much problems (as it doesnt touch me). But starting several new toplevel commands which do not give everything (i.e. the ability to selectively use columns) but still want to provide a more or less complete query language and should be sensibly usable in subqueries et al - thats another thing. That would involve significant parts of the gram.y, some parts of the parse analysis and the executor for not enough benefit compared to the significant cost. Agreed that wanting 'SHOW' commands to be full blown SQL is somewhat strange and pushing it. But people on the list wanted to gather ideas on how to do it before deciding its cost is higher than what it's worth, I guess. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sun, 2010-07-18 at 20:39 +0200, Dimitri Fontaine wrote: SHOW TABLE foo; Yes SHOW TABLES WHERE tablename ~ 'foo'; SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; For me, realistically, No. Simplifying SQL should be left to the SQL standards committee. It could certainly use a hand there, but its too big a mountain too climb, for me. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 07/18/2010 09:00 PM, Kevin Grittner wrote: Dimitri Fontainedfonta...@hi-media.com wrote: So what we'd need first is a series of named queries, which I think psql provides for. Any solution which only works within psql isn't a solution for a large part of the problem space people are trying to address. One important goal is that if someone spends a day to whip up a GUI query tool (as I did when I first started working in Java), it's easy to get displays like we get from the psql backslash commands (as it was in Sybase, which is what we were using at the time, through sp_help and related stored procedures). yeah but having to call a SP is basically the same as formulating a query - the point really is that it is completely up to the client to think of a suitable representation for the information and the interface for the user to select data. Just implementing something in the server that either shows everything (whatever that really is in practice) will very often not match to what the tool really wants. And once we are into providing something that can do arbitrary stuff like filtering or output manipulation we are back to where we are - issueing an SQL-query against the catalog. While the four DBAs use psql heavily, the twenty-some programmers and the business analysts all use various GUI tools which either tie in to their normal environments (for example, eclipse) or are web based hacks which probably didn't take much more effort than the above-mentioned GUI hack which I used for about ten years. Backslash commands do them no good whatsoever, nor will any solution which requires psql. It would be nice if when I display information about a table or some other database object, I could copy from my psql session, paste it into an email, and they could replicate the behavior in squirrel (or whatever the heck else they happen to be running). In that case you are not really using the tool per it's primary purpose (ie say a webgui that provides a graphical interpretation of something) but you are back to merely using it as an SQL-commandline client. I really doubt that there is any solution to the general problem as soon as you want filtering and related stuff - and if you only do the limited version people will soon come back and tell you it's not as flexible as was we had before (like backslash commands can do some limited filtering) or reimplementing SQL. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Hi Kevin, On Sunday 18 July 2010 21:24:25 Kevin Grittner wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: On 07/18/2010 08:58 PM, Andres Freund wrote: I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? exactly my thoughts - but as I said earlier maybe this is actually an opportunity to look at newsysviews again? I can't picture anything which could be done with views which would allow me to issue one statement and see everything of interest about a table (etc.). You know: tablespace, owner, permissions, columns, primary key, foreign keys, check constraints, exclusion constraints, ancestor tables, child tables, and whatever interesting features I missed or we later add. Other products allow that to be generated server-side, so that it is available to any and all clients. I think we should join the crowd in this respect. Such tables sure do not fit queries as in On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @ array['date'::regtype, 'time'::regtype]; At least I dont see any way how you could define aggregation or such sensibly here. Thats the part which scares me quite a bit. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Kevin, * Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: I can't picture anything which could be done with views which would allow me to issue one statement and see everything of interest about a table (etc.). You know: tablespace, owner, permissions, columns, primary key, foreign keys, check constraints, exclusion constraints, ancestor tables, child tables, and whatever interesting features I missed or we later add. You think that the users of the libpq() interface (or even the protocol itself) are going to handle getting \dt-type output back somehow..? As what, a single-column result of type text? And then they'll use non-fixed-width fonts, undoubtably, which means the results will end up looking rather ugly, even if we put in the effort to format the results. I'm becoming more and more inclined to just address this with newsysviews and encouraging use of the existing TABLE top-level command for people who have issue with 'SELECT *'. Other products allow that to be generated server-side, so that it is available to any and all clients. I think we should join the crowd in this respect. I could see some things being done this way, but the entire \dt output for a given table strikes me as stretching it pretty far.. And only doing it half-way doesn't strike me as a very good idea. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
On Jul 16, 2010, at 11:02 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.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. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Tim Landscheidt t...@tim-landscheidt.de wrote: One major flaw I see is that the fractional precision is fixed. Not only petrol stations split cents. Well, I've never paid a petrol station a fraction of a cent; I've only seen *rates* of money per some unit of measure with fractional cents. If you're being accurate about assigning types, a rate like that is no more money than speed is a distance. Likewise for everywhere else I can think of such fractional cents -- for example, hourly pay rates or tax mill rates on assessed value. These all (in my experience) are multiplied by a number in the unit of measure of the divisor to get a money amount without fractional cents before you do anything with actual *money*. While others may have had some contrary experience, I've worked with many types of businesses, non-profit organizations, and government agencies for 38 years, and can't recall having seen anywhere that what you describe would cause a problem, when the type is used correctly. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sat, 2010-07-17 at 09:02 -0500, Robert Haas wrote: On Jul 16, 2010, at 11:02 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.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. +1 JD ...Robert -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
2010/7/17 Joshua D. Drake j...@commandprompt.com: On Sat, 2010-07-17 at 09:02 -0500, Robert Haas wrote: On Jul 16, 2010, at 11:02 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.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. Every time I like psql from one reason, It is clean, what is SQL (server side) command and what is psql command (backslash command). So I am against to implement similar commands. Regards Pavel Stehule +1 JD ...Robert -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 07/17/2010 04:02 PM, Robert Haas wrote: On Jul 16, 2010, at 11:02 PM, Stephen Frostsfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.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. 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? 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? Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sat, 2010-07-17 at 23:30 +0200, Stefan Kaltenbrunner wrote: On 07/17/2010 04:02 PM, Robert Haas wrote: On Jul 16, 2010, at 11:02 PM, Stephen Frostsfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.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. By whose estimation? I hate the backslash commands and I have been using them longer than most. I do agree that the above is a bit verbose but it is also blatant as to what it is. 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? Really? Hmmm SELECT * FROM (where are system aggregates again?), oh right, pg_proc, what is the column that tells me it is a system aggregate? -- Do I filter by namespace? Oh, crimey, why can't I just type: SHOW COMMENTS ON SYSTEM AGGREGATES (or LIST) JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sat, Jul 17, 2010 at 5:30 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: On 07/17/2010 04:02 PM, Robert Haas wrote: On Jul 16, 2010, at 11:02 PM, Stephen Frostsfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.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
Re: [HACKERS] SHOW TABLES
Robert Haas wrote: 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 am concerned that implementing a command syntax to show complex output like above effectively means re-implementing a subset of SQL, and that subset will never be as flexible. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: 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 am concerned that implementing a command syntax to show complex output like above effectively means re-implementing a subset of SQL, and that subset will never be as flexible. That's a reasonable concern, but I don't have a better idea. Do you? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 21:57 -0400, Robert Haas wrote: Exactly which commands are we going to support? With exactly what syntax? What information will be returned by each command? In what format? We have no agreement on any of these points. The normal process is that we discuss the requirement for something, then design it, discuss it some more, then build it, then discuss it some more. For some things the process takes many years, for others it is short, which can be because of resource stalls from people involved or technical blockers etc.. Few things make it through. Fairly obviously we aren't far down the process yet; each of those stages takes time and effort. Some people skip the early stages of effort, which is why their patches ultimately go nowhere. Regrettably, I note that patch credit is given only for the later stages of development which doesn't encourage general appreciation of the total process. Not really sure why you wish to discuss general development processes, so probably worth starting a new thread if you have more to say. I see nothing special about this proposal with regards to dev process. Anyway, we seem to have established almost unanimous approval for the general requirement and so we move onto the next stage. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Jul 15, 2010, at 6:43 PM, Magnus Hagander wrote: On Thu, Jul 15, 2010 at 18:35, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Is there an actual common use-case for having these commands available for *non-psql* interfaces? There are many interfaces out there and people writing new ones everyday. We just wrote an interface for Android, for example. It is arguably *more* important to do this from non-psql interfaces. There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? i am not seeing this as an instead solution. this is an additional solution. SHOW TABLES etc could return a set of table. there is not need to change good of \d for that. it just a plain add on. everything else would be simply bad. many thanks, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Jul 15, 2010, at 11:18 PM, Josh Berkus j...@agliodbs.com wrote: I think it's very important, as Haas says, to consider that whatever we do in this arena, we'll be living with it forever, so let's not make the \dv vs. \df mistake again, ok? Refresh my memory? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
I have to agree with Simon here. \d is ridiculous for the common user. +1 Regards Markus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Andrew Dunstan wrote: Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? I think your assumption is questionable. Plenty of people use MySQL's SHOW TABLES in non-interactive settings (for good or ill). That's why any suggestion that we should return anything other than a resultset seems like a really terrible idea to me. If they are writing an application, finding the query to show all tables is the least of their problems. I don't see how SHOW TABLE significantly helps in that case, except make things 0.001% easier, while creating duplicate functionality in Postgres. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? I think your assumption is questionable. Plenty of people use MySQL's SHOW TABLES in non-interactive settings (for good or ill). That's why any suggestion that we should return anything other than a resultset seems like a really terrible idea to me. If they are writing an application, finding the query to show all tables is the least of their problems. I don't see how SHOW TABLE significantly helps in that case, except make things 0.001% easier, while creating duplicate functionality in Postgres. What would be interesting is if SHOW TABLES was psql-only, and showed the output in multi-column format, like ls -C. That would a a new display format and new useful functionality. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
2010/7/16 Bruce Momjian br...@momjian.us: Andrew Dunstan wrote: Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? I think your assumption is questionable. Plenty of people use MySQL's SHOW TABLES in non-interactive settings (for good or ill). That's why any suggestion that we should return anything other than a resultset seems like a really terrible idea to me. If they are writing an application, finding the query to show all tables is the least of their problems. I don't see how SHOW TABLE significantly helps in that case, except make things 0.001% easier, while creating duplicate functionality in Postgres. I am thinking same too. Maybe somebody will be happy, bacause they can to write SHOW TABLES, but they will be unsatisfied when will try to write SHOW TABLES WHERE ... so only full support of MySQL syntax has sense. Some only text version of SHOW TABLES command isn't sense for me - it can do only more problems with incompatibility. Still I thinking about top level hook - so these and similar commands can be implemented inside external modules. I have a different opinion on DESCRIBE command. This really can help. But it must not be a command. describe function is enought - select describe(oid); select describe_table(name); ... Regards Pavel Stehule -- Bruce Momjian br...@momjian.us http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 16 July 2010 13:49, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? I think your assumption is questionable. Plenty of people use MySQL's SHOW TABLES in non-interactive settings (for good or ill). That's why any suggestion that we should return anything other than a resultset seems like a really terrible idea to me. If they are writing an application, finding the query to show all tables is the least of their problems. I don't see how SHOW TABLE significantly helps in that case, except make things 0.001% easier, while creating duplicate functionality in Postgres. What would be interesting is if SHOW TABLES was psql-only, and showed the output in multi-column format, like ls -C. That would a a new display format and new useful functionality. -- The problem is people are stating different requirements. - to make it easy for new users of psql - to simplify fetching basic database information from any client application - to ease transition between MySQL and PostgreSQL The outcome would depend on what's needed. Like providing a pg_user_tables view for people to select from, using LIST TABLES as a more meaningful alternative to SHOW TABLES, providing hints for MySQL users using psql... etc. Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, 2010-07-16 at 14:07 +0100, Thom Brown wrote: The problem is people are stating different requirements. - to make it easy for new users of psql - to simplify fetching basic database information from any client application - to ease transition between MySQL and PostgreSQL Close, but I didn't state any of those as you have them. I want to make it easy for newbies to get access to obvious things like a list of tables, from *any* interactive application, wherever they exist. There are many and various apps and not all of them work the same. (The Windows installer ships two, for example). It would be nice to tell people just type SHOW TABLES and have it be true 100% of the time. They can remember that, or at least will try it if they can't remember anything at all about our RDBMS. Not trying to ease the transition between MySQL and PostgreSQL, it is about making things obvious for overworked sysadmins and DBAs. Many people are familiar with MySQL and many people use both. There are also dozens of legacy RDBMS for DBAs to remember: Sybase, DB2, Informix, Teradata, Ingres, MySQL and many others. Providing obvious commands that help people who have never connected or only connect sporadically would do much to help our cause. We are widely regarded as unhelpful, difficult to get started etc.. If we had a dollar for every person that has shouted OMG what is the damn command on Postgres? it would easily fund this development. This is not about simplifying things. It is about being obvious. Light switches are usually at shoulder height next to a door. Our light switches are 2 metres up, on the far side of the room. People are sick of banging their knees on furniture while trying to grope for the light. The light switch isn't so much hard to use, its just in the wrong place. We must envisage what it is to be a person that doesn't know where the switch is, or have forgotten. We don't need a programmable light switch API, or a multi-function light remote control. Just a switch by all of the doors. (Oh, they're probably not called lights outside UK; room lamps maybe?) -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Jul 16, 2010, at 8:11 AM, Simon Riggs wrote: On Fri, 2010-07-16 at 14:07 +0100, Thom Brown wrote: The problem is people are stating different requirements. - to make it easy for new users of psql - to simplify fetching basic database information from any client application - to ease transition between MySQL and PostgreSQL Close, but I didn't state any of those as you have them. I want to make it easy for newbies to get access to obvious things like a list of tables, from *any* interactive application, wherever they exist. There are many and various apps and not all of them work the same. (The Windows installer ships two, for example). It would be nice to tell people just type SHOW TABLES and have it be true 100% of the time. They can remember that, or at least will try it if they can't remember anything at all about our RDBMS. In pretty much any GUI application the expected way to see a list of tables is not going to involve typing anything anywhere. Either the list of tables is going to be shown all the time (common) or there'll be a menu or toolbar option to show them. There may not be anywhere obvious to type in a command, and if there is the output of a server-side implementation of show tables would likely be displayed like the contents of a table, rather than as names of tables - so all the metadata is going to be off. Things like the context menu for each row of the result having operations for modifying the contents of a table, rather than the operations for modifying a table. It'll offer DML operations where you'd expect, and want, DDL in other words. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 15:38 -0400, Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? My original thought was around the newbie experience: they connect to PostgreSQL and then nothing. No sensible commands work, typing help doesn't work, nor does typing quit. Few simple commands they've learnt elsewhere work either. We need a way to respond sensibly to common user input. Terminal program is the bit of thinking that is askew there. The question is what other non-psql interactive programs are there? Lots. There are many tools that can access Postgres. Some are libpq programs, though there are command line versions in every environment: java, python, etc.. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Simon Riggs wrote: On Thu, 2010-07-15 at 15:38 -0400, Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? My original thought was around the newbie experience: they connect to PostgreSQL and then nothing. No sensible commands work, typing help doesn't work, nor does typing quit. Few simple commands they've Well, help does work now, for some definition of work: $ psql test hpsql (9.1devel) Type help for help. test= help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit learnt elsewhere work either. We need a way to respond sensibly to common user input. Terminal program is the bit of thinking that is askew there. The question is what other non-psql interactive programs are there? Lots. There are many tools that can access Postgres. Some are libpq programs, though there are command line versions in every environment: java, python, etc.. Yeah, but do enough people use them to warrant putting this in the backend? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, Jul 16, 2010 at 11:44:58AM -0400, Bruce Momjian wrote: Simon Riggs wrote: On Thu, 2010-07-15 at 15:38 -0400, Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? My original thought was around the newbie experience: they connect to PostgreSQL and then nothing. No sensible commands work, typing help doesn't work, nor does typing quit. Few simple commands they've Well, help does work now, for some definition of work: $ psql test hpsql (9.1devel) Type help for help. test= help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit learnt elsewhere work either. We need a way to respond sensibly to common user input. Terminal program is the bit of thinking that is askew there. The question is what other non-psql interactive programs are there? Lots. There are many tools that can access Postgres. Some are libpq programs, though there are command line versions in every environment: java, python, etc.. Yeah, but do enough people use them to warrant putting this in the backend? Yes. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, 16 Jul 2010, Bruce Momjian wrote: There are many tools that can access Postgres. Some are libpq programs, though there are command line versions in every environment: java, python, etc.. Yeah, but do enough people use them to warrant putting this in the backend? I may have lost the gist of this question, but ... how can they use them if they don't exist? Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Marc G. Fournier wrote: On Fri, 16 Jul 2010, Bruce Momjian wrote: There are many tools that can access Postgres. Some are libpq programs, though there are command line versions in every environment: java, python, etc.. Yeah, but do enough people use them to warrant putting this in the backend? I may have lost the gist of this question, but ... how can they use them if they don't exist? Clarification, do enough people use non-psql command line tools to warrant putting this in the backend? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, Jul 16, 2010 at 12:04:01PM -0400, Bruce Momjian wrote: Marc G. Fournier wrote: On Fri, 16 Jul 2010, Bruce Momjian wrote: There are many tools that can access Postgres. Some are libpq programs, though there are command line versions in every environment: java, python, etc.. Yeah, but do enough people use them to warrant putting this in the backend? I may have lost the gist of this question, but ... how can they use them if they don't exist? Clarification, do enough people use non-psql command line tools to warrant putting this in the backend? Yes. Such backend stuff is in every RDBMS except ours. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Jul 16, 2010, at 9:09 AM, David Fetter wrote: Clarification, do enough people use non-psql command line tools to warrant putting this in the backend? Yes. Such backend stuff is in every RDBMS except ours. I admit that I had to do a *lot* of work to write the schema-testing functions for pgTAP. Getting information about functions is especially hairy (I poached a view from newsysviews to get what I needed). I'd love a cleaner way to get at this information. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
David Fetter wrote: On Fri, Jul 16, 2010 at 12:04:01PM -0400, Bruce Momjian wrote: Marc G. Fournier wrote: On Fri, 16 Jul 2010, Bruce Momjian wrote: There are many tools that can access Postgres. Some are libpq programs, though there are command line versions in every environment: java, python, etc.. Yeah, but do enough people use them to warrant putting this in the backend? I may have lost the gist of this question, but ... how can they use them if they don't exist? Clarification, do enough people use non-psql command line tools to warrant putting this in the backend? Yes. Such backend stuff is in every RDBMS except ours. Really? What are the other syntaxes? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote: Really? What are the other syntaxes? SHOW TABLES -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
* Simon Riggs si...@2ndquadrant.com [100716 12:24]: On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote: Really? What are the other syntaxes? SHOW TABLES Obviously, only for some $value of $other... The 3 database I have access to: [DataDirect][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'SHOW'. [ISQL]ERROR: Could not SQLExecute Error: near show: syntax error ERROR: unrecognized configuration parameter tables So it's obviously not universal... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
Simon Riggs wrote: On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote: Really? What are the other syntaxes? SHOW TABLES That is MySQL? Do does every other RDBMs also use that, as David suggested? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Bruce Momjian br...@momjian.us wrote: What are the other syntaxes? For Sybase ASE sp_help and other stored procedures, see: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1550/html/sprocs/X85190.htm Like \d, these server-side stored procedures can return a number of result sets. Like Robert, I'm skeptical of implementing a server-side solution for PostgreSQL which doesn't do the same. I'm not clear on whether that's even possible without a new version of wire protocol, though. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, 2010-07-16 at 12:25 -0400, Bruce Momjian wrote: Simon Riggs wrote: On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote: Really? What are the other syntaxes? SHOW TABLES That is MySQL? Do does every other RDBMs also use that, as David suggested? He didn't say it was exactly that syntax. We must retain some common sense in the discussion. DB2 uses LIST TABLES SQLServer and Sybase use sp_ procedures for this Informix uses INFO TABLES Ingres uses HELP and HELP TABLE foo Teradata uses SHOW TABLE foo but no syntax meaning all tables So I think David's actual response was appropriate and accurate: its a common thing to have easily guessable commands for this. Search Google for myfavouriteDBMS SHOW TABLES and you'll see that a lot of people look for and expect this kind of command to exist. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Jul 16, 2010, at 7:43 AM, Bruce Momjian br...@momjian.us wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? I think your assumption is questionable. Plenty of people use MySQL's SHOW TABLES in non-interactive settings (for good or ill). That's why any suggestion that we should return anything other than a resultset seems like a really terrible idea to me. If they are writing an application, finding the query to show all tables is the least of their problems. I don't see how SHOW TABLE significantly helps in that case, except make things 0.001% easier, while creating duplicate functionality in Postgres. Many years ago I needed to write a program that needed to be able to fetch a list of tables in the DB, and then a list of attributes for each table. It took me at least a full day and I almost gave up and abandoned PostgreSQL as a result. I think calling this duplicate functionality is ridiculous. Sure, it's possible. In fact, it's very easy. For committers. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas robertmh...@gmail.com wrote: For committers. Perhaps this discussions should be moved to the General list in order to poll the userbase. My .02 is that SHOW commands (even if they are not compatible) would make it much easier for me to make an argument to my boss to at least consider moving off another open source database. The show commands are in *very* widespread use by the MySQL community even after ~5 years of having the i_s. The Drizzle team (a radical fork of MySQL) very briefly considered removing the SHOW commands and the unanimous objections that followed caused that idea to scrapped. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, 16 Jul 2010, Simon Riggs wrote: SQLServer and Sybase use sp_ procedures for this Haven't experienced Sybase for 2 years in my last job, I can tell you that the sp_* commands are definitely non-intuitive :( Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
si...@2ndquadrant.com (Simon Riggs) writes: Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. If we're opening up the code to change this, it makes a lot of sense to try to Do It Really Right so that we're not going over this again and again. I think we're seeing several things that suck, and I'm quite sure I have not yet heard an answer that resolves it all. Things that have become clear: 1. \d isn't exactly the most intuitive thing ever And it's pretty clear that we have been heading into some increasingly cryptic bits of fruit salad of \dfzb+-meta-bucky-alt-foo Having SHOW THIS and SHOW THAT which are a bit more readily guessed would be somewhat nice. 2. information_schema doesn't have some useful things that we'd like it to have Listing databases would be nice. Unfortunately, ANSI didn't define a way to do that, so we can't add it. Alas, I don't see a good way to improve on this :-( 3. The \? commands are *solely* for psql, and it would be nice to have the Improvement work on server side so it's not only usable with the one client. 4. It would be Mighty Useful for whatever extensions get defined server-side to also be relational so that they can be usefully scripted in ways NOT vulnerable to screen size, output hackery, and such. - I've seen too many QA scripts that do awk parsing of output of psql \d commands that are vulnerable to all kinds of awfulness. Add an updated-on column to the output, and suddenly everything breaks. - I'd sure like to be able to write queries that *don't* involve array smashing or using grep on \z output to analyze object permissions. - \? output is often *not* amenable to this, as it sometimes has extra bits of data hierarchy in it. And array aggregation. There's a certain risk of things being overspecified such that there's *no* solution, but I don't think that forcibly *has* to happen. But the answers I'm seeing thus far run slipshod across too many of these things, so I don't see that we have arrived at actual solutions yet. -- let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];; http://linuxfinances.info/info/linuxdistributions.html Rules of the Evil Overlord #77. If I have a fit of temporary insanity and decide to give the hero the chance to reject a job as my trusted lieutentant, I will retain enough sanity to wait until my current trusted lieutenant is out of earshot before making the offer. http://www.eviloverlord.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Marc G. Fournier scra...@hub.org wrote: Haven't experienced Sybase for 2 years in my last job, I can tell you that the sp_* commands are definitely non-intuitive :( In general, I'd agree; although I think I got used to them about as fast as the PostgreSQL backslash commands. In the particular case of sp_help I would disagree; once you've heard that, it's pretty easy to remember and it works for tables, views, stored procedures, logs, rules, defaults, triggers, referential constraints, encryption keys, and check constraints. You type: sp_help pretty-much-any-database-object And you get information back which is both reasonably human-digestable based on the formatting of result sets in whatever client you're using, and reasonably machine-digestable based on looking at the column headers of the result sets. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Chris Browne wrote: - I'd sure like to be able to write queries that *don't* involve array smashing or using grep on \z output to analyze object permissions. The \z output is an embarrassment, no question about it in my mind. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 16/07/10 20:11, Rob Wultsch wrote: On Fri, Jul 16, 2010 at 9:56 AM, Robert Haasrobertmh...@gmail.com wrote: For committers. Perhaps this discussions should be moved to the General list in order to poll the userbase. My .02 is that SHOW commands (even if they are not compatible) would make it much easier for me to make an argument to my boss to at least consider moving off another open source database. The show commands are in *very* widespread use by the MySQL community even after ~5 years of having the i_s. The Drizzle team (a radical fork of MySQL) very briefly considered removing the SHOW commands and the unanimous objections that followed caused that idea to scrapped. That's for MySQL. I come from a DB2 background, and when I started using psql years ago, I often typed LIST TABLES without thinking much about it. Not SHOW TABLES, but LIST TABLES. I bet Oracle users coming to PostgreSQL will try DESC. Not SHOW TABLES. As Simon listed, every DBMS out there has a different syntax for this. I have nothing against SHOW TABLES (it might cause conflicts in grammar though), but if we're going to cater to people migrating from MySQL, I feel we should cater to people migrating from other products too. But surely we're not going to implement 10 different syntaxes for the same thing! We could, however, give a hint in the syntax error in all those cases. That way we're not on the hook to maintain them forever, and we will be doing people a favor by introducing them to the backslash commands or information schema, which are more powerful. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, Jul 16, 2010 at 10:52 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 16/07/10 20:11, Rob Wultsch wrote: On Fri, Jul 16, 2010 at 9:56 AM, Robert Haasrobertmh...@gmail.com wrote: For committers. Perhaps this discussions should be moved to the General list in order to poll the userbase. My .02 is that SHOW commands (even if they are not compatible) would make it much easier for me to make an argument to my boss to at least consider moving off another open source database. The show commands are in *very* widespread use by the MySQL community even after ~5 years of having the i_s. The Drizzle team (a radical fork of MySQL) very briefly considered removing the SHOW commands and the unanimous objections that followed caused that idea to scrapped. That's for MySQL. I come from a DB2 background, and when I started using psql years ago, I often typed LIST TABLES without thinking much about it. Not SHOW TABLES, but LIST TABLES. I bet Oracle users coming to PostgreSQL will try DESC. Not SHOW TABLES. As Simon listed, every DBMS out there has a different syntax for this. I have nothing against SHOW TABLES (it might cause conflicts in grammar though), but if we're going to cater to people migrating from MySQL, I feel we should cater to people migrating from other products too. But surely we're not going to implement 10 different syntaxes for the same thing! We could, however, give a hint in the syntax error in all those cases. That way we're not on the hook to maintain them forever, and we will be doing people a favor by introducing them to the backslash commands or information schema, which are more powerful. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com desc[ribe] also works in MySQL. Perhaps describe would be a good option: describe tables describe table table name (or perhaps descrive object?) describe schemas etc -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, 2010-07-16 at 20:52 +0300, Heikki Linnakangas wrote: On 16/07/10 20:11, Rob Wultsch wrote: On Fri, Jul 16, 2010 at 9:56 AM, Robert Haasrobertmh...@gmail.com wrote: For committers. Perhaps this discussions should be moved to the General list in order to poll the userbase. My .02 is that SHOW commands (even if they are not compatible) would make it much easier for me to make an argument to my boss to at least consider moving off another open source database. The show commands are in *very* widespread use by the MySQL community even after ~5 years of having the i_s. The Drizzle team (a radical fork of MySQL) very briefly considered removing the SHOW commands and the unanimous objections that followed caused that idea to scrapped. That's for MySQL. I come from a DB2 background, and when I started using psql years ago, I often typed LIST TABLES without thinking much about it. Not SHOW TABLES, but LIST TABLES. I bet Oracle users coming to PostgreSQL will try DESC. Not SHOW TABLES. As Simon listed, every DBMS out there has a different syntax for this. Agreed I have nothing against SHOW TABLES ...but SHOW wins, based on numbers of people expecting that (it might cause conflicts in grammar though) We don't have t handle it in the grammar. There are no parameters called tables, databases etc , but if we're going to cater to people migrating from MySQL, I feel we should cater to people migrating from other products too. But surely we're not going to implement 10 different syntaxes for the same thing! We could, however, give a hint in the syntax error in all those cases. That's a very sensible suggestion, we should give a hint for all common commands SHOW, LIST, etc., even though we pick just one to implement. That way we're not on the hook to maintain them forever, and we will be doing people a favor by introducing them to the backslash commands That's a sentence I never thought to see written down or information schema, which are more powerful. and this in no way detracts from that power and standardisation. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 16/07/10 21:32, Simon Riggs wrote: On Fri, 2010-07-16 at 20:52 +0300, Heikki Linnakangas wrote: I have nothing against SHOW TABLES ...but SHOW wins, based on numbers of people expecting that I'm not sure I buy that, but even if it's true, it doesn't seem fair to do a favor to one group of users, leaving the rest stranded and excluded forever. Even if SHOW TABLES has a bigger mind-share than the others, surely the others are not negligible either. , but if we're going to cater to people migrating from MySQL, I feel we should cater to people migrating from other products too. But surely we're not going to implement 10 different syntaxes for the same thing! We could, however, give a hint in the syntax error in all those cases. That's a very sensible suggestion, we should give a hint for all common commands SHOW, LIST, etc., even though we pick just one to implement. I'm suggesting that we should just add the hint for all of those and be done with it. doing people a favor by introducing them to the backslash commands That's a sentence I never thought to see written down :-). They're not that bad IMHO. \d is short, which is nice. \d and \df are the commands I routinely use and remember, for anything more advanced I have to resort to \h. The SHOW TABLES command wouldn't do more than that anyway. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Simon Riggs si...@2ndquadrant.com wrote: [...] Light switches are usually at shoulder height next to a door. Our light switches are 2 metres up, on the far side of the room. People are sick of banging their knees on furniture while trying to grope for the light. The light switch isn't so much hard to use, its just in the wrong place. We must envisage what it is to be a person that doesn't know where the switch is, or have forgotten. We don't need a programmable light switch API, or a multi-function light remote control. Just a switch by all of the doors. (Oh, they're probably not called lights outside UK; room lamps maybe?) Wow, the British must have shrunk a lot since my last vis- it - here light switches are mounted not more than 105 cm from the floor :-) (barrier-free not more than 85 cm). I guess the problem shown by others in this thread is that there doesn't seem to be a usually with regard to \d equivalents either. Tim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, 2010-07-16 at 19:32 +0100, Simon Riggs wrote: That's a very sensible suggestion, we should give a hint for all common commands SHOW, LIST, etc., even though we pick just one to implement. That way we're not on the hook to maintain them forever, and we will be doing people a favor by introducing them to the backslash commands That's a sentence I never thought to see written down No kidding. We are not helping users by introducing them to \d commands. I will repeat what I said at the beginning of this postgres vs. postgresql thread: Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. The discussions of \ commands and psql are irrelevant to this thread. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 17 July 2010 07:26, Joshua D. Drake j...@commandprompt.com wrote: Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. postgres=# SHOW ME THE MONEY; WARNING: THE MONEY is deprecated in this version of Postgres and may be discarded in a future version HINT: Use SHOW ME THE NUMERIC with the desired precision instead. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote: On 17 July 2010 07:26, Joshua D. Drake j...@commandprompt.com wrote: Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. postgres=# SHOW ME THE MONEY; WARNING: THE MONEY is deprecated in this version of Postgres and may be discarded in a future version HINT: Use SHOW ME THE NUMERIC with the desired precision instead. Funny, but no longer true: http://www.postgresql.org/docs/8.4/static/datatype-money.html (although I wish we would get rid of the type) JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
* Heikki Linnakangas (heikki.linnakan...@enterprisedb.com) wrote: I'm not sure I buy that, but even if it's true, it doesn't seem fair to do a favor to one group of users, leaving the rest stranded and excluded forever. Even if SHOW TABLES has a bigger mind-share than the others, surely the others are not negligible either. Have to say that I don't believe we're under any obligation to be fair to the users of various other RDBMS'. I hate MySQL with a passion, and originally came from an Oracle background, but I have to say that 'show tables;' makes a heck of alot more sense to me than 'desc'. I'm suggesting that we should just add the hint for all of those and be done with it. I do think it'd be useful to have a top-level set of 'show' commands. I agree with the others that the approach of saying well, if you just query pg_class joined against pg_namespace and filter out what you don't want, etc, etc, is way more complicated than it really needs to be. I can think of some applications where I would have actually used it (simple perl scripts and the like). I'm not sure how I feel about something like select * from (show tables) where table_name = 'blah';... :-). They're not that bad IMHO. \d is short, which is nice. \d and \df are the commands I routinely use and remember, for anything more advanced I have to resort to \h. The SHOW TABLES command wouldn't do more than that anyway. I don't find them all that bad either, really. I do find myself doing things like psql -c '\d'; in scripts and whatnot on occation, which isn't exactly ideal either. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
Joshua D. Drake j...@commandprompt.com wrote: On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote: postgres=# SHOW ME THE MONEY; WARNING: THE MONEY is deprecated in this version of Postgres and may be discarded in a future version HINT: Use SHOW ME THE NUMERIC with the desired precision instead. Funny, but no longer true: http://www.postgresql.org/docs/8.4/static/datatype-money.html (although I wish we would get rid of the type) I hadn't been aware it was ever deprecated. It has the advantage over numeric of using straight integer arithmetic for addition and subtraction, which are by far the most common operations on money, while allowing a decimal fraction without rounding problems. I'd been thinking about migrating our money columns to it (subject to some benchmarking first, to see how much it actually helped). It would seem odd for a database to tout its ability to deal with such data types as geometric shapes and global positioning, etc., which then didn't have such a common type as money. In my experience, many business applications deal with money. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Le 16 juil. 2010 à 18:42, Kevin Grittner a écrit : Like \d, these server-side stored procedures can return a number of result sets. Like Robert, I'm skeptical of implementing a server-side solution for PostgreSQL which doesn't do the same. I'm not clear on whether that's even possible without a new version of wire protocol, though. Well, I think we shouldn't mix it all. My view on that is that we need some easy simple commands in the backend, none of them on its own would mimic \d. Consider this psql command: psql -E -c '\d'. What I think is that each query you see there could easily become a SHOW subsyntax (from memory, we probably would have SHOW TABLE, SHOW INDEXES ON table, SHOW TRIGGERS ON table, SHOW CONSTRAINTS ON table, etc). Now, psql would be free to implement its \d in terms of those new queries rather than the full SQL ones it has now, that would be a good first client. Oh and that means the design is about all done already. And that we still are in the one command - one resultset interface. Meaning any libpq driver knows how to deal with the resultset, and that's not parsing text. I'm all with Simon here, it's not about offering any new capability that we don't already have, it's about having it handy from anywhere. So let's just have an easy syntax in the backend to do all the catalog 'magic' querying psql does, but one query at a time. Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Kevin Grittner kevin.gritt...@wicourts.gov wrote: postgres=# SHOW ME THE MONEY; WARNING: THE MONEY is deprecated in this version of Postgres and may be discarded in a future version HINT: Use SHOW ME THE NUMERIC with the desired precision instead. Funny, but no longer true: http://www.postgresql.org/docs/8.4/static/datatype-money.html (although I wish we would get rid of the type) I hadn't been aware it was ever deprecated. It has the advantage over numeric of using straight integer arithmetic for addition and subtraction, which are by far the most common operations on money, while allowing a decimal fraction without rounding problems. I'd been thinking about migrating our money columns to it (subject to some benchmarking first, to see how much it actually helped). It would seem odd for a database to tout its ability to deal with such data types as geometric shapes and global positioning, etc., which then didn't have such a common type as money. In my experience, many business applications deal with money. One major flaw I see is that the fractional precision is fixed. Not only petrol stations split cents. Tim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Fri, Jul 16, 2010 at 1:52 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: That's for MySQL. I come from a DB2 background, and when I started using psql years ago, I often typed LIST TABLES without thinking much about it. Not SHOW TABLES, but LIST TABLES. I bet Oracle users coming to PostgreSQL will try DESC. Not SHOW TABLES. As Simon listed, every DBMS out there has a different syntax for this. I have nothing against SHOW TABLES (it might cause conflicts in grammar though), but if we're going to cater to people migrating from MySQL, I feel we should cater to people migrating from other products too. But surely we're not going to implement 10 different syntaxes for the same thing! We could, however, give a hint in the syntax error in all those cases. That way we're not on the hook to maintain them forever, and we will be doing people a favor by introducing them to the backslash commands or information schema, which are more powerful. One advantage of using LIST is that LIST doesn't already mean something else, which would simplify the grammar handling. LIST [SYSTEM | ALL] any-object-type-in-plural-form DESCRIBE name-of-table Why must the backslash commands be more powerful than any alternative we might come up with? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
* Robert Haas (robertmh...@gmail.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. Consider 'standard' perl vs. perl w/ 'use English;'. The former is much more condesned and the latter is much more verbose. Which would you want to use on a daily basis and which would you like to have in an application someone else may have to support some day? Next question: how long do you really think you're going to be around? :) Stephen signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 15 July 2010 16:20, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Jul 15, 2010, at 5:20 PM, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services simon is absolutely right here. we should not mind being a little more user friendly in this area. many people are simply used to this kind of stuff. remember when you rejected something the last time (not necessarily software). was ist because you could not make it work in 2 min or was it because you did not like something else? do you reject buying a car because of a non obvious screw in the engine or because it somehow does not feel right? simon made an important point and i can simply agree - regardless of whether it has been discussed before or not. if you die a beautiful death you are still dead after all. regards, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, Jul 15, 2010 at 17:30, Thom Brown thombr...@gmail.com wrote: On 15 July 2010 16:20, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. (O.k. I will take Euros too). JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Thom Brown wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php This is not at all what Simon proposed. He wants to make it a backend command, not a psql command. I don't have a horse in the race, particularly. If we really want more utility commands, my preference would be to concentrate on those that are hard rather than those that could be easily done, e.g. a command that would give you the SQL necessary to create a given object. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Le 15/07/2010 17:48, Joshua D. Drake a écrit : On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. And would you add the complete syntax? I mean: SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] I'm wondering what one can do with the [FROM db_name] clause :) -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 15 July 2010 16:52, Andrew Dunstan and...@dunslane.net wrote: Thom Brown wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php This is not at all what Simon proposed. He wants to make it a backend command, not a psql command. My bad. But I find the following slightly odd: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. But I agree with the principal of improving usability. There's the issue of schema with SHOW TABLES though. It would either have to show tables and their associated schema in separate columns, or have an extended SHOW TABLES IN [SCHEMA] my_schema syntax. I personally think LIST object type makes more sense, although I guess the point is that SHOW would be familiar to MySQL defectors ;) Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 18:02 +0200, Guillaume Lelarge wrote: I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. And would you add the complete syntax? I mean: SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] I'm wondering what one can do with the [FROM db_name] clause :) Well I hadn't thought it out fully. I was just shutting down somebody elses idea that the feature had no legs. Which is obviously, not true. Let the discussion bloom :D JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
As a common user -- probably a bit more than that now -- I'd have to say my reaction to '\d' instead of 'SHOW DATABASES;' was more of a meh moment for me. Furthermore, '\d' is much quick to type than 'SHOW DATABASES;', and much less likely to suffer typos. As for '\d' not being memorable: It sure as heck is! I think the real problem here is that there's a little effort required in learning a new set of commands when switching from a competing database. 'SHOW . . . .' cannot be implemented in psql alone. It would have to supported in the backend. So that other drivers are able to understand it as well. If it implemented in psql only, we will be bombarded with I don't understand this! 'SHOW . . . .' works when I do it at the command line, but not in my script! WTF?' The best solution is to offer a hint to the user in psql when they submit 'SHOW . . . .' with a response like: SHOW . . . . is not a valid command. Perhaps you mean \d . . . . Sincerely, Aaron On Thursday 15 July 2010 11:48:39 Joshua D. Drake wrote: On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. Sincerely, Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 15 Jul 2010, Thom Brown wrote: If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. Personally, I think this is somethign that should go into the backend ... I'd like to be able to write perl scripts that talk to the backend without having to remember all the various system tables I need to query / join to get the same results as \d gives me in psql ... same for any interface language, really ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 15 July 2010 17:07, Marc G. Fournier scra...@hub.org wrote: On Thu, 15 Jul 2010, Thom Brown wrote: If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. Personally, I think this is somethign that should go into the backend ... I'd like to be able to write perl scripts that talk to the backend without having to remember all the various system tables I need to query / join to get the same results as \d gives me in psql ... same for any interface language, really ... Isn't that what the information_schema catalog is for? Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Jul 15, 2010, at 10:50 AM, Joshua D. Drake j...@commandprompt.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. Damn straight. I like \d as well as anyone but there are real problems with it. Perhaps when we add \dxrvbfqS$: we'll stop to reflect on what they are. Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 15 Jul 2010, Thom Brown wrote:
On 15 July 2010 17:07, Marc G. Fournier scra...@hub.org wrote:
On Thu, 15 Jul 2010, Thom Brown wrote:
If it's only a psql problem, why implement it as SQL? Is it just so we're
not adding keywords specifically to psql? In that case, it shouldn't
support QUIT.
Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend without
having to remember all the various system tables I need to query / join to
get the same results as \d gives me in psql ... same for any interface
language, really ...
Isn't that what the information_schema catalog is for?
I'd rather write:
SHOW TABLES;
then:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');
And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...
Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org
Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
