Re: 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.com
PostgreSQL Development, 24x7 Support, Training and Services

Simon Riggs <simon(at)2ndQuadrant(dot)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(at)2ndQuadrant(dot)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

On 15 July 2010 16:20, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)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(at)2ndQuadrant(dot)com> writes:
>>> The biggest turn off that most people experience when using PostgreSQL
>>> is that psql does not support memorable commands.
>>
>>> I would like to implement the following commands as SQL, allowing them
>>> to be used from any interface.
>>
>>> SHOW TABLES
>>> SHOW COLUMNS
>>> SHOW DATABASES
>>
>> This has been discussed before, and rejected before. Please see
>> archives.
>
> Many years ago. I think it's worth revisiting now in light of the number
> of people now joining the PostgreSQL community and the greater
> prevalence other ways of doing it. The world has changed, we have not.
>
> I'm not proposing any change in function, just a simpler syntax to allow
> the above information to be available, for newbies.
>
> Just for the record, I've never ever met anyone that said "Oh, this \d
> syntax makes so much sense. I'm a real convert to Postgres now you've
> shown me this". The reaction is always the opposite one; always
> negative. Which detracts from our efforts elsewhere.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Training and Services
>

simon is absolutely right here.
we should not mind being a little more user friendly in this area.
many people are simply used to this kind of stuff.

remember when you rejected something the last time (not necessarily software). was ist because you could not make it work in 2 min or was it because you did not like something else?
do you reject buying a car because of a non obvious screw in the engine or because "it somehow does not feel right"?

simon made an important point and i can simply agree - regardless of whether it has been discussed before or not.
if you die a beautiful death you are still dead after all.

regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

On Thu, Jul 15, 2010 at 17:30, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> On 15 July 2010 16:20, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:
>>> Simon Riggs <simon(at)2ndQuadrant(dot)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(at)2ndQuadrant(dot)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(at)2ndQuadrant(dot)com> writes:
>>>> The biggest turn off that most people experience when using PostgreSQL
>>>> is that psql does not support memorable commands.
>>>
>>>> I would like to implement the following commands as SQL, allowing them
>>>> to be used from any interface.
>>>
>>>> SHOW TABLES
>>>> SHOW COLUMNS
>>>> SHOW DATABASES
>>>
>>> This has been discussed before, and rejected before. Please see
>>> archives.
>>
>> Many years ago. I think it's worth revisiting now in light of the number
>> of people now joining the PostgreSQL community and the greater
>> prevalence other ways of doing it. The world has changed, we have not.
>>
>> I'm not proposing any change in function, just a simpler syntax to allow
>> the above information to be available, for newbies.
>>
>> Just for the record, I've never ever met anyone that said "Oh, this \d
>> syntax makes so much sense. I'm a real convert to Postgres now you've
>> shown me this". The reaction is always the opposite one; always
>> negative. Which detracts from our efforts elsewhere.
>
> I have to agree with Simon here. \d is ridiculous for the common user.
>
> SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like
> DESCRIBE TABLE foo makes a lot more sense than \d.
>

And would you add the complete syntax? I mean:

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

I'm wondering what one can do with the [FROM db_name] clause :)

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

On 15 July 2010 16:52, Andrew Dunstan <andrew(at)dunslane(dot)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(at)2ndQuadrant(dot)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(at)hub(dot)org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy(at)hub(dot)org

On 15 July 2010 17:07, Marc G. Fournier <scrappy(at)hub(dot)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(at)commandprompt(dot)com> wrote:
> On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote:
>
>>> Looks like the last time this was discussed, there wasn't any clear
>>> conclusion. Someone created a patch and it's still on the TODO list:
>>> http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php
>>
>> That one is about:
>> a) doing it in psql., not the backend
>> b) not actually implementing the command, but implementing hints for
>> the user telling them which is the correct command
>>
>> Is there an actual common use-case for having these commands available
>> for *non-psql* interfaces?
>
> Yes. We should provide a single, well described grammar for interacting
> with objects in the database regardless of client. I should be able to
> open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
> fall out.

Damn straight. I like \d as well as anyone but there are real problems with it. Perhaps when we add \dxrvbfqS$: we'll stop to reflect on what they are.

Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration.

...Robert

On Thu, 15 Jul 2010, Thom Brown wrote:

> On 15 July 2010 17:07, Marc G. Fournier <scrappy(at)hub(dot)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(at)hub(dot)org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy(at)hub(dot)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(at)hub(dot)org> wrote:
> On Thu, 15 Jul 2010, Thom Brown wrote:
>
>> On 15 July 2010 17:07, Marc G. Fournier <scrappy(at)hub(dot)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(at)gmail(dot)com> wrote:
> > On 15 July 2010 16:20, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:
> >>> Simon Riggs <simon(at)2ndQuadrant(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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.com
PostgreSQL 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(at)hub(dot)org> wrote:
>> On Thu, 15 Jul 2010, Thom Brown wrote:
>>
>>> On 15 July 2010 17:07, Marc G. Fournier <scrappy(at)hub(dot)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 information schema 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 or so 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 a maintenance 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, 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.com
PostgreSQL Development, 24x7 Support, Training and Services

On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon(at)2ndquadrant(dot)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 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.com
PostgreSQL Development, 24x7 Support, Training and Services

Magnus Hagander <magnus(at)hagander(dot)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(at)2ndQuadrant(dot)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:
> 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 ...

Moving it into the backend (together with the other commands) would
also solve this usabillity issue:

WARNING: You are connected to a server with major version 8.4,
but your psql client is major version 8.3. Some backslash commands,
such as \d, might not work properly.

testdb \d testtable
ERROR: column "reltriggers" does not exist
LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr...

-- ^
Jesper

On Thu, 2010-07-15 at 18:43 +0200, Magnus Hagander wrote:
> On Thu, Jul 15, 2010 at 18:35, Simon Riggs <simon(at)2ndquadrant(dot)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.com
PostgreSQL Development, 24x7 Support, Training and Services

On Thu, Jul 15, 2010 at 18:59, Simon Riggs <simon(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndQuadrant(dot)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.com
PostgreSQL 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(at)2ndQuadrant(dot)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.com
PostgreSQL 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(at)hub(dot)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(at)turnstep(dot)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(at)turnstep(dot)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(at)commandprompt(dot)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(at)2ndquadrant(dot)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(at)hub(dot)org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy(at)hub(dot)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.com
PostgreSQL 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(at)turnstep(dot)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(at)2ndQuadrant(dot)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(at)2ndquadrant(dot)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 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.
>
> 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(at)postgresql(dot)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 15. Juli 2010 18:02:10 +0200 Guillaume Lelarge
<guillaume(at)lelarge(dot)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

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(at)hub(dot)org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy(at)hub(dot)org

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(at)2ndQuadrant(dot)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(at)lelarge(dot)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(at)momjian(dot)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(at)turnstep(dot)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(at)dunslane(dot)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, 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.

This sounds roughly like the patch I submitted in January (linked upthread), although that swiped the input before it hit the backend. I don't know if I like the idea of that HINT or not.

Regards,

David
--
David Christensen
End Point Corporation
david(at)endpoint(dot)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(at)rice(dot)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.com
PostgreSQL Development, 24x7 Support, Training and Services

On Jul 15, 2010, at 2:26 PM, Richard Huxton <dev(at)archonet(dot)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 already sounds 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... from a 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.com
PostgreSQL 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.com
PostgreSQL Development, 24x7 Support, Training and Services

Le 15 juil. 2010 à 18:43, Magnus Hagander <magnus(at)hagander(dot)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 now with SELECT queries.

That means we keep a resultset per SHOW query, so it's easy on the application.

Regards,
--
dim

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

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(at)2ndquadrant(dot)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, 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(at)hub(dot)org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy(at)hub(dot)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.com
PostgreSQL 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(at)2ndquadrant(dot)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(at)agliodbs(dot)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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

2010/7/16 Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 16 July 2010 13:49, Bruce Momjian <bruce(at)momjian(dot)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.com
PostgreSQL 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.com
PostgreSQL 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 test
hpsql (9.1devel)
Type "help" for help.

test=> help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

> learnt elsewhere work either.
>
> We need a way to respond sensibly to common user input.
>
> "Terminal program" is the bit of thinking that is askew there. The
> question is "what other non-psql interactive programs are there"?
> Lots.
>
> There are many tools that can access Postgres. Some are libpq programs,
> though there are command line versions in every environment: java,
> python, etc..

Yeah, but do enough people use them to warrant putting this in the
backend?

--
Bruce Momjian <bruce(at)momjian(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)hub(dot)org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy(at)hub(dot)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(at)momjian(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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 functions is especially hairy (I poached a view from newsysviews to get what I needed).

I'd love a cleaner way to get at this information.

Best,

David

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(at)momjian(dot)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.com
PostgreSQL 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

* Simon Riggs <simon(at)2ndQuadrant(dot)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(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Bruce Momjian <bruce(at)momjian(dot)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.com
PostgreSQL Development, 24x7 Support, Training and Services

simon(at)2ndQuadrant(dot)com (Simon Riggs) writes:
> Just for the record, I've never ever met anyone that said "Oh, this
> \d syntax makes so much sense. I'm a real convert to Postgres now
> you've shown me this". The reaction is always the opposite one;
> always negative. Which detracts from our efforts elsewhere.

If we're opening up the code to change this, it makes a lot of sense
to try to Do It Really Right so that we're not going over this again
and again.

I think we're seeing several things that suck, and I'm quite sure I
have not yet heard an answer that resolves it all. Things that have
become clear:

1. \d isn't exactly the most intuitive thing ever

And it's pretty clear that we have been heading into some
increasingly cryptic bits of fruit salad of
\dfzb+-meta-bucky-alt-foo

Having SHOW THIS and SHOW THAT which are a bit more readily
guessed would be somewhat nice.

2. information_schema doesn't have some useful things that we'd like
it to have

Listing databases would be nice. Unfortunately, "ANSI didn't
define a way to do that, so we can't add it."

Alas, I don't see a good way to improve on this :-(

3. The \? commands are *solely* for psql, and it would be nice to
have the Improvement work on server side so it's not only usable
with the one client.

4. It would be Mighty Useful for whatever extensions get defined
server-side to also be "relational" so that they can be usefully
scripted in ways NOT vulnerable to screen size, output hackery,
and such.

- I've seen too many QA scripts that do awk parsing of output of
psql "\d" commands that are vulnerable to all kinds of awfulness.
Add an "updated-on" column to the output, and suddenly everything
breaks.

- I'd sure like to be able to write queries that *don't* involve
array smashing or using "grep" on \z output to analyze object
permissions.

- \? output is often *not* amenable to this, as it sometimes has
extra bits of data hierarchy in it. And "array aggregation."

There's a certain risk of things being overspecified such that there's
*no* solution, but I don't think that forcibly *has* to happen.

But the answers I'm seeing thus far run slipshod across too many of
these things, so I don't see that we have arrived at actual solutions
yet.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/linuxdistributions.html
Rules of the Evil Overlord #77. "If I have a fit of temporary insanity
and decide to give the hero the chance to reject a job as my trusted
lieutentant, I will retain enough sanity to wait until my current
trusted lieutenant is out of earshot before making the offer."
<http://www.eviloverlord.com/>

On Jul 16, 2010, at 7:43 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Andrew Dunstan wrote:
>>
>> Bruce Momjian wrote:
>>> I assume SHOW TABLES would only be useful for interactive terminal
>>> sesssions, not for application code (which should use
>>> information_schema), so what non-psql interactive terminal programs are
>>> there?
>>>
>>>
>>
>> I think your assumption is questionable.
>>
>> Plenty of people use MySQL's "SHOW TABLES" in non-interactive settings
>> (for good or ill). That's why any suggestion that we should return
>> anything other than a resultset seems like a really terrible idea to me.
>
> If they are writing an application, finding the query to show all tables
> is the least of their problems. I don't see how SHOW TABLE
> significantly helps in that case, except make things 0.001% easier,
> while creating duplicate functionality in Postgres.

Many years ago I needed to write a program that needed to be able to fetch a list of tables in the DB, and then a list of attributes for each table. It took me at least a full day and I almost gave up and abandoned PostgreSQL as a result. I think calling this duplicate functionality is ridiculous. Sure, it's possible. In fact, it's very easy.

For committers.

...Robert

On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas <robertmhaas(at)gmail(dot)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(at)gmail(dot)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(at)hub(dot)org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy(at)hub(dot)org

"Marc G. Fournier" <scrappy(at)hub(dot)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(at)momjian(dot)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(at)gmail(dot)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(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 16/07/10 20:11, Rob Wultsch wrote:
>>
>> On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas<robertmhaas(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)com> wrote:
> >> For committers.
> >
> > Perhaps this discussions should be moved to the General list in order
> > to poll the userbase.
> >
> > My .02 is that SHOW commands (even if they are not compatible) would
> > make it much easier for me to make an argument to my boss to at least
> > consider moving off another open source database. The show commands
> > are in *very* widespread use by the MySQL community even after ~5
> > years of having the i_s. The Drizzle team (a radical fork of MySQL)
> > very briefly considered removing the SHOW commands and the unanimous
> > objections that followed caused that idea to scrapped.
>
> That's for MySQL. I come from a DB2 background, and when I started using
> psql years ago, I often typed "LIST TABLES" without thinking much about
> it. Not SHOW TABLES, but LIST TABLES.

> I bet Oracle users coming to PostgreSQL will try "DESC". Not SHOW
> TABLES. As Simon listed, every DBMS out there has a different syntax for
> this.

Agreed

> I have nothing against SHOW TABLES

...but SHOW wins, based on numbers of people expecting that

> (it might cause conflicts in grammar
> though)

We don't have t handle it in the grammar. There are no parameters called
"tables", "databases" etc

> , but if we're going to cater to people migrating from MySQL, I
> feel we should cater to people migrating from other products too. But
> surely we're not going to implement 10 different syntaxes for the same
> thing! We could, however, give a hint in the syntax error in all those
> cases.

That's a very sensible suggestion, we should give a hint for all common
commands SHOW, LIST, etc., even though we pick just one to implement.

> That way we're not on the hook to maintain them forever, and we
> will be

> doing people a favor by introducing them to the backslash
> commands

That's a sentence I never thought to see written down

> or information schema, which are more powerful.

and this in no way detracts from that power and standardisation.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

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(at)2ndQuadrant(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(dot)linnakangas(at)enterprisedb(dot)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(at)commandprompt(dot)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 of them on its own would mimic \d.

Consider this psql command: psql -E -c '\d'. What I think is that each query you see there could easily become a SHOW subsyntax (from memory, we probably would have SHOW TABLE, SHOW INDEXES ON <table>, SHOW TRIGGERS ON <table>, SHOW CONSTRAINTS ON <table>, etc).

Now, psql would be free to implement its \d in terms of those new queries rather than the full SQL ones it has now, that would be a good first client. Oh and that means the design is about all done already. And that we still are in the one command - one resultset interface. Meaning any libpq driver knows how to deal with the resultset, and that's not parsing text.

I'm all with Simon here, it's not about offering any new capability that we don't already have, it's about having it handy from anywhere. So let's just have an easy syntax in the backend to do all the catalog 'magic' querying psql does, but one query at a time.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(dot)linnakangas(at)enterprisedb(dot)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(at)gmail(dot)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 Jul 16, 2010, at 11:02 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> Why must the backslash commands be more powerful than any alternative
>> we might come up with?
>
> Because they encode alot of information in a character- something which
> is next to impossible to do in "english".

I don't think that "terse" and "powerful" are the same thing. One of my beefs with the backslash commands is that the syntax is not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon as you think about going much further with it, it starts to seem like alphabet soup.

In fact, we're pretty close to alphabet soup already. Without looking at the help, what does \db do? What are the commands to list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command to list comments, but only those on a certain object type? If you don't think we should have a backslash command for that, can you write an SQL query that lists comments on built-in aggregates in less than two minutes? How many people do you think can do it at all?

I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward in usability.

...Robert

Tim Landscheidt <tim(at)tim-landscheidt(dot)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(at)snowman(dot)net> wrote:
> > * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> >> Why must the backslash commands be more powerful than any alternative
> >> we might come up with?
> >
> > Because they encode alot of information in a character- something which
> > is next to impossible to do in "english".
>
> I don't think that "terse" and "powerful" are the same thing. One of my beefs with the backslash commands is that the syntax is not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon as you think about going much further with it, it starts to seem like alphabet soup.
>
> In fact, we're pretty close to alphabet soup already. Without looking at the help, what does \db do? What are the commands to list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command to list comments, but only those on a certain object type? If you don't think we should have a backslash command for that, can you write an SQL query that lists comments on built-in aggregates in less than two minutes? How many people do you think can do it at all?
>
> I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward in usability.

+1

JD

>
> ...Robert

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering

2010/7/17 Joshua D. Drake <jd(at)commandprompt(dot)com>:
> On Sat, 2010-07-17 at 09:02 -0500, Robert Haas wrote:
>> On Jul 16, 2010, at 11:02 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> > * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> >> Why must the backslash commands be more powerful than any alternative
>> >> we might come up with?
>> >
>> > Because they encode alot of information in a character- something which
>> > is next to impossible to do in "english".
>>
>> I don't think that "terse" and "powerful" are the same thing. One of my beefs with the backslash commands is that the syntax is not cleanly extensible.  We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon as you think about going much further with it, it starts to seem like alphabet soup.
>>
>> In fact, we're pretty close to alphabet soup already. Without looking at the help, what does \db do?  What are the commands to list casts, conversions, and comments, respectively?  What syntax would you propose for a backslash command to list comments, but only those on a certain object type?  If you don't think we should have a backslash command for that, can you write an SQL query that lists comments on built-in aggregates in less than two minutes?  How many people do you think can do it at all?
>>
>> I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward in usability.

Every time I like psql from one reason, It is clean, what is SQL
(server side) command and what is psql command (backslash command). So
I am against to implement similar commands.

Regards
Pavel Stehule
>
> +1
>
> JD
>
>>
>> ...Robert
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)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(at)snowman(dot)net> wrote:
>> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>>> Why must the backslash commands be more powerful than any alternative
>>> we might come up with?
>>
>> Because they encode alot of information in a character- something which
>> is next to impossible to do in "english".
>
> I don't think that "terse" and "powerful" are the same thing. One of my beefs with the backslash commands is that the syntax is not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon as you think about going much further with it, it starts to seem like alphabet soup.
>
> In fact, we're pretty close to alphabet soup already. Without looking at the help, what does \db do? What are the commands to list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command to list comments, but only those on a certain object type? If you don't think we should have a backslash command for that, can you write an SQL query that lists comments on built-in aggregates in less than two minutes? How many people do you think can do it at all?
>
> I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward in usability.

uh oh - that actually sounds like a big step backwards to me - it's
inventing extremely verbose pseudo english syntax for something that we
currently do with a trivial and easy to remember backslash command.
Do we really need to invent a completely new language for this?
Once you extend that syntax to what you are proposing (ie provide a way
to filter like "LIST COMMENTS ON SYSTEM AGGREGATES WITH NUMERIC INPUT")
you basically reinvented a query language - ever heard of SQL or QUEL?
I'm not sure where to draw the line but implementing a proper shortcut
interface for cammands is something taht should be done on the client
side because not every client is the same and the needs of psql might be
radically different from any other client (like pgadmin or a fancy Web
2.0 AJAX thingy - those will likely always use custom catalog queries).
Maybe a differnet way to look at the whole thing is to reconsider our
own catalogs (anyone remember newsysview?) and add a bunch of views to
abstract away most of the current complexity for these usecases?

Stefan

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(at)snowman(dot)net> wrote:
> >> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> >>> Why must the backslash commands be more powerful than any alternative
> >>> we might come up with?
> >>
> >> Because they encode alot of information in a character- something which
> >> is next to impossible to do in "english".
> >
> > I don't think that "terse" and "powerful" are the same thing. One of my beefs with the backslash commands is that the syntax is not cleanly extensible. We have S and + as postfix modifiers, and that's fairly comprehensible, but as soon as you think about going much further with it, it starts to seem like alphabet soup.
> >
> > In fact, we're pretty close to alphabet soup already. Without looking at the help, what does \db do? What are the commands to list casts, conversions, and comments, respectively? What syntax would you propose for a backslash command to list comments, but only those on a certain object type? If you don't think we should have a backslash command for that, can you write an SQL query that lists comments on built-in aggregates in less than two minutes? How many people do you think can do it at all?
> >
> > I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward in usability.
>
>
> uh oh - that actually sounds like a big step backwards to me - it's
> inventing extremely verbose pseudo english syntax for something that we
> currently do with a trivial and easy to remember backslash command.

By whose estimation?

I hate the backslash commands and I have been using them longer than
most.

I do agree that the above is a bit verbose but it is also blatant as to
what it is.

> Do we really need to invent a completely new language for this?
> Once you extend that syntax to what you are proposing (ie provide a way
> to filter like "LIST COMMENTS ON SYSTEM AGGREGATES WITH NUMERIC INPUT")
> you basically reinvented a query language - ever heard of SQL or QUEL?

Really? Hmmm....

SELECT * FROM (where are system aggregates again?), oh right, pg_proc,
what is the column that tells me it is a system aggregate? -- Do I
filter by namespace?

Oh, crimey, why can't I just type:

SHOW COMMENTS ON SYSTEM AGGREGATES (or LIST)

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering

On Sat, Jul 17, 2010 at 5:30 PM, Stefan Kaltenbrunner
<stefan(at)kaltenbrunner(dot)cc> wrote:
> On 07/17/2010 04:02 PM, Robert Haas wrote:
>> On Jul 16, 2010, at 11:02 PM, Stephen Frost<sfrost(at)snowman(dot)net>  wrote:
>>> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>>>>
>>>> Why must the backslash commands be more powerful than any alternative
>>>> we might come up with?
>>>
>>> Because they encode alot of information in a character- something which
>>> is next to impossible to do in "english".
>>
>> I don't think that "terse" and "powerful" are the same thing. One of my
>> beefs with the backslash commands is that the syntax is not cleanly
>> extensible.  We have S and + as postfix modifiers, and that's fairly
>> comprehensible, but as soon as you think about going much further with it,
>> it starts to seem like alphabet soup.
>>
>> In fact, we're pretty close to alphabet soup already. Without looking at
>> the help, what does \db do?  What are the commands to list casts,
>> conversions, and comments, respectively?  What syntax would you propose for
>> a backslash command to list comments, but only those on a certain object
>> type?  If you don't think we should have a backslash command for that, can
>> you write an SQL query that lists comments on built-in aggregates in less
>> than two minutes?  How many people do you think can do it at all?
>>
>> I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic step forward
>> in usability.
>
> uh oh - that actually sounds like a big step backwards to me - it's
> inventing extremely verbose pseudo english syntax for something that we
> currently do with a trivial and easy to remember backslash command.

Which trivial and easy-to-remember backslash command is that?

> Do we really need to invent a completely new language for this?
> Once you extend that syntax to what you are proposing (ie provide a way to
> filter like "LIST COMMENTS ON SYSTEM AGGREGATES WITH NUMERIC INPUT") you
> basically reinvented a query language - ever heard of SQL or QUEL?

Uhm, yes! I'm not going to say the SQL is the epitome of language
design, but actually I've done a fair amount of work on a database
product that uses it heavily - and I rather like it, on the whole. I
notice that you didn't actually answer any of the points that I raised
in the "alphabet soup" paragraph above. I don't think there's
anything WRONG with letting "\dFp" show text search dictionaries and
"\dfwS+" list system window functions with additional detail - but I'd
like an alternative that emphasizes ease of remembering over brevity,
works in every client, and can be extended in whatever reasonable ways
the community decides are worth having.

When we committed the patch to add extensible options to EXPLAIN, I
didn't know exactly what options we were going to end up with - but I
knew that somebody else would think up use cases for the new
functionality, and so it proved. In 9.0 we have EXPLAIN (BUFFERS),
something which would have gotten shot down if it had necessitated the
use of the old syntax, due to keyword proliferation, and it wouldn't
surprise me if additional options get added in the future. In the
same way, I'm not exactly sure how far and in what direction we might
decide to extend the syntax of any query-language-based routines to
list or describe database objects - but I'm almost positive that the
current requirement that they be able to expressed as a backslash
command is limiting what we can do.

I'd like to be able to list comments on objects of a particular type.
And, yeah, I'd like to be able to list all the aggregates that take a
numeric argument, or all the functions that take, say, an argument of
type internal. Right now, this is an ENORMOUS pain in the neck. I
usually end up running psql -c '<some backslash command>' | grep |
awk ... or something like that. I have no idea what Windows users do.
I'm sure it's possible to write a query to do it, but it's not
anything approaching easy. All of this talk about backslash commands
being powerful rings totally hollow for me. For ordinary, day to day
tasks like listing all my tables, or looking at the details of a
particular table, they're great. I use them all the time and would
still use them even if some other syntax were available. But there is
no reasonable way to pass options to them, and that to me is a pretty
major drawback.

> I'm not sure where to draw the line but implementing a proper shortcut
> interface for cammands is something taht should be done on the client side
> because not every client is the same and the needs of psql might be
> radically different from any other client (like pgadmin or a fancy Web 2.0
> AJAX thingy - those will likely always use custom catalog queries).
> Maybe a differnet way to look at the whole thing is to reconsider our own
> catalogs (anyone remember newsysview?) and add a bunch of views to abstract
> away most of the current complexity for these usecases?

Our previous experiments in this area haven't been wildly successful.
For example, we have pg_tables, but suppose you want to augment the
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(at)momjian(dot)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(at)momjian(dot)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

Hi,

Le 18 juil. 2010 à 05:41, Robert Haas a écrit :
> On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> I am concerned that implementing a command syntax to show complex output
>> like above effectively means re-implementing a subset of SQL, and that
>> subset will never be as flexible.
>
> That's a reasonable concern, but I don't have a better idea. Do you?

I think that SHOW could be some syntax sugar atop the current rewrite rules system. I mean it would be implemented by means of "parametrized" views. It could be that SQL only SRFs could do a better job at it. In both cases the idea is that we should be able to write SELECT like statements.

SHOW TABLE foo;

SHOW TABLES WHERE tablename ~ 'foo';

SHOW ANY TABLE
GROUP BY tablename
HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype];

The last one has an "english like" trick using ANY rather than ALL, but that's just for the bikesheding of it, and would list all tables with both a date and a time column. The trick is there because if you want the attributes to show up you're after enhancing the SHOW TABLE query, not the SHOW TABLES one.

So what we'd need first is a series of named queries, which I think psql provides for. Then some technique to have them available both as plain and easy usage and in full SQL. I think the rewrite system is meant to allow that, I'm not sure if using views or pure SQL SRFs is better, in both cases the rewritten query has to provide arguments "placeholders": if a VIEW, that's a WHERE clause, if a SRF, any number of named arguments.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

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(at)hi-media(dot)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(at)anarazel(dot)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(at)gmail(dot)com