Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Tue, Sep 21, 2010 at 4:52 AM, Boszormenyi Zoltan  wrote:
> I think it's related to making this work:
>    SELECT * FROM db.schema.table;

Which is a non-starter, I think.  Every function in the system that
thinks an OID uniquely identifies a database object would need to
modified, or else you'd need unique indices that can span tables in
multiple different databases.  It would also require blowing a massive
hole in the isolation wall between databases, and reengineering of
every place that thinks a backend can be connected to only one
database at a time.  None of which would be good for either code
stability or performance.

The only way I can imagine making this work is if any references of
that type got treated like foreign tables: spawn a "helper backend"
connected to the correct DB (failing if you haven't permissions), and
then stream the tuples back to the main backend from there.
Considering the amount of effort that would be required for the amount
of benefit you'd actually derive from it, I doubt anyone is likely to
tackle this any time soon...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Boszormenyi Zoltan]

Guillaume Lelarge írta:
> Le 15/07/2010 17:48, Joshua D. Drake a écrit :
>   
>> On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote:
>> 
>>> On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:
>>>   
 Simon Riggs  writes:
 
> The biggest turn off that most people experience when using PostgreSQL
> is that psql does not support memorable commands.
>   
> I would like to implement the following commands as SQL, allowing them
> to be used from any interface.
>   
> SHOW TABLES
> SHOW COLUMNS
> SHOW DATABASES
>   
 This has been discussed before, and rejected before.  Please see
 archives.
 
>>> Many years ago. I think it's worth revisiting now in light of the number
>>> of people now joining the PostgreSQL community and the greater
>>> prevalence other ways of doing it. The world has changed, we have not.
>>>
>>> I'm not proposing any change in function, just a simpler syntax to allow
>>> the above information to be available, for newbies.
>>>
>>> Just for the record, I've never ever met anyone that said "Oh, this \d
>>> syntax makes so much sense. I'm a real convert to Postgres now you've
>>> shown me this". The reaction is always the opposite one; always
>>> negative. Which detracts from our efforts elsewhere.
>>>   
>> I have to agree with Simon here. \d is ridiculous for the common user.
>>
>> SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like
>> DESCRIBE TABLE foo makes a lot more sense than \d.
>>
>> 
>
> And would you add the complete syntax? I mean:
>
>   SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
>
> I'm wondering what one can do with the [FROM db_name] clause :)
>   

I think it's related to making this work:
SELECT * FROM db.schema.table;

Best regards,
Zoltán Böszörményi

-- 
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

Bruce Momjian  wrote:
> Kevin Grittner wrote:
 
>> I can't picture anything which could be done with views which
>> would allow me to issue one statement and see everything of
>> interest about a table (etc.).  You know: tablespace, owner,
>> permissions, columns, primary key, foreign keys, check
>> constraints, exclusion constraints, ancestor tables, child
>> tables, and whatever interesting features I missed or we later
>> add.  Other products allow that to be generated server-side, so
>> that it is available to any and all clients.  I think we should
>> join the crowd in this respect.
> 
> Consider if the server-side description comes to the client
> unformatted, then that format is going to changes as Postgres adds
> features, and that might really make the output useless except for
> raw display purposes.
 
Yeah, sending it back as unformatted text would be horrible.  See
this post:
 
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00738.php
 
If you follow the link there, you will see examples of another
product returning up to 11 result sets and a few INFO level messages
in response to a single "sp_help objectname".  If it's technically
feasible for PostgreSQL to do something like that, it would
absolutely rock.  Result sets have enough structure to them to be
able to write code dealing with such behavior with relative
confidence.
 
For more naive ramblings by me on the topic, see:
 
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00752.php
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00892.php
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00909.php
 
Nobody has yet come out and said that these ideas *aren't*
technically feasible, so I continue to live with my dreams intact. 
So far
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

Kevin Grittner wrote:
> Stefan Kaltenbrunner  wrote:
> > On 07/18/2010 08:58 PM, Andres Freund wrote:
>  
> >> I am quite a bit surprised about all this discussion. I have a
> >> very hard time we will find anything people agree about and can
> >> remember well enough to be usefull for both manual and automatic
> >> processing.
> >>
> >> I agree that the internal pg_* tables are not exactly easy to
> >> query. And that the information_schema. ones arent complete
> >> enough and have enough concept mismatch to be confusing. But why
> >> all this?
> > 
> > exactly my thoughts - but as I said earlier maybe this is actually
> > an opportunity to look at newsysviews again?
>  
> I can't picture anything which could be done with views which would
> allow me to issue one statement and see everything of interest about
> a table (etc.).  You know: tablespace, owner, permissions, columns,
> primary key, foreign keys, check constraints, exclusion constraints,
> ancestor tables, child tables, and whatever interesting features I
> missed or we later add.  Other products allow that to be generated
> server-side, so that it is available to any and all clients.  I
> think we should join the crowd in this respect.

Consider if the server-side description comes to the client unformatted,
then that format is going to changes as Postgres adds features, and that
might really make the output useless except for raw display purposes.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Mon, Aug 9, 2010 at 5:42 PM, Bruce Momjian  wrote:
> Sorry for the late reply.  If we are going to end up recreating SQL, we
> might as well just keep the backslash mess we have, or tell them to use
> SQL for the complex queries.  My point was that we might find that what
> we cook up is as complex as what we have now.

I think that would require malice aforethought.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

On Mon, 2010-08-09 at 17:42 -0400, Bruce Momjian wrote:
> Robert Haas wrote:
> > On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian  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 '' | ?grep |
> > >> awk ... or something like that. ?I have no idea what Windows users do.
> > >> ?I'm sure it's possible to write a query to do it, but it's not
> > >> anything approaching easy. ?All of this talk about backslash commands
> > >> being powerful rings totally hollow for me. ?For ordinary, day to day
> > >> tasks like listing all my tables, or looking at the details of a
> > >> particular table, they're great. ?I use them all the time and would
> > >> still use them even if some other syntax were available. ?But there is
> > >> no reasonable way to pass options to them, and that to me is a pretty
> > >> major drawback.
> > >
> > > I am concerned that implementing a command syntax to show complex output
> > > like above effectively means re-implementing a subset of SQL, and that
> > > subset will never be as flexible.
> > 
> > That's a reasonable concern, but I don't have a better idea.  Do you?
> 
> Sorry for the late reply.  If we are going to end up recreating SQL, we
> might as well just keep the backslash mess we have, or tell them to use
> SQL for the complex queries.  My point was that we might find that what
> we cook up is as complex as what we have now.

My proposal is that SHOW TABLES returns exactly the same output as \d
yet works the same from every interface.

I have no intention of designing or writing what Robert proposes above
and he is welcome to do that, but its clearly a different requirement.

SHOW TABLES is a simple command with a simple purpose: helping newbies
by putting obvious commands in their way that do useful things. The
simple goal I've outlined for SHOW TABLES has nothing whatsoever to do
with inventing what appears to be a new flexible catalog metadata
language that is hard to implement, non-standard and poorly understood
except by only a few people.

Currently, \d runs this SQL

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

I would make this into a server view, and then make SHOW TABLES a
synonym for SELECT * FROM pg_show_backslash_d. I'd probably work out a
better name for the view also. It's mostly just refactoring.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

Robert Haas wrote:
> On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian  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 '' | ?grep |
> >> awk ... or something like that. ?I have no idea what Windows users do.
> >> ?I'm sure it's possible to write a query to do it, but it's not
> >> anything approaching easy. ?All of this talk about backslash commands
> >> being powerful rings totally hollow for me. ?For ordinary, day to day
> >> tasks like listing all my tables, or looking at the details of a
> >> particular table, they're great. ?I use them all the time and would
> >> still use them even if some other syntax were available. ?But there is
> >> no reasonable way to pass options to them, and that to me is a pretty
> >> major drawback.
> >
> > I am concerned that implementing a command syntax to show complex output
> > like above effectively means re-implementing a subset of SQL, and that
> > subset will never be as flexible.
> 
> That's a reasonable concern, but I don't have a better idea.  Do you?

Sorry for the late reply.  If we are going to end up recreating SQL, we
might as well just keep the backslash mess we have, or tell them to use
SQL for the complex queries.  My point was that we might find that what
we cook up is as complex as what we have now.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

David Fetter  wrote:
 
> Would something like this do?  Thanks to Andrew Gierth for helping
> me figure out how to get this working :)
> 
> CREATE OR REPLACE FUNCTION multi_result()
> RETURNS SETOF REFCURSOR
 
With appropriate tweaks to JDBC and the other drivers, this would
cover a lot of ground.  You might be able to cover the last little
bit by returning a SETOF some record with (at least) three columns,
one of which would be filled in each row: REFCURSOR (for a result
set), INTEGER (for a row count), and something which could carry an
object which would map to a SQLWarning (which can be used with
SQLSTATE '0' to deliver informational text or '01xxx' for actual
warnings).  A JDBC execute request (as opposed to executeUpdate or
executeQuery) may get back any combination of the above in an
ordered fashion.  Essentially, this meta result set would need to be
hidden within the Statement object.
 
http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/Statement.html#execute%28java.lang.String%29
 
http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/Statement.html#getWarnings%28%29
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[David Fetter]

On Mon, Jul 19, 2010 at 09:31:06AM -0500, Kevin Grittner wrote:
> >Stephen Frost  wrote:
>  
> > You think that the users of the libpq() interface (or even the
> > protocol itself) are going to handle getting \dt-type output back
> > somehow..?
>  
> If you look back in the thread, you'll see that I admitted my
> ignorance of whether this could be properly implemented in the back
> end without a protocol change.  Ignorance being bliss, I can revel
> in the dreams of *having* such a feature without being dragged down
> by the potential pain of its implementation.  ;-)
>  
> I know, though, that the JDBC spec supports such things -- you can
> keep pulling ResultSet objects off the wire, each with its own
> distinct set of columns.  (That is, each ResultSet has its own
> ResultSetMetaData which specifies how many columns that particular
> ResultSet has, what the column names are, what the data type is for
> each column, etc.  Each ResultSet returned from a response stream
> for a request can be entirely different in all of these
> characteristics.)

Would something like this do?  Thanks to Andrew Gierth for helping me
figure out how to get this working :)

CREATE OR REPLACE FUNCTION multi_result()
RETURNS SETOF REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
ref REFCURSOR;
BEGIN
FOR r IN
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'information_schema'
LOOP
ref := 'multi_result_' || quote_ident(r.table_name);
OPEN ref FOR EXECUTE 'SELECT * FROM information_schema.' || 
quote_ident(r.table_name); /* Not really needed. */
RETURN NEXT ref;
ref := NULL;
END LOOP;
RETURN;
END;
$$;

BEGIN;
SELECT * FROM multi_result();
FETCH FORWARD ALL FROM multi_result_views;
ROLLBACK;

> > As what, a single-column result of type text?
>  
> No, that would be horrible.  That has been mentioned as a

+1 on the shuddering.  Add also nausea. :P

> > And then they'll use non-fixed-width fonts, undoubtably, which
> > means the results will end up looking rather ugly, even if we put
> > in the effort to format the results.
>  
> With, for example, Sybase's sp_help, each result set can be listed
> any way the client chooses -- I've seen it put into character format
> like the psql \d commands, I've seen each result set put into a
> table for brower-based query tools, and I've seen each result set
> put into a JTable for Java Swing applications.  If a client gets
> back a series of result sets, the sky is the limit.

Ad astra per PostgreSQL!

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

David Fetter wrote:

>> No arguments there, but that's the nature of the beast. I don't 
>> think it's as bad as is made out, however, as \d covers 99% of 
>> everyday usage and certainly the "show tables" that started 
>> this thread.

> It covers 0% of cases where people are not using psql.

Yes, and everything else already has a "show tables". See 
for example, PPA:

http://phppgadmin.sourceforge.net/images/4.png

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201007191342
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkxEj4kACgkQvJuQZxSWSshrwgCg65eIziE2SW8XhdTSHwVMzxnm
ynIAoLPOc0yuKyrE2kaaJFq5UiDb45Nd
=veva
-END PGP SIGNATURE-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Joshua D. Drake]

On Mon, 2010-07-19 at 10:23 -0700, David Fetter wrote:
> On Mon, Jul 19, 2010 at 02:12:19PM -, Greg Sabino Mullane wrote:
> > 
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: RIPEMD160
> > 
> > 
> > > 1.  \d isn't exactly the most intuitive thing ever
> > 
> > Seems fairly mnemomic to me (d=describe) and it packs a 
> > *lot* of information into a single letter (see below). 
> > Things that are done often should have short keystrokes, 
> > and not require learning Yet Another Meta-Language.
> > 
> > > And it's pretty clear that we have been heading into some
> > > increasingly cryptic bits of fruit salad of
> > > \dfzb+-meta-bucky-alt-foo
> > 
> > No arguments there, but that's the nature of the beast. I don't 
> > think it's as bad as is made out, however, as \d covers 99% of 
> > everyday usage and certainly the "show tables" that started 
> > this thread.
> 
> It covers 0% of cases where people are not using psql.

Which is probably 85% of our users. (No I have no actual metric)

Every single corp I have walked into is using at least PgAdmin as well.

Until this project as a whole recognizes that for the majority to the
populace the command line is dead; we will continue to have these
arguments for no apparent reason but to make sure spam filters are still
reading our emails.


JD


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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[David Fetter]

On Mon, Jul 19, 2010 at 02:12:19PM -, Greg Sabino Mullane wrote:
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
> 
> 
> > 1.  \d isn't exactly the most intuitive thing ever
> 
> Seems fairly mnemomic to me (d=describe) and it packs a 
> *lot* of information into a single letter (see below). 
> Things that are done often should have short keystrokes, 
> and not require learning Yet Another Meta-Language.
> 
> > And it's pretty clear that we have been heading into some
> > increasingly cryptic bits of fruit salad of
> > \dfzb+-meta-bucky-alt-foo
> 
> No arguments there, but that's the nature of the beast. I don't 
> think it's as bad as is made out, however, as \d covers 99% of 
> everyday usage and certainly the "show tables" that started 
> this thread.

It covers 0% of cases where people are not using psql.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Mon, Jul 19, 2010 at 10:25 AM, Greg Sabino Mullane  wrote:
>> in the "alphabet soup" paragraph above.  I don't think there's
>> anything WRONG with letting "\dFp" show text search dictionaries and
>> "\dfwS+" list system window functions with additional detail - but I'd
>> like an alternative that emphasizes ease of remembering over brevity,
>> works in every client, and can be extended in whatever reasonable ways
>> the community decides are worth having.
> ...
> I don't know that I'd necessarily remember all those any better, and would
> certainly not enjoy typing out:
>
> LIST TEST SEARCH DICTIONARIES
>
> I don't have to remember \dFp - all I have to remember is \?. For the
> more common ones that I use day to day and don't have to look up
> (\d \dt \df \l etc.) the advantage of a two or three character
> string is strong.

I don't think anyone is proposing getting rid of backslash commands.
That would be nuts.  What's being proposed is to try to create a
better way to list objects, a way that involves some server-side
support so that clients don't need to muck about with system catalogs
quite so much.

I like psql as well as anyone, but saying that it's easy to do this
stuff because you can use \? to get the appropriate backslash command
seems to me to be missing the point.  Suppose you regularly use
PGadmin to access the database, or some other graphical client, and
you want to find a query to list the comments on every item in the
database.  Good luck!  You'll need to figure out how to use psql,
discover that it has a -E switch (of which I was ignorant for my first
10 years of using PostgreSQL), and get the query out of there.  Then
you'll find that the query psql uses is more than 50 lines long and
also wrong: it omits half the object types.  Woohoo!

And even supposing that you fix the query, there's no guarantee that
it won't be wrong again when PG version X+1 comes out.  Take a look at
describe.c.  It's riddled with special cases for particular PG
versions, special cases that must be replicated in every other client
that wants to work with multiple PG versions.  So, basically, our
advice to anyone who wants a simple, portable way to list objects of
particular types in a cross-PG version compatible way is - copy the
logic in describe.c, adapt it to your application, and update it every
time a new major release comes out.  Is that really the best we can
do, and do we really think that's adequate?

>> being powerful rings totally hollow for me.  For ordinary, day to day
>> tasks like listing all my tables, or looking at the details of a
>> particular table, they're great.  I use them all the time and would
>> still use them even if some other syntax were available.  But there is
>> no reasonable way to pass options to them, and that to me is a pretty
>> major drawback.
>
> Well, there's the rub. You're arguing this from a hacker's persepective,
> while the SHOW syntax seems to be overwhelmingly agreed upon to be either
> helpful for clueless noobs, or some nice syntactic sugar for average users.

I use the darn database, too.  The machinations I've gone through to
get some of the information are ridiculously complex.  As Larry Wall
one said, a good programming language should make simple things simple
and complicated things possible; so, I don't believe that having a
simple interface and a powerful interface are mutually exclusive
goals.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Stephen Frost]

* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote:
> I know, though, that the JDBC spec supports such things -- you can
> keep pulling ResultSet objects off the wire, each with its own
> distinct set of columns.  (That is, each ResultSet has its own
> ResultSetMetaData which specifies how many columns that particular
> ResultSet has, what the column names are, what the data type is for
> each column, etc.  Each ResultSet returned from a response stream
> for a request can be entirely different in all of these
> characteristics.)

I'm pretty sure we don't have the ability to support that at either the
libpq or the protocol level today, but in general I do think it's a good
idea and would be good to support.  To be honest, I seem to recall
someone talking about working on it (or perhaps it's on the TODO?).  In
any case, long way there from here.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

>Stephen Frost  wrote:
 
> You think that the users of the libpq() interface (or even the
> protocol itself) are going to handle getting \dt-type output back
> somehow..?
 
If you look back in the thread, you'll see that I admitted my
ignorance of whether this could be properly implemented in the back
end without a protocol change.  Ignorance being bliss, I can revel
in the dreams of *having* such a feature without being dragged down
by the potential pain of its implementation.  ;-)
 
I know, though, that the JDBC spec supports such things -- you can
keep pulling ResultSet objects off the wire, each with its own
distinct set of columns.  (That is, each ResultSet has its own
ResultSetMetaData which specifies how many columns that particular
ResultSet has, what the column names are, what the data type is for
each column, etc.  Each ResultSet returned from a response stream
for a request can be entirely different in all of these
characteristics.)
 
> As what, a single-column result of type text?
 
No, that would be horrible.  That has been mentioned as a
possibility, and it makes me shudder.
 
> And then they'll use non-fixed-width fonts, undoubtably, which
> means the results will end up looking rather ugly, even if we put
> in the effort to format the results.
 
With, for example, Sybase's sp_help, each result set can be listed
any way the client chooses -- I've seen it put into character format
like the psql \d commands, I've seen each result set put into a
table for brower-based query tools, and I've seen each result set
put into a JTable for Java Swing applications.  If a client gets
back a series of result sets, the sky is the limit.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Kevin Grittner wrote:

> Any solution which only works within psql isn't a solution for a
> large part of the problem space people are trying to address.  One
> important goal is that if someone spends a day to whip up a GUI
> query tool (as I did when I first started working in Java), it's
> easy to get displays like we get from the psql backslash commands
> (as it was in Sybase, which is what we were using at the time,
> through sp_help and related stored procedures).

I don't agree that this is an important goal. Certainly someone 
writing a GUI (or a new driver) should be expected to be familiar 
with the system catalogs. Moreover, a GUI relies on an underlying 
driver, and every driver should already be providing things like 
a list of tables natively.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201007191030
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkxEYZ0ACgkQvJuQZxSWSsiIlQCfdXDgTqletVez/r+pKHY4EcW6
QAsAoPLUmblzN2aNEw5DveHEav3XyB/K
=TGq1
-END PGP SIGNATURE-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Robert Haas (robertmh...@gmail.com) wrote:
> I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic 
> step forward in usability.

Perhaps. But it would behoove you to come up with a less er...
arcane example. I've been using Postgres a long time, and I can 
count the number of times I've needed to see comments on system 
aggregates on my hand. With at least four fingers left over.
...
> in the "alphabet soup" paragraph above.  I don't think there's
> anything WRONG with letting "\dFp" show text search dictionaries and
> "\dfwS+" list system window functions with additional detail - but I'd
> like an alternative that emphasizes ease of remembering over brevity,
> works in every client, and can be extended in whatever reasonable ways
> the community decides are worth having.
...
I don't know that I'd necessarily remember all those any better, and would 
certainly not enjoy typing out:

LIST TEST SEARCH DICTIONARIES

I don't have to remember \dFp - all I have to remember is \?. For the 
more common ones that I use day to day and don't have to look up 
(\d \dt \df \l etc.) the advantage of a two or three character 
string is strong.

(There is some devil's advocate in there - a standard cross client 
(and dare I say it, cross RDBMS?) way would be nice)

...
> being powerful rings totally hollow for me.  For ordinary, day to day
> tasks like listing all my tables, or looking at the details of a
> particular table, they're great.  I use them all the time and would
> still use them even if some other syntax were available.  But there is
> no reasonable way to pass options to them, and that to me is a pretty
> major drawback.

Well, there's the rub. You're arguing this from a hacker's persepective, 
while the SHOW syntax seems to be overwhelmingly agreed upon to be either 
helpful for clueless noobs, or some nice syntactic sugar for average users.

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

Yep, agreed. Now, if we can just agree to put information_schema in the default 
search_path, because nobody enjoys having to type out "information_schema"...

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201007191021
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkxEYDgACgkQvJuQZxSWSsikFwCdGo88Ehdcm8OHi2+VxISTG60Y
b9sAoLsetxcpdMSconsCwj+3Xa1fCCzo
=3aM1
-END PGP SIGNATURE-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


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

Seems fairly mnemomic to me (d=describe) and it packs a 
*lot* of information into a single letter (see below). 
Things that are done often should have short keystrokes, 
and not require learning Yet Another Meta-Language.

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

No arguments there, but that's the nature of the beast. I don't 
think it's as bad as is made out, however, as \d covers 99% of 
everyday usage and certainly the "show tables" that started 
this thread.

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

I'm not sure why "easily guessed" is thrown out in this thread as 
such a great thing. To achieve that goal, we simply need the 
help system that has been proposed many times: entering in 
"SHOW " gives you a quick rundown of the backslash system.

As far as SHOW THIS, there is a big difference from a plain "\dt" 
and "\d ". The former could be emulated quite easily 
with a SHOW command (although even our \dt prints out more information 
than mysql's SHOW TABLES), but the latter includes a crazy amount 
of information that would lead to quite a large "SHOW..." statement. 
Also, if it were made a server-side thing, how would you return things 
like indexes on a table in a SRF? Have a meta-column describing what 
the other columns represent? Ugly.

> information_schema doesn't have some useful things that we'd like
> ait to have
...
> Alas, I don't see a good way to improve on this :-(

newsysviews seems the way out of that particular mess. I'm also not 
particularly opposed to adding new views or columns to 
information_schema. We would still support the standard by 
having all the required views and columns.

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

Agreed, but is there some other command-line client? If it's not 
command-line, free-form SQL typing, it inevitably already has 
support for querying the catalogs built in. At least, every GUI, 
app, and driver I can think of does.

> I've seen too many QA scripts that do awk parsing of output of
> psql "\d" commands that are vulnerable to all kinds of awfulness.

They should be querying information_schema.

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

Yeah, that would be a better information_schema. :)

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201007191011
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkxEXS0ACgkQvJuQZxSWSshLKwCffkfe0T3tELInxRqG7yCDS5Vr
Ku8AoLUtOu7tTplGZZLPOEuDfKHt+EEm
=Oubu
-END PGP SIGNATURE-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Stephen Frost]

Kevin,

* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote:
> I can't picture anything which could be done with views which would
> allow me to issue one statement and see everything of interest about
> a table (etc.).  You know: tablespace, owner, permissions, columns,
> primary key, foreign keys, check constraints, exclusion constraints,
> ancestor tables, child tables, and whatever interesting features I
> missed or we later add.

You think that the users of the libpq() interface (or even the protocol
itself) are going to handle getting \dt-type output back somehow..?  As
what, a single-column result of type text?  And then they'll use
non-fixed-width fonts, undoubtably, which means the results will end up
looking rather ugly, even if we put in the effort to format the results.

I'm becoming more and more inclined to just address this with
newsysviews and encouraging use of the existing TABLE top-level command
for people who have issue with 'SELECT *'.

> Other products allow that to be generated
> server-side, so that it is available to any and all clients.  I
> think we should join the crowd in this respect.

I could see some things being done this way, but the entire \dt output
for a given table strikes me as stretching it pretty far..  And only
doing it half-way doesn't strike me as a very good idea.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Andres Freund]

Hi Kevin,

On Sunday 18 July 2010 21:24:25 Kevin Grittner wrote:
> Stefan Kaltenbrunner  wrote:
> > On 07/18/2010 08:58 PM, Andres Freund wrote:
> >> I am quite a bit surprised about all this discussion. I have a
> >> very hard time we will find anything people agree about and can
> >> remember well enough to be usefull for both manual and automatic
> >> processing.
> >> 
> >> I agree that the internal pg_* tables are not exactly easy to
> >> query. And that the information_schema. ones arent complete
> >> enough and have enough concept mismatch to be confusing. But why
> >> all this?
> > 
> > exactly my thoughts - but as I said earlier maybe this is actually
> > an opportunity to look at newsysviews again?
> 
> I can't picture anything which could be done with views which would
> allow me to issue one statement and see everything of interest about
> a table (etc.).  You know: tablespace, owner, permissions, columns,
> primary key, foreign keys, check constraints, exclusion constraints,
> ancestor tables, child tables, and whatever interesting features I
> missed or we later add.  Other products allow that to be generated
> server-side, so that it is available to any and all clients.  I
> think we should join the crowd in this respect.
Such tables sure do not fit queries as in

On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote:
> SHOW ANY TABLE
> GROUP BY tablename
>  HAVING array_agg(attributes) @>  array['date'::regtype, 'time'::regtype];
At least I dont see any way how you could define aggregation or such sensibly 
here.

Thats the part which scares me quite a bit.

Andres

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Stefan Kaltenbrunner]

On 07/18/2010 09:00 PM, Kevin Grittner wrote:

Dimitri Fontaine  wrote:


So what we'd need first is a series of named queries, which I
think psql provides for.


Any solution which only works within psql isn't a solution for a
large part of the problem space people are trying to address.  One
important goal is that if someone spends a day to whip up a GUI
query tool (as I did when I first started working in Java), it's
easy to get displays like we get from the psql backslash commands
(as it was in Sybase, which is what we were using at the time,
through sp_help and related stored procedures).


yeah but having to call a SP is basically the same as formulating a 
query - the point really is that it is completely up to the client to 
think of a suitable representation for the information and the interface 
for the user to select data.
Just implementing something in the server that either shows "everything" 
(whatever that really is in practice) will very often not match to what 
the tool really wants. And once we are into "providing something that 
can do arbitrary stuff like filtering or output manipulation" we are 
back to where we are - issueing an SQL-query against the catalog.




While the four DBAs use psql heavily, the twenty-some programmers
and the business analysts all use various GUI tools which either tie
in to their normal environments (for example, eclipse) or are web
based hacks which probably didn't take much more effort than the
above-mentioned GUI hack which I used for about ten years.
Backslash commands do them no good whatsoever, nor will any solution
which requires psql.

It would be nice if when I display information about a table or some
other database object, I could copy from my psql session, paste it
into an email, and they could replicate the behavior in squirrel (or
whatever the heck else they happen to be running).


In that case you are not really using the tool per it's primary purpose 
(ie say a webgui that provides a graphical interpretation of something) 
but you are back to merely using it as an SQL-commandline client.
I really doubt that there is any solution to the general problem as soon 
as you want filtering and related stuff - and if you only do the limited 
version people will soon come back and tell you it's not as flexible as 
was we had before (like backslash commands can do some limited 
filtering) or reimplementing SQL.



Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

On Sun, 2010-07-18 at 20:39 +0200, Dimitri Fontaine wrote:

> SHOW TABLE foo;

Yes

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

For me, realistically, No. 

Simplifying SQL should be left to the SQL standards committee. It could
certainly use a hand there, but its too big a mountain too climb, for
me.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Dimitri Fontaine]

Le 18 juil. 2010 à 21:21, Andres Freund a écrit :
> Providing an easy wrapper is something I could agree without much problems 
> (as 
> it doesnt touch me). But starting several new toplevel commands which do not 
> give everything (i.e. the ability to selectively use columns) but still want 
> to provide a more or less complete query language and should be sensibly 
> usable in subqueries et al - thats another thing. That would involve 
> significant parts of the gram.y, some parts of the parse analysis and the 
> executor for not enough benefit compared to the significant cost.

Agreed that wanting 'SHOW' commands to be full blown SQL is somewhat strange 
and "pushing it".
But people on the list wanted to gather ideas on how to do it before deciding 
its cost is higher than what it's worth, I guess.

Regards,
-- 
dim





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Dimitri Fontaine]

Le 18 juil. 2010 à 20:58, Andres Freund a écrit :
> On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote:
>> SHOW ANY TABLE
>> GROUP BY tablename 
>>  HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype];
> Why is that in *any* way better than
> 
> SELECT *
> FROM meta.tables
> ...

There are two questions here I think, really.

First is about having meta-data queries in the backend, and we want that 
because we want it to be easy for everybody to have access to those, whether 
they choose to use psql or whatever else.

Second is about why having SHOW be usable as if it where a "real" SQL query? 
That's because it's been said that people will certainly want to go further 
away using the facility. And now they want full SQL.

So it seems to me we're now trying to catch 2 birds with a single 'SHOW' stone.
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte






-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Dimitri Fontaine]

Le 18 juil. 2010 à 21:00, Kevin Grittner a écrit :
> Dimitri Fontaine  wrote:
> 
>> So what we'd need first is a series of named queries, which I
>> think psql provides for.
> 
> Any solution which only works within psql isn't a solution for a
> large part of the problem space people are trying to address.

Exactly. It's all about having it in the backend, in an easy to share format.

But what kind of facilities are we talking about? 
For me, those catalog queries psql already implements. I don't think we should 
offer \d or whatever in the backend as is, but the queries that \d uses should 
be a SHOW  away.

Now this subthread is about having a hard coded facility or the full blown SQL 
atop.
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte






-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

Stefan Kaltenbrunner  wrote:
> On 07/18/2010 08:58 PM, Andres Freund wrote:
 
>> I am quite a bit surprised about all this discussion. I have a
>> very hard time we will find anything people agree about and can
>> remember well enough to be usefull for both manual and automatic
>> processing.
>>
>> I agree that the internal pg_* tables are not exactly easy to
>> query. And that the information_schema. ones arent complete
>> enough and have enough concept mismatch to be confusing. But why
>> all this?
> 
> exactly my thoughts - but as I said earlier maybe this is actually
> an opportunity to look at newsysviews again?
 
I can't picture anything which could be done with views which would
allow me to issue one statement and see everything of interest about
a table (etc.).  You know: tablespace, owner, permissions, columns,
primary key, foreign keys, check constraints, exclusion constraints,
ancestor tables, child tables, and whatever interesting features I
missed or we later add.  Other products allow that to be generated
server-side, so that it is available to any and all clients.  I
think we should join the crowd in this respect.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Andres Freund]

Hi,

On Sunday 18 July 2010 21:02:59 Rob Wultsch wrote:
> On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund  wrote:
> > On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote:
> >> SHOW ANY TABLE
> >> GROUP BY tablename
> >>   HAVING array_agg(attributes) @> array['date'::regtype,
> >> 'time'::regtype];
> > 
> > Why is that in *any* way better than
> > 
> > SELECT *
> > FROM meta.tables
> > ...
> > 
> > Oh. The second looks like something I know. Oh. My editor maybe as well?
> > Oh. And some other tools also?
> > 
> > Your syntax also forgets that maybe I only need a subset of the
> > information.
> > 
> > I am quite a bit surprised about all this discussion. I have a very hard
> > time we will find anything people agree about and can remember well
> > enough to be usefull for both manual and automatic processing.
> > 
> > I agree that the internal pg_* tables are not exactly easy to query. And
> > that the information_schema. ones arent complete enough and have enough
> > concept mismatch to be confusing. But why all this?

> Do you have an alternative suggestion for emulating
> "SHOW SCHEMAS"
> "SHOW TABLES"
> "DESC object"?
I personally still fail to see the point of emulating it. Maybe building a 
short wrapper pointing to the docs or whatever. But thats not the point.

Providing an easy wrapper is something I could agree without much problems (as 
it doesnt touch me). But starting several new toplevel commands which do not 
give everything (i.e. the ability to selectively use columns) but still want 
to provide a more or less complete query language and should be sensibly 
usable in subqueries et al - thats another thing. That would involve 
significant parts of the gram.y, some parts of the parse analysis and the 
executor for not enough benefit compared to the significant cost.

> Make a user friendly interface is not easy, but it sure as heck is
> important.
>From my pov making it easier to query the system (either through functions or 
views) is a worthwile goal though, dont misunderstand me.

Andres

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Stefan Kaltenbrunner]

On 07/18/2010 08:58 PM, Andres Freund wrote:

On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote:

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

Why is that in *any* way better than

SELECT *
FROM meta.tables
...

Oh. The second looks like something I know. Oh. My editor maybe as well? Oh.
And some other tools also?

Your syntax also forgets that maybe I only need a subset of the information.

I am quite a bit surprised about all this discussion. I have a very hard time
we will find anything people agree about and can remember well enough to be
usefull for both manual and automatic processing.

I agree that the internal pg_* tables are not exactly easy to query. And that
the information_schema. ones arent complete enough and have enough concept
mismatch to be confusing. But why all this?


exactly my thoughts - but as I said earlier maybe this is actually an 
opportunity to look at newsysviews again?




Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Rob Wultsch]

On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund  wrote:
> On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote:
>> SHOW ANY TABLE
>> GROUP BY tablename
>>   HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype];
> Why is that in *any* way better than
>
> SELECT *
> FROM meta.tables
> ...
>
> Oh. The second looks like something I know. Oh. My editor maybe as well? Oh.
> And some other tools also?
>
> Your syntax also forgets that maybe I only need a subset of the information.
>
> I am quite a bit surprised about all this discussion. I have a very hard time
> we will find anything people agree about and can remember well enough to be
> usefull for both manual and automatic processing.
>
> I agree that the internal pg_* tables are not exactly easy to query. And that
> the information_schema. ones arent complete enough and have enough concept
> mismatch to be confusing. But why all this?
>
> Andres
>

Do you have an alternative suggestion for emulating
"SHOW SCHEMAS"
"SHOW TABLES"
"DESC object"?

Make a user friendly interface is not easy, but it sure as heck is important.


-- 
Rob Wultsch
wult...@gmail.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

Dimitri Fontaine  wrote:
 
> So what we'd need first is a series of named queries, which I
> think psql provides for.
 
Any solution which only works within psql isn't a solution for a
large part of the problem space people are trying to address.  One
important goal is that if someone spends a day to whip up a GUI
query tool (as I did when I first started working in Java), it's
easy to get displays like we get from the psql backslash commands
(as it was in Sybase, which is what we were using at the time,
through sp_help and related stored procedures).
 
While the four DBAs use psql heavily, the twenty-some programmers
and the business analysts all use various GUI tools which either tie
in to their normal environments (for example, eclipse) or are web
based hacks which probably didn't take much more effort than the
above-mentioned GUI hack which I used for about ten years. 
Backslash commands do them no good whatsoever, nor will any solution
which requires psql.
 
It would be nice if when I display information about a table or some
other database object, I could copy from my psql session, paste it
into an email, and they could replicate the behavior in squirrel (or
whatever the heck else they happen to be running).
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Andres Freund]

On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote:
> SHOW ANY TABLE
> GROUP BY tablename 
>   HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype];
Why is that in *any* way better than

SELECT *
FROM meta.tables
...

Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. 
And some other tools also?

Your syntax also forgets that maybe I only need a subset of the information.

I am quite a bit surprised about all this discussion. I have a very hard time 
we will find anything people agree about and can remember well enough to be 
usefull for both manual and automatic processing.

I agree that the internal pg_* tables are not exactly easy to query. And that 
the information_schema. ones arent complete enough and have enough concept 
mismatch to be confusing. But why all this?

Andres 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Dimitri Fontaine]

Hi,

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

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

SHOW TABLE foo;

SHOW TABLES WHERE tablename ~ 'foo';

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

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

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

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte






-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian  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 '' |  grep |
>> awk ... or something like that.  I have no idea what Windows users do.
>>  I'm sure it's possible to write a query to do it, but it's not
>> anything approaching easy.  All of this talk about backslash commands
>> being powerful rings totally hollow for me.  For ordinary, day to day
>> tasks like listing all my tables, or looking at the details of a
>> particular table, they're great.  I use them all the time and would
>> still use them even if some other syntax were available.  But there is
>> no reasonable way to pass options to them, and that to me is a pretty
>> major drawback.
>
> I am concerned that implementing a command syntax to show complex output
> like above effectively means re-implementing a subset of SQL, and that
> subset will never be as flexible.

That's a reasonable concern, but I don't have a better idea.  Do you?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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 '' |  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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Sat, Jul 17, 2010 at 5:30 PM, Stefan Kaltenbrunner
 wrote:
> On 07/17/2010 04:02 PM, Robert Haas wrote:
>> On Jul 16, 2010, at 11:02 PM, Stephen Frost  wrote:
>>> * Robert Haas (robertmh...@gmail.com) wrote:

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

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

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

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

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

I'd like to be able to list comments on objects of a particular type.
And, yeah, I'd like to be able to list all the aggregates that take a
numeric argument, or all the functions that take, say, an argument of
type internal.  Right now, this is an ENORMOUS pain in the neck.  I
usually end up running psql -c '' |  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 supp

Re: [HACKERS] SHOW TABLES

[Joshua D. Drake]

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

By whose estimation?

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

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

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

Really? Hmmm

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

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

SHOW COMMENTS ON SYSTEM AGGREGATES (or LIST)

JD

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Stefan Kaltenbrunner]

On 07/17/2010 04:02 PM, Robert Haas wrote:

On Jul 16, 2010, at 11:02 PM, Stephen Frost  wrote:

* Robert Haas (robertmh...@gmail.com) wrote:

Why must the backslash commands be more powerful than any alternative
we might come up with?


Because they encode alot of information in a character- something which
is next to impossible to do in "english".


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

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

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



uh oh - that actually sounds like a big step backwards to me - it's 
inventing extremely verbose pseudo english syntax for something that we 
currently do with a trivial and easy to remember backslash command.

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



Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Pavel Stehule]

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

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

Regards
Pavel Stehule
>
> +1
>
> JD
>
>>
>> ...Robert
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Joshua D. Drake]

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

+1

JD

> 
> ...Robert

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

Tim Landscheidt  wrote:
 
> One major flaw I see is that the fractional precision is
> fixed. Not only petrol stations split cents.
 
Well, I've never paid a petrol station a fraction of a cent; I've
only seen *rates* of money per some unit of measure with fractional
cents.  If you're being accurate about assigning types, a rate like
that is no more money than speed is a distance.  Likewise for
everywhere else I can think of such fractional cents -- for example,
hourly pay rates or tax mill rates on assessed value.  These all (in
my experience) are multiplied by a number in the unit of measure of
the divisor to get a money amount without fractional cents before
you do anything with actual *money*.
 
While others may have had some contrary experience, I've worked with
many types of businesses, non-profit organizations, and government
agencies for 38 years, and can't recall having seen anywhere that
what you describe would cause a problem, when the type is used
correctly.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Jul 16, 2010, at 11:02 PM, Stephen Frost  wrote:
> * Robert Haas (robertmh...@gmail.com) wrote:
>> Why must the backslash commands be more powerful than any alternative
>> we might come up with?
> 
> Because they encode alot of information in a character- something which
> is next to impossible to do in "english".

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

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

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

...Robert
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> Why must the backslash commands be more powerful than any alternative
> we might come up with?

Because they encode alot of information in a character- something which
is next to impossible to do in "english".

Consider 'standard' perl vs. perl w/ 'use English;'.  The former is much
more condesned and the latter is much more verbose.  Which would you
want to use on a daily basis and which would you like to have in an
application someone else may have to support some day?  Next question:
how long do you really think you're going to be around? :)

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Fri, Jul 16, 2010 at 1:52 PM, Heikki Linnakangas
 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] 
DESCRIBE 

Why must the backslash commands be more powerful than any alternative
we might come up with?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Tim Landscheidt]

"Kevin Grittner"  wrote:

>>>  postgres=# SHOW ME THE MONEY;
>>> WARNING: THE MONEY is deprecated in this version of Postgres and
>>> may be discarded in a future version
>>> HINT: Use SHOW ME THE NUMERIC with the desired precision instead.

>> Funny, but no longer true:

>> http://www.postgresql.org/docs/8.4/static/datatype-money.html

>> (although I wish we would get rid of the type)

> I hadn't been aware it was ever deprecated.  It has the advantage
> over numeric of using straight integer arithmetic for addition and
> subtraction, which are by far the most common operations on money,
> while allowing a decimal fraction without rounding problems.  I'd
> been thinking about migrating our money columns to it (subject to
> some benchmarking first, to see how much it actually helped).  It
> would seem odd for a database to tout its ability to deal with such
> data types as geometric shapes and global positioning, etc., which
> then didn't have such a common type as money.  In my experience,
> many business applications deal with money.

One major flaw I see is that the fractional precision is
fixed. Not only petrol stations split cents.

Tim


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Dimitri Fontaine]

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 , SHOW TRIGGERS ON , SHOW 
CONSTRAINTS ON , etc).

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

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

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte






-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

"Joshua D. Drake"  wrote:
> On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote:
 
>>  postgres=# SHOW ME THE MONEY;
>> WARNING: THE MONEY is deprecated in this version of Postgres and
>> may be discarded in a future version
>> HINT: Use SHOW ME THE NUMERIC with the desired precision instead.
> 
> Funny, but no longer true:
> 
> http://www.postgresql.org/docs/8.4/static/datatype-money.html
> 
> (although I wish we would get rid of the type)
 
I hadn't been aware it was ever deprecated.  It has the advantage
over numeric of using straight integer arithmetic for addition and
subtraction, which are by far the most common operations on money,
while allowing a decimal fraction without rounding problems.  I'd
been thinking about migrating our money columns to it (subject to
some benchmarking first, to see how much it actually helped).  It
would seem odd for a database to tout its ability to deal with such
data types as geometric shapes and global positioning, etc., which
then didn't have such a common type as money.  In my experience,
many business applications deal with money.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Stephen Frost]

* Heikki Linnakangas (heikki.linnakan...@enterprisedb.com) wrote:
> I'm not sure I buy that, but even if it's true, it doesn't seem fair to  
> do a favor to one group of users, leaving the rest stranded and excluded  
> forever. Even if SHOW TABLES has a bigger mind-share than the others,  
> surely the others are not negligible either.

Have to say that I don't believe we're under any obligation to be "fair"
to the users of various other RDBMS'.  I hate MySQL with a passion, and
originally came from an Oracle background, but I have to say that
'show tables;' makes a heck of alot more sense to me than 'desc'.

> I'm suggesting that we should just add the hint for all of those and be  
> done with it.

I do think it'd be useful to have a top-level set of 'show' commands.  I
agree with the others that the approach of saying "well, if you just
query pg_class joined against pg_namespace and filter out what you don't
want", etc, etc, is way more complicated than it really needs to be.  I
can think of some applications where I would have actually used it
(simple perl scripts and the like).

I'm not sure how I feel about something like "select * from (show
tables) where table_name = 'blah';"...

> :-). They're not that bad IMHO. \d is short, which is nice. \d and \df  
> are the commands I routinely use and remember, for anything more  
> advanced I have to resort to \h. The SHOW TABLES command wouldn't do  
> more than that anyway.

I don't find them all that bad either, really.  I do find myself doing
things like "psql -c '\d';" in scripts and whatnot on occation, which
isn't exactly ideal either. :)

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Joshua D. Drake]

On Sat, 2010-07-17 at 07:36 +1000, Brendan Jurd wrote:
> On 17 July 2010 07:26, Joshua D. Drake  wrote:
> > Yes. We should provide a single, well described grammar for interacting
> > with objects in the database regardless of client. I should be able to
> > open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
> > fall out.
> 
> postgres=# SHOW ME THE MONEY;
> WARNING: THE MONEY is deprecated in this version of Postgres and may
> be discarded in a future version
> HINT: Use SHOW ME THE NUMERIC with the desired precision instead.

Funny, but no longer true:

http://www.postgresql.org/docs/8.4/static/datatype-money.html

(although I wish we would get rid of the type)

JD

> 

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Brendan Jurd]

On 17 July 2010 07:26, Joshua D. Drake  wrote:
> Yes. We should provide a single, well described grammar for interacting
> with objects in the database regardless of client. I should be able to
> open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
> fall out.

postgres=# SHOW ME THE MONEY;
WARNING: THE MONEY is deprecated in this version of Postgres and may
be discarded in a future version
HINT: Use SHOW ME THE NUMERIC with the desired precision instead.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Joshua D. Drake]

On Fri, 2010-07-16 at 19:32 +0100, Simon Riggs wrote:

> That's a very sensible suggestion, we should give a hint for all common
> commands SHOW, LIST, etc., even though we pick just one to implement.
> 
> > That way we're not on the hook to maintain them forever, and we 
> > will be 
> 
> > doing people a favor by introducing them to the backslash 
> > commands
> 
> That's a sentence I never thought to see written down

No kidding. 

We are not helping users by introducing them to \d commands. 

I will repeat what I said at the beginning of this postgres vs.
postgresql thread:

Yes. We should provide a single, well described grammar for interacting
with objects in the database regardless of client. I should be able to
open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
fall out.

The discussions of \ commands and psql are irrelevant to this thread.


Joshua D. Drake


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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Tim Landscheidt]

Simon Riggs  wrote:

> [...]
> Light switches are usually at shoulder height next to a door. Our light
> switches are 2 metres up, on the far side of the room. People are sick
> of banging their knees on furniture while trying to grope for the light.
> The light switch isn't so much hard to use, its just in the wrong place.
> We must envisage what it is to be a person that doesn't know where the
> switch is, or have forgotten. We don't need a programmable light switch
> API, or a multi-function light remote control. Just a switch by all of
> the doors.

> (Oh, they're probably not called lights outside UK; room lamps maybe?)

Wow, the British must have shrunk a lot since my last vis-
it - here light switches are mounted not more than 105 cm
from the floor :-) (barrier-free not more than 85 cm).

  I guess the problem shown by others in this thread is that
there doesn't seem to be a "usually" with regard to "\d"
equivalents either.

Tim


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Heikki Linnakangas]

On 16/07/10 21:32, Simon Riggs wrote:

On Fri, 2010-07-16 at 20:52 +0300, Heikki Linnakangas wrote:

I have nothing against SHOW TABLES


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


I'm not sure I buy that, but even if it's true, it doesn't seem fair to 
do a favor to one group of users, leaving the rest stranded and excluded 
forever. Even if SHOW TABLES has a bigger mind-share than the others, 
surely the others are not negligible either.



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


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


I'm suggesting that we should just add the hint for all of those and be 
done with it.



doing people a favor by introducing them to the backslash
commands


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


:-). They're not that bad IMHO. \d is short, which is nice. \d and \df 
are the commands I routinely use and remember, for anything more 
advanced I have to resort to \h. The SHOW TABLES command wouldn't do 
more than that anyway.



--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

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

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

Agreed

> I have nothing against SHOW TABLES 

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

> (it might cause conflicts in grammar 
> though)

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

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

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

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

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

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

> or information schema, which are more powerful.

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

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Rob Wultsch]

On Fri, Jul 16, 2010 at 10:52 AM, Heikki Linnakangas
 wrote:
> On 16/07/10 20:11, Rob Wultsch wrote:
>>
>> On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas
>>  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  (or perhaps descrive ?)
describe schemas
etc


-- 
Rob Wultsch
wult...@gmail.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Heikki Linnakangas]

On 16/07/10 20:11, Rob Wultsch wrote:

On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas  wrote:

For committers.


Perhaps this discussions should be moved to the General list in order
to poll the userbase.

My .02 is that SHOW commands (even if they are not compatible) would
make it much easier for me to make an argument to my boss to at least
consider moving off another open source database. The show commands
are in *very* widespread use by the MySQL community even after ~5
years of having the i_s. The Drizzle team (a radical fork of MySQL)
very briefly considered removing the SHOW commands and the unanimous
objections that followed caused that idea to scrapped.


That's for MySQL. I come from a DB2 background, and when I started using 
psql years ago, I often typed "LIST TABLES" without thinking much about 
it. Not SHOW TABLES, but LIST TABLES.


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


I have nothing against SHOW TABLES (it might cause conflicts in grammar 
though), but if we're going to cater to people migrating from MySQL, I 
feel we should cater to people migrating from other products too. But 
surely we're not going to implement 10 different syntaxes for the same 
thing! We could, however, give a hint in the syntax error in all those 
cases. That way we're not on the hook to maintain them forever, and we 
will be doing people a favor by introducing them to the backslash 
commands or information schema, which are more powerful.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

"Marc G. Fournier"  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 
 
And you get information back which is both reasonably
human-digestable based on the formatting of result sets in whatever
client you're using, and reasonably machine-digestable based on
looking at the column headers of the result sets.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Chris Browne]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Marc G. Fournier]

On Fri, 16 Jul 2010, Simon Riggs wrote:


SQLServer and Sybase use sp_ procedures for this


Haven't experienced Sybase for 2 years in my last job, I can tell you that 
the sp_* commands are definitely non-intuitive :(



Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Rob Wultsch]

On Fri, Jul 16, 2010 at 9:56 AM, Robert Haas  wrote:
> For committers.

Perhaps this discussions should be moved to the General list in order
to poll the userbase.

My .02 is that SHOW commands (even if they are not compatible) would
make it much easier for me to make an argument to my boss to at least
consider moving off another open source database. The show commands
are in *very* widespread use by the MySQL community even after ~5
years of having the i_s. The Drizzle team (a radical fork of MySQL)
very briefly considered removing the SHOW commands and the unanimous
objections that followed caused that idea to scrapped.

-- 
Rob Wultsch
wult...@gmail.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Jul 16, 2010, at 7:43 AM, 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.

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

For committers.

...Robert
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

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 " SHOW TABLES" and you'll see that a
lot of people look for and expect this kind of command to exist.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Kevin Grittner]

Bruce Momjian  wrote:
 
> What are the other syntaxes?
 
For Sybase ASE sp_help and other stored procedures, see:
 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1550/html/sprocs/X85190.htm
 
Like \d, these server-side stored procedures can return a number of
result sets.  Like Robert, I'm skeptical of implementing a
server-side solution for PostgreSQL which doesn't do the same.  I'm
not clear on whether that's even possible without a new version of
wire protocol, though.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Aidan Van Dyk]

* Simon Riggs  [100716 12:24]:
> On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote:
> 
> > Really?  What are the other syntaxes?
> 
> SHOW TABLES

Obviously, only for some $value of $other...

The 3 database I have access to:

[DataDirect][ODBC SQL Server Driver][SQL Server]Could not find stored 
procedure 'SHOW'.
[ISQL]ERROR: Could not SQLExecute

Error: near "show": syntax error

ERROR:  unrecognized configuration parameter "tables"

So it's obviously not universal...

a.

-- 
Aidan Van Dyk Create like a god,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

On Fri, 2010-07-16 at 12:16 -0400, Bruce Momjian wrote:

> Really?  What are the other syntaxes?

SHOW TABLES

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[David E. Wheeler]

On Jul 16, 2010, at 9:09 AM, David Fetter wrote:

>> Clarification, do enough people use non-psql command line tools to
>> warrant putting this in the backend?
> 
> Yes.  Such backend stuff is in every RDBMS except ours.

I admit that I had to do a *lot* of work to write the schema-testing functions 
for pgTAP. Getting information about functions is especially hairy (I poached a 
view from newsysviews to get what I needed).

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

Best,

David
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[David Fetter]

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  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Marc G. Fournier]

On Fri, 16 Jul 2010, Bruce Momjian wrote:


There are many tools that can access Postgres. Some are libpq programs,
though there are command line versions in every environment: java,
python, etc..


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


I may have lost the gist of this question, but ... how can they use them 
if they don't exist?



Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[David Fetter]

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  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

On Thu, 2010-07-15 at 15:38 -0400, Bruce Momjian wrote:

> I assume SHOW TABLES would only be useful for interactive terminal
> sesssions, not for application code (which should use
> information_schema), so what non-psql interactive terminal programs
> are there?

My original thought was around the newbie experience: they connect to
PostgreSQL and then nothing. No sensible commands work, typing
"help" doesn't work, nor does typing "quit". Few simple commands they've
learnt elsewhere work either.

We need a way to respond sensibly to common user input.

"Terminal program" is the bit of thinking that is askew there. The
question is "what other non-psql interactive programs are there"?
Lots.

There are many tools that can access Postgres. Some are libpq programs,
though there are command line versions in every environment: java,
python, etc..

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Steve Atkins]

On Jul 16, 2010, at 8:11 AM, Simon Riggs wrote:

> On Fri, 2010-07-16 at 14:07 +0100, Thom Brown wrote:
> 
>> The problem is people are stating different requirements.
>> 
>> - to make it easy for new users of psql
>> - to simplify fetching basic database information from any client application
>> - to ease transition between MySQL and PostgreSQL
> 
> Close, but I didn't state any of those as you have them.
> 
> I want to make it easy for newbies to get access to obvious things like
> a list of tables, from *any* interactive application, wherever they
> exist. There are many and various apps and not all of them work the
> same. (The Windows installer ships two, for example). It would be nice
> to tell people "just type SHOW TABLES" and have it be true 100% of the
> time. They can remember that, or at least will try it if they can't
> remember anything at all about our RDBMS.

In pretty much any GUI application the expected way to see a list
of tables is not going to involve typing anything anywhere. Either
the list of tables is going to be shown all the time (common) or
there'll be a menu or toolbar option to show them. 

There may not be anywhere obvious to type in a command, and if there is
the output of a server-side implementation of show tables would
likely be displayed like the contents of a table, rather than as
names of tables - so all the metadata is going to be off. Things
like the context menu for each row of the result having operations
for modifying the contents of a table, rather than the operations
for modifying a table. It'll offer DML operations where you'd expect,
and want, DDL in other words.

Cheers,
  Steve


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

On Fri, 2010-07-16 at 14:07 +0100, Thom Brown wrote:

> The problem is people are stating different requirements.
> 
> - to make it easy for new users of psql
> - to simplify fetching basic database information from any client application
> - to ease transition between MySQL and PostgreSQL

Close, but I didn't state any of those as you have them.

I want to make it easy for newbies to get access to obvious things like
a list of tables, from *any* interactive application, wherever they
exist. There are many and various apps and not all of them work the
same. (The Windows installer ships two, for example). It would be nice
to tell people "just type SHOW TABLES" and have it be true 100% of the
time. They can remember that, or at least will try it if they can't
remember anything at all about our RDBMS.

Not trying to ease the transition between MySQL and PostgreSQL, it is
about making things obvious for overworked sysadmins and DBAs. Many
people are familiar with MySQL and many people use both. There are also
dozens of legacy RDBMS for DBAs to remember: Sybase, DB2, Informix,
Teradata, Ingres, MySQL and many others. Providing obvious commands that
help people who have never connected or only connect sporadically would
do much to help our cause. We are widely regarded as unhelpful,
"difficult to get started" etc.. If we had a dollar for every person
that has shouted "OMG what is the damn command on Postgres?" it would
easily fund this development.

This is not about simplifying things. It is about being obvious. 

Light switches are usually at shoulder height next to a door. Our light
switches are 2 metres up, on the far side of the room. People are sick
of banging their knees on furniture while trying to grope for the light.
The light switch isn't so much hard to use, its just in the wrong place.
We must envisage what it is to be a person that doesn't know where the
switch is, or have forgotten. We don't need a programmable light switch
API, or a multi-function light remote control. Just a switch by all of
the doors.

(Oh, they're probably not called lights outside UK; room lamps maybe?)

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Thom Brown]

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

The problem is people are stating different requirements.

- to make it easy for new users of psql
- to simplify fetching basic database information from any client application
- to ease transition between MySQL and PostgreSQL

The outcome would depend on what's needed.  Like providing a
pg_user_tables view for people to select from, using LIST TABLES as a
more meaningful alternative to SHOW TABLES, providing hints for MySQL
users using psql... etc.

Thom

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Pavel Stehule]

2010/7/16 Bruce Momjian :
> 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          http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + None of us is going to be here forever. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bruce Momjian]

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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Markus Wanner]

> I have to agree with Simon here. \d is ridiculous for the common user.

+1

Regards

Markus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Jul 15, 2010, at 11:18 PM, Josh Berkus  wrote:
> I think it's very important, as Haas says, to consider that whatever we
> do in this arena, we'll be living with it forever, so let's not make the
> \dv vs. \df mistake again, ok?

Refresh my memory?

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Hans-Jürgen Schönig]

On Jul 15, 2010, at 6:43 PM, Magnus Hagander wrote:

> On Thu, Jul 15, 2010 at 18:35, Simon Riggs  wrote:
>> On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote:
>> 
>>> Is there an actual common use-case for having these commands available
>>> for *non-psql* interfaces?
>> 
>> There are many interfaces out there and people writing new ones
>> everyday. We just wrote an interface for Android, for example.
>> 
>> It is arguably *more* important to do this from non-psql interfaces.
>> 
>> There should be one command to "display a list of tables" and it needs
>> to be easily guessable for those who have forgotten.
> 
> The downside is that you are then limited to what can be returned as a
> resultset. A "\d table" in psql returns a hell of a lot more than
> that. So do we keep two separate formats for this? Or do we remove the
> current, useful, output format in favor of a much worse formt just to
> support more clients?
> 


i am not seeing this as an "instead" solution. this is an "additional" solution.
SHOW TABLES etc could return a set of table. there is not need to change good 
of \d for that.
it just a plain add on. everything else would be simply bad.

many thanks,

hans


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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

On Thu, 2010-07-15 at 21:57 -0400, Robert Haas wrote:

> Exactly which commands are we going to support?  With exactly what
> syntax?  What information will be returned by each command?  In what
> format?  We have no agreement on any of these points.

The normal process is that we discuss the requirement for something,
then design it, discuss it some more, then build it, then discuss it
some more. For some things the process takes many years, for others it
is short, which can be because of resource stalls from people involved
or technical blockers etc.. Few things make it through.

Fairly obviously we aren't far down the process yet; each of those
stages takes time and effort. Some people skip the early stages of
effort, which is why their patches ultimately go nowhere. Regrettably, I
note that patch credit is given only for the later stages of development
which doesn't encourage general appreciation of the total process. Not
really sure why you wish to discuss general development processes, so
probably worth starting a new thread if you have more to say. I see
nothing special about this proposal with regards to dev process.


Anyway, we seem to have established almost unanimous approval for the
general requirement and so we move onto the next stage.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Josh Berkus]

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Marc G. Fournier]

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. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Thu, Jul 15, 2010 at 5:34 PM, Simon Riggs  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" 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Alvaro Herrera]

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.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[David Fetter]

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  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Dimitri Fontaine]

Le 15 juil. 2010 à 18:43, Magnus Hagander  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
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

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  

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Robert Haas]

On Jul 15, 2010, at 2:26 PM, Richard Huxton  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
> 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Simon Riggs]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Ross J. Reedstrom]

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. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
The Connexions Project  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[David Christensen]

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
da...@endpoint.com





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Richard Huxton]

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Bernd Helmle]

--On 15. Juli 2010 15:52:24 -0400 Andrew Dunstan  
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



Yeah, something like this was in my mind.

--
Thanks

Bernd

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Andrew Dunstan]

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 


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SHOW TABLES

[Heikki Linnakangas]

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers