Thread: SHOW TABLES
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 ... SHOW [FULL] <any object type> with identical meaning to psql's \d<?> syntax. Why? Because it will help people, most importantly, new people. It's similar enough to other systems to be useful and user friendly enough to be sensible. The command output will not mimic output from other systems. While I'm on the theme of "do the obvious", I'd also like to make psql recognise the word QUIT, in all cases. No, its not April 1, this is a serious and to 1000s of people an obvious thing to help us shine a light inside the black box of Postgres. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
Simon Riggs <simon@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
On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: > Simon Riggs <simon@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.comPostgreSQL Development, 24x7 Support, Training and Services
On 15 July 2010 16:20, Simon Riggs <simon@2ndquadrant.com> wrote: > On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: >> Simon Riggs <simon@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
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 <simon@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
workin 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
On Thu, Jul 15, 2010 at 17:30, Thom Brown <thombrown@gmail.com> wrote: > On 15 July 2010 16:20, Simon Riggs <simon@2ndquadrant.com> wrote: >> On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: >>> Simon Riggs <simon@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/
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 <simon@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
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
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
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 <simon@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 :) -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On 15 July 2010 16:52, Andrew Dunstan <andrew@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
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
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 <simon@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
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. Fournier Hub.Org Hosting Solutions S.A. scrappy@hub.org http://www.hub.org Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org
On 15 July 2010 17:07, Marc G. Fournier <scrappy@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
On Jul 15, 2010, at 10:50 AM, "Joshua D. Drake" <jd@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 stopto 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 turninto a mess. I have no interest in slamming something through without adequate consideration. ...Robert
On Thu, 15 Jul 2010, Thom Brown wrote:
> On 15 July 2010 17:07, Marc G. Fournier <scrappy@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. Fournier Hub.Org Hosting Solutions S.A.
scrappy@hub.org http://www.hub.org
Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org
On Thu, 2010-07-15 at 13:16 -0300, Marc G. Fournier wrote:
> > 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 ...
>
Thank you Marc. That is an excellent description of the problem that is
being ignored. We are no longer the academia database. We need to think
of real users here.
JD
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
On 15 July 2010 17:16, Marc G. Fournier <scrappy@hub.org> wrote:
> On Thu, 15 Jul 2010, Thom Brown wrote:
>
>> On 15 July 2010 17:07, Marc G. Fournier <scrappy@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 ...
Yes, I see what you mean now. That would simplify things greatly.
Thom
On Thu, Jul 15, 2010 at 05:38:35PM +0200, Magnus Hagander wrote: > On Thu, Jul 15, 2010 at 17:30, Thom Brown <thombrown@gmail.com> wrote: > > On 15 July 2010 16:20, Simon Riggs <simon@2ndquadrant.com> wrote: > >> On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: > >>> Simon Riggs <simon@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? In a word, YES! In two words, HELL, YES! Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
On Thu, Jul 15, 2010 at 08:50:31AM -0700, Joshua D. Drake 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. It's all about the Washingtons ;) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
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. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
On Jul 15, 2010, at 6:20 PM, Thom Brown wrote:
> On 15 July 2010 17:16, Marc G. Fournier <scrappy@hub.org> wrote:
>> On Thu, 15 Jul 2010, Thom Brown wrote:
>>
>>> On 15 July 2010 17:07, Marc G. Fournier <scrappy@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 ...
>
> Yes, I see what you mean now. That would simplify things greatly.
>
> Thom
>
exactly ...
and also: how many people outside the "inner circle" do you know who have ever seen the information schema?
i have been in postgres business for more than 10 years (full time) and i cannot name 5 customers who ever used the
informationschema to do "show tables" ...
a big argument is: "show tables" (or whatever) could work for all versions to come while a direct hit on the pg_class
orso would not give you total portability forever.
and yes, it is all about simplicity ...
it would not even add too much code to the backend and thus the complexity of this feature can really be neglected from
amaintenance point of view.
regards,
hans
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon@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? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Thu, 2010-07-15 at 13:16 -0300, Marc G. Fournier wrote:
> 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 ...
+1
-- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
On Thu, 2010-07-15 at 11:10 -0500, Robert Haas wrote: > 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. It's OK, I wasn't asking you or anyone else to do this. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
Magnus Hagander <magnus@hagander.net> wrote: > 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? The solution to this on some products (e.g., Sybase ASE) is to embed such logic in stored procedures. A stored procedure can generate an intermingled stream of results sets with different layouts and INFO, WARN, etc. lines. If we *had* stored procedures with such capabilities, I think that would be the direction to go; since we don't, I'm ambivalent. I don't suppose a stored procedure implementation is in the works anywhere? -Kevin
Simon Riggs <simon@2ndQuadrant.com> wrote: >> 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. > > It's OK, I wasn't asking you or anyone else to do this. I don't think a mess is OK regardless of who makes it. I think it would be wise to try to get some sort of consensus on what it would look like, rough as that process is. -Kevin
On 2010-07-15 18:07, Marc G. Fournier wrote:<br /><span style="white-space: pre;">> On Thu, 15 Jul 2010, Thom Brown wrote:<br/> > <br /> >> If it's only a psql problem, why implement it as SQL? Is it just<br /> >> so we'renot adding keywords specifically to psql? In that case,<br /> >> it shouldn't support QUIT.<br /> > <br />> Personally, I think this is somethign that should go into the backend<br /> > ... I'd like to be able to writeperl scripts that talk to the<br /> > backend without having to remember all the various system tables I<br /> >need to query / join to get the same results as \d gives me in psql<br /> > ... same for any interface language, really...<br /></span><br /> Moving it into the backend (together with the other commands) would<br /> also solve this usabillityissue:<br /><br /> WARNING: You are connected to a server with major version 8.4,<br /> but your psql client ismajor version 8.3. Some backslash commands,<br /> such as \d, might not work properly.<br /><br /> testdb \d testtable<br/> ERROR: column "reltriggers" does not exist<br /> LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers,relhasr...<br /> <br /> -- ^<br /> Jesper<br />
On Thu, 2010-07-15 at 18:43 +0200, Magnus Hagander wrote: > On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon@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 imagined that we would do something similar to EXPLAIN, a set of text rows returned. It should be possible to migrate \d options to using new outputs, when everything works in a useful manner. Probably not in this release. If I get some working solutions ready for Sept 15 we then have 4 months for other people to patch away at this. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
On Thu, Jul 15, 2010 at 18:59, Simon Riggs <simon@2ndquadrant.com> wrote: > On Thu, 2010-07-15 at 18:43 +0200, Magnus Hagander wrote: >> On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon@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 imagined that we would do something similar to EXPLAIN, a set of text > rows returned. Wouldn't that be useless for the case when an app wants to use it? An app will require it to be structured somehow. There's a reason we made EXPLAIN output data structured now. But I guess you could define an XML/JSON schema and return it in that... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Thu, 2010-07-15 at 11:55 -0500, Kevin Grittner wrote: > Simon Riggs <simon@2ndQuadrant.com> wrote: > > >> 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. > > > > It's OK, I wasn't asking you or anyone else to do this. > > I don't think a mess is OK regardless of who makes it. I think it > would be wise to try to get some sort of consensus on what it would > look like, rough as that process is. So starting a discussion thread is the wrong way to achieve that? How would you have me gain consensus if not this way? -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
On Thu, 2010-07-15 at 18:16 +0100, Simon Riggs wrote: > On Thu, 2010-07-15 at 11:55 -0500, Kevin Grittner wrote: > > Simon Riggs <simon@2ndQuadrant.com> wrote: > > > > >> 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. > > > > > > It's OK, I wasn't asking you or anyone else to do this. > > > > I don't think a mess is OK regardless of who makes it. I think it > > would be wise to try to get some sort of consensus on what it would > > look like, rough as that process is. > > So starting a discussion thread is the wrong way to achieve that? How > would you have me gain consensus if not this way? I think you guys are talking past each other. I believe Kevin was in fact stating that we needed to continue discussion. Joshua D. Drake > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Training and Services > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering
On Thu, 2010-07-15 at 19:02 +0200, Magnus Hagander wrote: > > I imagined that we would do something similar to EXPLAIN, a set of text > > rows returned. > > Wouldn't that be useless for the case when an app wants to use it? An > app will require it to be structured somehow. > > There's a reason we made EXPLAIN output data structured now. But I > guess you could define an XML/JSON schema and return it in that... The proposed goal is simplicity, not to be all things to all men. Anybody that wants structured output can i) write that as a future patch ii) write SQL to retrieve exactly what they want (preferred) -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
On Thu, 15 Jul 2010 17:09:32 +0100 Thom Brown wrote: > On 15 July 2010 17:07, Marc G. Fournier <scrappy@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? Is there a way to query all databases from information_schema? Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > The solution to this on some products (e.g., Sybase ASE) is to embed > such logic in stored procedures. > ...(skip other ideas)... > > I don't suppose a stored procedure implementation is in the works > anywhere? Certainly some set-returning functions would be easy to implement, and could even be bolted on to existing systems for testing, etc. Now that plpgsql is installed by default, this is not the show-stopper it once was.... - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 201007151321 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkw/Q+gACgkQvJuQZxSWSsiLpgCfU7Zt3ZmJwK0PrzYr5T0y6blD IiwAoNGoPXvxDhCbHn0MNKwxwh49fcdY =kfX8 -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Moving it into the backend (together with the other commands) would > also solve this usabillity issue: ... > testdb \d testtable > ERROR: column "reltriggers" does not exist > LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr... This has already been solved at the psql level in recent versions of psql. Although, having a common functionality was always one of the proposed solutions to the problem, rather than having all the code paths inside of psql. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 201007151325 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkw/RMkACgkQvJuQZxSWSsglegCfU0qWorYc3c0Nq9+2weDu6dPi 3lgAn0r5w2Xbvbb3x57bjzC/LKzCe3em =Ie8l -----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> wrote: > I think you guys are talking past each other. So it would appear. I was replying to a comment by Simon which sounded to me as though he didn't feel any further discussion was needed. > I believe Kevin was in fact stating that we needed to continue > discussion. Right, and judging from Simon's reply, we have no disagreement on that. Sorry for the confusion. I still think that the ability to issue one request and get back a series of responses, each of which could be the result of RAISE or a SELECT, would be valuable, and would be the best way to implement this; but of course it would be totally insane to try to burden Simon's proposal with such a requirement. I was hoping that someone had something in the works, close to fruition, which could be set as a prerequisite for Simon's feature -- so that it could be done in the best possible manner. The current alternatives of a separate request for each related bit of information versus a bunch of text lines makes me a bit queasy. Formated text (XML, YAML, etc.) seems worse than either of the above. -Kevin
On Thu, 15 Jul 2010, Magnus Hagander wrote: > On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon@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? One is an interface comamnd (ie. psql specific), the other is a generic command for any interface ... \d doesn't work in perl or tcl or ... so, for those, we're talking about adding a 'short form' (show tables), but if someone wants to use the long form of querying multiple table s(or information_schema), that option is still open to them ... ---- Marc G. Fournier Hub.Org Hosting Solutions S.A. scrappy@hub.org http://www.hub.org Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org
On Thu, 2010-07-15 at 12:36 -0500, Kevin Grittner wrote: > I still think that the ability to issue one request and get back a > series of responses, each of which could be the result of RAISE or a > SELECT, would be valuable, and would be the best way to implement > this; but of course it would be totally insane to try to burden > Simon's proposal with such a requirement. I was hoping that someone > had something in the works, close to fruition, which could be set as > a prerequisite for Simon's feature -- so that it could be done in > the best possible manner. The current alternatives of a separate > request for each related bit of information versus a bunch of text > lines makes me a bit queasy. Formated text (XML, YAML, etc.) seems > worse than either of the above. Yes, the feature is harder than it first appears, but that's a reasonable reason for me to do it. But once we have the structures in place, rattling out a few patches should be easy enough. The bulk of the code could be very similar to psql and text EXPLAIN. So I would work on SHOW TABLES first, developing the core facilities required. Will come back with a full plan for discussion probably a month from now. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > 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 ... Perl, eh? Most (all?) interfaces already have their own methods, e.g. $dbh->tables(); Of course, they don't really provide all the information that \d does, but you shouldn't need to be querying the system catalogs directly for *any* interface, including psql (for most common tasks). - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007151357 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkw/TBkACgkQvJuQZxSWSshuvACgtVpCav5qcl3nYsrsRdZ0vcT7 siUAoJaKkQ/RMAHcKCKJEyecjeEUhiQz =2mS9 -----END PGP SIGNATURE-----
On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: > There should be one command to "display a list of tables" and it needs > to be easily guessable for those who have forgotten. Well, if you put information_schema in the default path, it'd be SELECT * FROM TABLES;
Excerpts from Peter Eisentraut's message of jue jul 15 14:21:26 -0400 2010: > On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: > > There should be one command to "display a list of tables" and it needs > > to be easily guessable for those who have forgotten. > > Well, if you put information_schema in the default path, it'd be > > SELECT * FROM TABLES; Or even TABLE TABLES; weird though that is ...
On Jul 15, 2010, at 11:59 AM, Simon Riggs <simon@2ndQuadrant.com> wrote: > On Thu, 2010-07-15 at 18:43 +0200, Magnus Hagander wrote: >> On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon@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 imagined that we would do something similar to EXPLAIN, a set of text > rows returned. That seems rather wretched for machine-parsability, which I think is an important property for anything we do in this area. We need to think harder about how we could structure this to allow returning more than just a tabular result set whilestill allowing clients easy programmatic access to the underlying data. > It should be possible to migrate \d options to using new outputs, when > everything works in a useful manner. Probably not in this release. > > If I get some working solutions ready for Sept 15 we then have 4 months > for other people to patch away at this. Sounds good, but we need agreement on a more detailed design first. ...Robert
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Well, if you put information_schema in the default path, it'd be > > SELECT * FROM TABLES; Except it also shows views[1]. Oh, and it has a bunch of other arcane and unwanted columns. Which we can't remove, nor can we add additional columns to it, because it was specified by the standard, which means that it was designed by committee and thus ugly and unusable for most things. [1] Granted, it would at least *tell* you its a view, unlike MySQL's SHOW TABLES. On the other hand, no owner as per \dt P.S. What's with the uppercase mania in this thread? Can we please get back to saying "select * from tables" and "show tables"? :) - -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkw/WA0ACgkQvJuQZxSWSsilEwCgqo8OefwS2B65JnJjH9xLVhp0 R2wAoPtuMxSPvVKfZ19yBDo8as59p7lv =YmAb -----END PGP SIGNATURE-----
On tor, 2010-07-15 at 19:21 +0200, Andreas 'ads' Scherbaum wrote: > Is there a way to query all databases from information_schema? No.
On Thu, 2010-07-15 at 18:48 +0000, Greg Sabino Mullane wrote: > P.S. What's with the uppercase mania in this thread? Can we > please get back to saying "select * from tables" > and "show tables"? :) The standard specifies that it it should be uppercase. :P JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering
On Thu, 15 Jul 2010, Peter Eisentraut wrote: > On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: >> There should be one command to "display a list of tables" and it needs >> to be easily guessable for those who have forgotten. > > Well, if you put information_schema in the default path, it'd be > > SELECT * FROM TABLES; mre like: SELECT * FROM TABLES WHERE not a system table or information schema table; if we want to get *somewhere* close to \d ... ---- Marc G. Fournier Hub.Org Hosting Solutions S.A. scrappy@hub.org http://www.hub.org Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org
--On 15. Juli 2010 18:02:10 +0200 Guillaume Lelarge <guillaume@lelarge.info> wrote: > 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 :) And as soon as you have this, people want to have that list ordered by size, schema or number of estimated tuples..... I think what we really need to do is to improve our current interfaces and/or documentation to show how to do many tasks. In former courses i gave i always heard how easy it is in MySQL to get dictionary information, but once you are going to show the entry keys in how to query information_schema or using the administration functions in PostgreSQL the big "aha" begin to start. Maybe we can wrap many of the current psql output into SRF's to ease the retrieval for such kind of information other procedures or interfaces. -- Thanks Bernd
Joshua D. Drake wrote: > On Thu, 2010-07-15 at 18:48 +0000, Greg Sabino Mullane wrote: > > >> P.S. What's with the uppercase mania in this thread? Can we >> please get back to saying "select * from tables" >> and "show tables"? :) >> > > The standard specifies that it it should be uppercase. > > :P > > > You're thinking about the unquoted identifiers. That's quite different from what is required of keywords. cheers andrew
On 15/07/10 19:44, Robert Haas wrote: > On Jul 15, 2010, at 11:59 AM, Simon Riggs<simon@2ndQuadrant.com> > wrote: >> >> I imagined that we would do something similar to EXPLAIN, a set of >> text rows returned. > > That seems rather wretched for machine-parsability, which I think is > an important property for anything we do in this area. We need to > think harder about how we could structure this to allow returning > more than just a tabular result set while still allowing clients easy > programmatic access to the underlying data. > >> It should be possible to migrate \d options to using new outputs, >> when everything works in a useful manner. Probably not in this >> release. Feature sounds useful. I think our \dxx commands have grown a little unwieldy in the last version or two. Which is not to say you can take \d away :-) I was assuming the process would be something like: 1. Move existing \d queries into functions* 2. Convert psql to use those 3. Add "SHOW xxx" and have it return a single query Have it also issue "NOTICE: from psql, try \dt for more info" If/when we have multiple sets returned from one query it should be simple to provide something pretty close to \d... from a single command. Trying to format the data in the backend is probably just going to frustrate writers of different clients (of which I think we have quite a few now). * These functions could then be back-ported as an admin-pack too for clients/apps that wanted cross-version compatibility for these sorts of things. -- Richard Huxton Archonet Ltd
On Thu, 15 Jul 2010 22:01:34 +0300 Peter Eisentraut wrote: > On tor, 2010-07-15 at 19:21 +0200, Andreas 'ads' Scherbaum wrote: > > Is there a way to query all databases from information_schema? > > No. This got rejected before, because of "not in the standard". In this case: no way to answer "SHOW DATABASES" by just using information_schema. At least this question requires using the system tables. Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project
Bernd Helmle wrote: > > > --On 15. Juli 2010 18:02:10 +0200 Guillaume Lelarge > <guillaume@lelarge.info> wrote: > > > 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 :) > > And as soon as you have this, people want to have that list ordered by > size, schema or number of estimated tuples..... > > I think what we really need to do is to improve our current interfaces > and/or documentation to show how to do many tasks. In former courses i gave > i always heard how easy it is in MySQL to get dictionary information, but > once you are going to show the entry keys in how to query > information_schema or using the administration functions in PostgreSQL the > big "aha" begin to start. Maybe we can wrap many of the current psql output > into SRF's to ease the retrieval for such kind of information other > procedures or interfaces. 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? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I was assuming the process would be something like: > 1. Move existing \d queries into functions* > 2. Convert psql to use those Oops! There's goes your ability to handle older versions of Postgres from the existing psql[1]. Cue angry mobs of DBAs with pitchforks. And no, a contrib like add-on won't go over well either. [1] (unless we keep both types of access around for many many years, until we can be sure that any database queries will have the new utility functions) - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007151540 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkw/ZOIACgkQvJuQZxSWSshQ8gCfacG3r9N3rIn4Vbb/8tz0JK7S uCcAn037OYy9E5uiG84qBjjxzNox27+D =6sHa -----END PGP SIGNATURE-----
On 15/07/10 19:06, Aaron W. Swenson wrote: > 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 . . . . +1. That doesn't force us to implement a whole new set of commands and syntax to describe stuff in the backend, duplicating the \d commands, but is polite to the users, and immediately guides them to the right commands. You could even do that in the backend for a few simple commands like SHOW TABLES: ERROR: syntax error at "SHOW TABLES" HINT: To list tables in the database, SELECT * FROM pg_tables or use the \d psql command. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
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. This could presumably be implemented by creating a view to return the required information and then making "SHOW TABLES" an alias for "select * from viewname". FYI, MS-SQL does this stuff with some stored procedures. I regularly use sp_columns to fiind out what I'm really being asked to interact with. See <http://msdn.microsoft.com/en-us/library/ms182764.aspx> cheers andrew
--On 15. Juli 2010 15:52:24 -0400 Andrew Dunstan <andrew@dunslane.net> wrote: > FYI, MS-SQL does this stuff with some stored procedures. I regularly use > sp_columns to fiind out what I'm really being asked to interact with. See > <http://msdn.microsoft.com/en-us/library/ms182764.aspx> Yeah, something like this was in my mind. -- Thanks Bernd
On 15/07/10 20:43, Greg Sabino Mullane wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > >> I was assuming the process would be something like: >> 1. Move existing \d queries into functions* >> 2. Convert psql to use those > > Oops! There's goes your ability to handle older versions > of Postgres from the existing psql Arse. It's little details like this that demonstrate why I'm a user and not a hacker :-) -- Richard Huxton Archonet Ltd
On Jul 15, 2010, at 2:45 PM, Heikki Linnakangas wrote: > On 15/07/10 19:06, Aaron W. Swenson wrote: >> 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 . . . . > > +1. That doesn't force us to implement a whole new set of commands and syntax to describe stuff in the backend, duplicatingthe \d commands, but is polite to the users, and immediately guides them to the right commands. > > You could even do that in the backend for a few simple commands like SHOW TABLES: > > ERROR: syntax error at "SHOW TABLES" > HINT: To list tables in the database, SELECT * FROM pg_tables or use the \d psql command. This sounds roughly like the patch I submitted in January (linked upthread), although that swiped the input before it hitthe backend. I don't know if I like the idea of that HINT or not. Regards, David -- David Christensen End Point Corporation david@endpoint.com
On Thu, Jul 15, 2010 at 04:20:12PM +0100, Simon Riggs wrote: > > 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. > Ah, that's true, we've never met in person ... Let me say that I recall finding the clean separation of what the client implements vs. what the server implements very useful when I was new to postgresql. Anything that doesn't start with a backslash works equally well from psql and from python/psycopg2, for example. If you make SHOW variants that are actually client side \d commands, you break that. Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 The Connexions Project http://cnx.org fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
On Thu, 2010-07-15 at 13:44 -0500, Robert Haas wrote: > Sounds good, but we need agreement on a more detailed design first. What do you mean? -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
On Jul 15, 2010, at 2:26 PM, Richard Huxton <dev@archonet.com> wrote: > 3. Add "SHOW xxx" and have it return a single query > Have it also issue "NOTICE: from psql, try \dt for more info" A big -1 from me on that. Going to a whole lot of trouble to implement something half as functional as what we have alreadysounds like a huge lose to me. > If/when we have multiple sets returned from one query it should be simple to provide something pretty close to \d... froma single command. Sounds to me like this is just about a prerequisite for this project. ...Robert >
On Thu, 2010-07-15 at 13:44 -0500, Robert Haas wrote: > That seems rather wretched for machine-parsability, which I think is > an important property for anything we do in this area. I completely disagree. This is for humans only, and mostly newbies only. Anybody that wants structured output can type the SQL and get as much structure as they want. I'm not reinventing the whole wheel. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
On Thu, 2010-07-15 at 15:52 -0400, Andrew Dunstan wrote: > This could presumably be implemented by creating a view to return the > required information and then making "SHOW TABLES" an alias for > "select > * from viewname". > > FYI, MS-SQL does this stuff with some stored procedures. I regularly > use > sp_columns to fiind out what I'm really being asked to interact with. > See <http://msdn.microsoft.com/en-us/library/ms182764.aspx> Sounds good. OK, how about this: We write a function to derive the output, which can be executed as a function if people like that. We then make SHOW TABLEs a synonym for SELECT * FROM show_function() That way we get both in one go. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
Le 15 juil. 2010 à 18:43, Magnus Hagander <magnus@hagander.net> a écrit : > 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 think we should keep both, and optionaly have a given psql \d command issue more than one SHOW query. Same as it does nowwith SELECT queries. That means we keep a resultset per SHOW query, so it's easy on the application. Regards, -- dim
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 <simon@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
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
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
On Thu, 2010-07-15 at 13:16 -0300, Marc G. Fournier wrote:
> > 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 ...
>
Thank you Marc. That is an excellent description of the problem that is
being ignored. We are no longer the academia database. We need to think
of real users here.
JD
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
On Thu, Jul 15, 2010 at 02:31:10PM -0400, Alvaro Herrera wrote: > Excerpts from Peter Eisentraut's message of jue jul 15 14:21:26 -0400 2010: > > On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: > > > There should be one command to "display a list of tables" and it needs > > > to be easily guessable for those who have forgotten. > > > > Well, if you put information_schema in the default path, it'd be > > > > SELECT * FROM TABLES; > > Or even > > TABLE TABLES; > > weird though that is ... "Weird though that is," is *exactly* the problem we're trying to address here. SHOW TABLES is really, really easy to remember or guess. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
On Thu, 2010-07-15 at 18:16 +0100, Simon Riggs wrote: > On Thu, 2010-07-15 at 11:55 -0500, Kevin Grittner wrote: > > Simon Riggs <simon@2ndQuadrant.com> wrote: > > > > >> 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. > > > > > > It's OK, I wasn't asking you or anyone else to do this. > > > > I don't think a mess is OK regardless of who makes it. I think it > > would be wise to try to get some sort of consensus on what it would > > look like, rough as that process is. > > So starting a discussion thread is the wrong way to achieve that? How > would you have me gain consensus if not this way? I think you guys are talking past each other. I believe Kevin was in fact stating that we needed to continue discussion. Joshua D. Drake > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Training and Services > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering
Excerpts from David Fetter's message of jue jul 15 19:19:47 -0400 2010: > On Thu, Jul 15, 2010 at 02:31:10PM -0400, Alvaro Herrera wrote: > > Or even > > > > TABLE TABLES; > > > > weird though that is ... > > "Weird though that is," is *exactly* the problem we're trying to > address here. SHOW TABLES is really, really easy to remember or > guess. Eh? I thought the problem being solved is that the command is implemented in the client side rather than the server side, so all interfaces need to implement it time and time again. With TABLE TABLES there's no such problem. TABLE has also the advantage (over SHOW) that it already works on 8.4, and moreover it is SQL standard.
On Thu, Jul 15, 2010 at 5:34 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Thu, 2010-07-15 at 13:44 -0500, Robert Haas wrote: >> Sounds good, but we need agreement on a more detailed design first. > What do you mean? 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. I am of the view that this is not worth doing if it is only a kludge to make things sort-of work for newbies, with the expectation that they'll never do it again once they learn how to use backslash commands (and let's keep in mind that many users access the database through tools other than psql - e.g. pgadmin). I am also of the view that it would be poor to have allow users to type "show tables" to see tables and "show functions" to see functions but require them to type "\des" to see foreign servers. That's not a real fix for any real problem - that's a cheap hack. If we can create a command set which is (1) more mnemonic than the existing backslash commands, (2) generates tabular output that can easily be used by scripts and clients other than psql, (3) applies across-the-board to all of our object types, and (4) is capable of providing all the same functionality that we currently get through "\d<whatever>" commands, then I'm in favor of it. Otherwise, I'm probably not, though I'm willing to listen to what you and others have to say. It's possible that the community might be in favor of a solution which doesn't include all of the above elements, but you can't presume that because the community is generally in favor of doing something along the lines that they will also be in favor of any specific proposal. That's why I think it's important to have, and agree on, a detailed design before writing code. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
On Thu, 2010-07-15 at 18:48 +0000, Greg Sabino Mullane wrote: > P.S. What's with the uppercase mania in this thread? Can we > please get back to saying "select * from tables" > and "show tables"? :) The standard specifies that it it should be uppercase. :P JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering
On Thu, 15 Jul 2010, Simon Riggs wrote: > On Thu, 2010-07-15 at 13:44 -0500, Robert Haas wrote: >> That seems rather wretched for machine-parsability, which I think is >> an important property for anything we do in this area. > > I completely disagree. This is for humans only, and mostly newbies only. > > Anybody that wants structured output can type the SQL and get as much > structure as they want. I'm not reinventing the whole wheel. 'k, but now we are back to why can't this just be an extension of psql vs in the backend? If someone writing an interface should be typing the SQL to get the information, then 'SHOW TABLES' doesn't really provide them anything, does it? ---- Marc G. Fournier Hub.Org Hosting Solutions S.A. scrappy@hub.org http://www.hub.org Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org
All,
So, from my perspective is that the main issue with the \d commands is
that they are not accessible from interfaces other than psql. Often,
you have to write a big, hairy, pg-version-specific query to make them
happen. information_schema is nice but (a) it's not in the default
search path, and (b) it doesn't show everything. Just try to get your
list of FKs out of it. Think of the number of people who use "echo
commands" just to extract the query for the \d commands.
This was why we (well, mainly Andrew Geirth) developed newsysviews. But
you know how that went.
I think that users could live with other syntax (like SELECT name FROM
pg_tables()) if the result returned were intuitive (user tables only)
and didn't change over postgres versions much. As Andrew points out,
SQLServer users seem to have no problem calling sp_* commands.
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?
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
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.comPostgreSQL Development, 24x7 Support, Training and Services
On Jul 15, 2010, at 6:43 PM, Magnus Hagander wrote:
> On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon@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
On Jul 15, 2010, at 11:18 PM, Josh Berkus <josh@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
> I have to agree with Simon here. \d is ridiculous for the common user. +1 Regards Markus
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
2010/7/16 Bruce Momjian <bruce@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 <bruce@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 >
On 16 July 2010 13:49, Bruce Momjian <bruce@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
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.comPostgreSQL Development, 24x7 Support, Training and Services
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
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.comPostgreSQL Development, 24x7 Support, Training and Services
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 testhpsql (9.1devel)Type "help" for help.test=> helpYou 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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
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 <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
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. Fournier Hub.Org Hosting Solutions S.A. scrappy@hub.org http://www.hub.org Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
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 <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
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 functionsis 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
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote: > Really? What are the other syntaxes? SHOW TABLES -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
* Simon Riggs <simon@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, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Bruce Momjian <bruce@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
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.comPostgreSQL Development, 24x7 Support, Training and Services
On Jul 16, 2010, at 7:43 AM, Bruce Momjian <bruce@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 ofattributes for each table. It took me at least a full day and I almost gave up and abandoned PostgreSQL as a result. Ithink calling this duplicate functionality is ridiculous. Sure, it's possible. In fact, it's very easy. For committers. ...Robert
On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas <robertmhaas@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 wultsch@gmail.com
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. Fournier Hub.Org Hosting Solutions S.A. scrappy@hub.org http://www.hub.org Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org
simon@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
notonly 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
canbe 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
kindsof 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
toanalyze object permissions.
- \? output is often *not* amenable to this, as it sometimes has extra bits of data hierarchy in it. And
"arrayaggregation."
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/>
"Marc G. Fournier" <scrappy@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
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
On 16/07/10 20:11, Rob Wultsch wrote: > On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas<robertmhaas@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
On Fri, Jul 16, 2010 at 10:52 AM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > On 16/07/10 20:11, Rob Wultsch wrote: >> >> On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas<robertmhaas@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 wultsch@gmail.com
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 Haas<robertmhaas@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.comPostgreSQL Development, 24x7 Support, Training and Services
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
Simon Riggs <simon@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
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
On 17 July 2010 07:26, Joshua D. Drake <jd@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.
On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote: > On 17 July 2010 07:26, Joshua D. Drake <jd@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
* Heikki Linnakangas (heikki.linnakangas@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
"Joshua D. Drake" <jd@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
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 ofthem 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 CONSTRAINTSON <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, thatwould be a good first client. Oh and that means the design is about all done already. And that we still are in the onecommand - one resultset interface. Meaning any libpq driver knows how to deal with the resultset, and that's not parsingtext. 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 handyfrom anywhere. So let's just have an easy syntax in the backend to do all the catalog 'magic' querying psql does, butone query at a time. Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte
"Kevin Grittner" <Kevin.Grittner@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
On Fri, Jul 16, 2010 at 1:52 PM, Heikki Linnakangas <heikki.linnakangas@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
* Robert Haas (robertmhaas@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
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
On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote: > On 17 July 2010 07:26, Joshua D. Drake <jd@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
On Jul 16, 2010, at 11:02 PM, Stephen Frost <sfrost@snowman.net> wrote: > * Robert Haas (robertmhaas@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 syntaxis not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon asyou 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 commandsto list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command tolist 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 doyou think can do it at all? I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward in usability. ...Robert
Tim Landscheidt <tim@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
On Sat, 2010-07-17 at 09:02 -0500, Robert Haas wrote: > On Jul 16, 2010, at 11:02 PM, Stephen Frost <sfrost@snowman.net> wrote: > > * Robert Haas (robertmhaas@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 syntaxis not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon asyou 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 commandsto list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command tolist 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 doyou 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
2010/7/17 Joshua D. Drake <jd@commandprompt.com>: > On Sat, 2010-07-17 at 09:02 -0500, Robert Haas wrote: >> On Jul 16, 2010, at 11:02 PM, Stephen Frost <sfrost@snowman.net> wrote: >> > * Robert Haas (robertmhaas@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 thesyntax is not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soonas 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 commandsto list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command tolist 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 doyou 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 >
On 07/17/2010 04:02 PM, Robert Haas wrote: > On Jul 16, 2010, at 11:02 PM, Stephen Frost<sfrost@snowman.net> wrote: >> * Robert Haas (robertmhaas@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 syntaxis not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon asyou 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 commandsto list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command tolist 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 doyou 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
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 Frost<sfrost@snowman.net> wrote: > >> * Robert Haas (robertmhaas@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 thesyntax is not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soonas 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 commandsto list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command tolist 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 doyou 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
On Sat, Jul 17, 2010 at 5:30 PM, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: > On 07/17/2010 04:02 PM, Robert Haas wrote: >> On Jul 16, 2010, at 11:02 PM, Stephen Frost<sfrost@snowman.net> wrote: >>> * Robert Haas (robertmhaas@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, butit's not anything approaching easy. All of this talk about backslash commands being powerful rings totally hollow for me. For ordinary, day to day tasks like listing all my tables, or looking at the details of a particular table, they're great. I use them all the time and would still use them even if some other syntax were available. But there is no reasonable way to pass options to them, and that to me is a pretty major drawback. > I'm not sure where to draw the line but implementing a proper shortcut > interface for cammands is something taht should be done on the client side > because not every client is the same and the needs of psql might be > radically different from any other client (like pgadmin or a fancy Web 2.0 > AJAX thingy - those will likely always use custom catalog queries). > Maybe a differnet way to look at the whole thing is to reconsider our own > catalogs (anyone remember newsysview?) and add a bunch of views to abstract > away most of the current complexity for these usecases? Our previous experiments in this area haven't been wildly successful. For example, we have pg_tables, but suppose you want to augment the results with the size of each table. To handle this in a schema-safe manner, you need the OID, which isn't available, so you're back to querying against pg_class directly. The selection of columns in pg_tables is pretty random anyway - I dunno why someone thinks that hasindexes, hasrules, and hastriggers are more interesting than any other property of the table you might care to query. I'm not opposed to some further experimentation in this area, but I'm not convinced it's going to get us very far. What's your proposal? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian <bruce@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
On Sat, 2010-07-17 at 09:02 -0500, Robert Haas wrote: > On Jul 16, 2010, at 11:02 PM, Stephen Frost <sfrost@snowman.net> wrote: > > * Robert Haas (robertmhaas@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 syntaxis not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon asyou 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 commandsto list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command tolist 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 doyou 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
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 Frost<sfrost@snowman.net> wrote: > >> * Robert Haas (robertmhaas@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 thesyntax is not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soonas 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 commandsto list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command tolist 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 doyou 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
Hi, Le 18 juil. 2010 à 05:41, Robert Haas a écrit : > On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian <bruce@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 meansof "parametrized" views. It could be that SQL only SRFs could do a better job at it. In both cases the idea is thatwe 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 wouldlist all tables with both a date and a time column. The trick is there because if you want the attributes to show upyou'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 availableboth as plain and easy usage and in full SQL. I think the rewrite system is meant to allow that, I'm not sure ifusing views or pure SQL SRFs is better, in both cases the rewritten query has to provide arguments "placeholders": if aVIEW, that's a WHERE clause, if a SRF, any number of named arguments. Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte
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
Dimitri Fontaine <dfontaine@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
On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund <andres@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 wultsch@gmail.com
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
Hi, On Sunday 18 July 2010 21:02:59 Rob Wultsch wrote: > On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund <andres@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
Stefan Kaltenbrunner <stefan@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
Le 18 juil. 2010 à 21:00, Kevin Grittner a écrit : > Dimitri Fontaine <dfontaine@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
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 haveaccess 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 willcertainly 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
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, Iguess. Regards, -- dim
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.comPostgreSQL Development, 24x7 Support, Training and Services
On 07/18/2010 09:00 PM, Kevin Grittner wrote: > Dimitri Fontaine<dfontaine@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
Hi Kevin, On Sunday 18 July 2010 21:24:25 Kevin Grittner wrote: > Stefan Kaltenbrunner <stefan@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
Kevin,
* Kevin Grittner (Kevin.Grittner@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
-----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 greg@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-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Robert Haas (robertmhaas@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 greg@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-----
-----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 greg@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-----
>Stephen Frost <sfrost@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
* Kevin Grittner (Kevin.Grittner@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
On Mon, Jul 19, 2010 at 10:25 AM, Greg Sabino Mullane <greg@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
On Mon, Jul 19, 2010 at 02:12:19PM -0000, 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 <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
On Mon, 2010-07-19 at 10:23 -0700, David Fetter wrote: > On Mon, Jul 19, 2010 at 02:12:19PM -0000, 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
-----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 greg@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-----
On Mon, Jul 19, 2010 at 09:31:06AM -0500, Kevin Grittner wrote: > >Stephen Frost <sfrost@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 <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
David Fetter <david@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 '00000' 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
On Mon, 2010-07-19 at 10:23 -0700, David Fetter wrote: > On Mon, Jul 19, 2010 at 02:12:19PM -0000, 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
Robert Haas wrote: > On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian <bruce@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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 <bruce@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.comPostgreSQL Development, 24x7 Support, Training and Services
On Mon, Aug 9, 2010 at 5:42 PM, Bruce Momjian <bruce@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
Kevin Grittner wrote: > Stefan Kaltenbrunner <stefan@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@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
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 <simon@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/
On Tue, Sep 21, 2010 at 4:52 AM, Boszormenyi Zoltan <zb@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