Thread: \d type queries - why not views in system catalog?!?
Hi! I just came across a posting in this list, and a question arose from that which I'm carrying for some time. PG has *some* views in the system catalog, which make life easier, but some essential(?) things like 'list all tables in DB' has to be done in a multi-table join with special attributes. What is the rationale of that? Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. psql) to have views for that? Do I miss a point here? I'd be even willing to do some work here, if considered worthwhile... Greetings,Joerg -- Leading SW developer - S.E.A GmbH Mail: joerg.hessdoerfer@sea-gmbh.com WWW: http://www.sea-gmbh.com
Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com> writes:
> PG has *some* views in the system catalog, which make life easier, but
> some essential(?) things like 'list all tables in DB' has to be done
> in a multi-table join with special attributes. What is the rationale
> of that? Wouldn't it be easier (and more portable, see 7.3/7.2 system
> catalogs vs. psql) to have views for that?
Only to the extent that the views match what a particular front-end
actually wants to see.
Peter Eisentraut is currently working on adding the SQL-spec-mandated
"INFORMATION_SCHEMA" views; so as long as all you want to know is what's
in the spec, those should be your answer. But I do not foresee psql or
pg_dump ever switching over to INFORMATION_SCHEMA, because they want to
know about some things that are Postgres-specific.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message > Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. > psql) to have views for that? Do I miss a point here? Putting the \d commands into views has been on the TODO list for a long time: I think it is actually the only psql-related item left, until we change the backend protocol to indicate transaction state. I don't think a view would have helped with the psql 7.2/7.3 change: a lot more changed than simply the underlying SQL. Some of the the backslash commands are not amenable to putting inside a view, as they actually compromise multiple SQL calls and some logic in the C code, but a few could probably be made into views. Could whomever added that particular TODO item expand on this? -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200301131137 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+IuuovJuQZxSWSsgRAgQXAKCdu0+CelZ1V2bwI/HoJHIz+a3DPACgix7u pOcRXwHb+4NJLMeSpNaqzRM= =0yFo -----END PGP SIGNATURE-----
On Mon, 2003-01-13 at 11:28, greg@turnstep.com wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > NotDashEscaped: You need GnuPG to verify this message > > > > Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. > > psql) to have views for that? Do I miss a point here? > > Putting the \d commands into views has been on the TODO list for a long time: > I think it is actually the only psql-related item left, until we change > the backend protocol to indicate transaction state. I don't think a view > would have helped with the psql 7.2/7.3 change: a lot more changed than > simply the underlying SQL. It would be a wise idea to use the INFORMATION_SCHEMA where possible for these, as that is pretty much guaranteed to be static in format. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
On Mon, 2003-01-13 at 11:28, greg@turnstep.com wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > NotDashEscaped: You need GnuPG to verify this message > > > > Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. > > psql) to have views for that? Do I miss a point here? > > Putting the \d commands into views has been on the TODO list for a long time: > I think it is actually the only psql-related item left, until we change > the backend protocol to indicate transaction state. I don't think a view > would have helped with the psql 7.2/7.3 change: a lot more changed than > simply the underlying SQL. > > Some of the the backslash commands are not amenable to putting inside a > view, as they actually compromise multiple SQL calls and some logic in > the C code, but a few could probably be made into views. Could whomever > added that particular TODO item expand on this? > One idea I've always thought would be nice would be to make full fledged C functions out of the \ commands and ship them with the database. This way the \ commands could just be alias to "select myfunc()". This would help out all of us who write GUI interfaces since we would have standard functions we could call upon, and would also help with backward compatibility since \dv could always call select list_views(), which would already be included with each server. One of the reasons that this was not feasible in the past was that we needed functions that could return multiple rows and columns easily. Now that we have that in 7.3, it might be worth revisiting. Robert Treat
Oh! That's an excellent idea. Seemingly addresses the issue and has value-add. I'm not aware of any gotchas here. Is there something that is being overlooked? Greg On Mon, 2003-01-13 at 14:50, Robert Treat wrote: > On Mon, 2003-01-13 at 11:28, greg@turnstep.com wrote: > > > > -----BEGIN PGP SIGNED MESSAGE----- > > Hash: SHA1 > > NotDashEscaped: You need GnuPG to verify this message > > > > > > > Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. > > > psql) to have views for that? Do I miss a point here? > > > > Putting the \d commands into views has been on the TODO list for a long time: > > I think it is actually the only psql-related item left, until we change > > the backend protocol to indicate transaction state. I don't think a view > > would have helped with the psql 7.2/7.3 change: a lot more changed than > > simply the underlying SQL. > > > > Some of the the backslash commands are not amenable to putting inside a > > view, as they actually compromise multiple SQL calls and some logic in > > the C code, but a few could probably be made into views. Could whomever > > added that particular TODO item expand on this? > > > > One idea I've always thought would be nice would be to make full fledged > C functions out of the \ commands and ship them with the database. This > way the \ commands could just be alias to "select myfunc()". This would > help out all of us who write GUI interfaces since we would have standard > functions we could call upon, and would also help with backward > compatibility since \dv could always call select list_views(), which > would already be included with each server. One of the reasons that this > was not feasible in the past was that we needed functions that could > return multiple rows and columns easily. Now that we have that in 7.3, > it might be worth revisiting. > > Robert Treat > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Greg Copeland <greg@copelandconsulting.net> Copeland Computer Consulting
> -----Original Message----- > From: Greg Copeland [mailto:greg@CopelandConsulting.Net] > Sent: 13 January 2003 20:56 > To: Robert Treat > Cc: greg@turnstep.com; PostgresSQL Hackers Mailing List > Subject: Re: [HACKERS] \d type queries - why not views in > system catalog?!? > > > Oh! > > That's an excellent idea. Seemingly addresses the issue and > has value-add. I'm not aware of any gotchas here. Is there > something that is being overlooked? Why use functions instead of views? Most UIs will want to format the output as they see fit so a recordset would be the appropriate output. Yes, a function could do this, but surely views would be simpler to implement and maintain. Regards, Dave.
Views or C-functions, I think the idea is excellent. It's the concept that I really like. Greg On Mon, 2003-01-13 at 15:00, Dave Page wrote: > > -----Original Message----- > > From: Greg Copeland [mailto:greg@CopelandConsulting.Net] > > Sent: 13 January 2003 20:56 > > To: Robert Treat > > Cc: greg@turnstep.com; PostgresSQL Hackers Mailing List > > Subject: Re: [HACKERS] \d type queries - why not views in > > system catalog?!? > > > > > > Oh! > > > > That's an excellent idea. Seemingly addresses the issue and > > has value-add. I'm not aware of any gotchas here. Is there > > something that is being overlooked? > > Why use functions instead of views? Most UIs will want to format the > output as they see fit so a recordset would be the appropriate output. > Yes, a function could do this, but surely views would be simpler to > implement and maintain. > > Regards, Dave. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Greg Copeland <greg@copelandconsulting.net> Copeland Computer Consulting
You have to do it in functions because some of the \ commands use multiple queries and logic inside the C code. Robert Treat On Mon, 2003-01-13 at 16:42, Greg Copeland wrote: > Views or C-functions, I think the idea is excellent. It's the concept > that I really like. > > Greg > > > On Mon, 2003-01-13 at 15:00, Dave Page wrote: > > > -----Original Message----- > > > From: Greg Copeland [mailto:greg@CopelandConsulting.Net] > > > Sent: 13 January 2003 20:56 > > > To: Robert Treat > > > Cc: greg@turnstep.com; PostgresSQL Hackers Mailing List > > > Subject: Re: [HACKERS] \d type queries - why not views in > > > system catalog?!? > > > > > > > > > Oh! > > > > > > That's an excellent idea. Seemingly addresses the issue and > > > has value-add. I'm not aware of any gotchas here. Is there > > > something that is being overlooked? > > > > Why use functions instead of views? Most UIs will want to format the > > output as they see fit so a recordset would be the appropriate output. > > Yes, a function could do this, but surely views would be simpler to > > implement and maintain. > > > > Regards, Dave. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > -- > Greg Copeland <greg@copelandconsulting.net> > Copeland Computer Consulting > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Robert Treat kirjutas T, 14.01.2003 kell 01:50: > One of the reasons that this > was not feasible in the past was that we needed functions that could > return multiple rows and columns easily. Now that we have that in 7.3, > it might be worth revisiting. Also, we have schemas now, so it would be easier to avoid name clashes. -- Hannu Krosing <hannu@tm.ee>
Robert Treat writes: > One idea I've always thought would be nice would be to make full fledged > C functions out of the \ commands and ship them with the database. The psql meta-commands are not a nicely designed set of queries that one would encapsulate into a public library interface. They are created for interactive use, representing precomposed views of the database that are thought to be useful. If the ideas of usefulness change, then the commands might change. If you want to create a set of views or functions for noninteractive use by client applications, then you need to step back and create maximally decomposed views of the database that can be combined in all possible ways. The SQL information schema is such a set, but the information is perhaps too limited for some applications. (But I urge you too look first.) But any other set would have to be designed on similar principles. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Robert Treat writes:
>> One idea I've always thought would be nice would be to make full fledged
>> C functions out of the \ commands and ship them with the database.
> The psql meta-commands are not a nicely designed set of queries that one
> would encapsulate into a public library interface. They are created for
> interactive use, representing precomposed views of the database that are
> thought to be useful. If the ideas of usefulness change, then the
> commands might change.
I think that the proposal is to take describe.c more or less
lock-stock-and-barrel out of psql and put it in the backend instead.
It doesn't matter whether the views are orthogonal or useful for
non-interactive purposes; they're defined to do whatever we think
psql should show.
The question is whether this gives us a useful amount of decoupling of
psql from the backend version. Certainly the describe.c code is the
stuff most subject to breakage across versions, but there are a lot of
other aspects of psql that could still break. One fairly obvious
example of backend-dependent psql code that won't be helped this way is
the tab completion code.
While I don't have any strong objection to moving the guts of these
queries to the backend, I can't get real excited about it either;
I suspect that psql will still be pretty version-dependent.
regards, tom lane
What should we do with the TODO item? Add question mark? Remove? --------------------------------------------------------------------------- Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Robert Treat writes: > >> One idea I've always thought would be nice would be to make full fledged > >> C functions out of the \ commands and ship them with the database. > > > The psql meta-commands are not a nicely designed set of queries that one > > would encapsulate into a public library interface. They are created for > > interactive use, representing precomposed views of the database that are > > thought to be useful. If the ideas of usefulness change, then the > > commands might change. > > I think that the proposal is to take describe.c more or less > lock-stock-and-barrel out of psql and put it in the backend instead. > It doesn't matter whether the views are orthogonal or useful for > non-interactive purposes; they're defined to do whatever we think > psql should show. > > The question is whether this gives us a useful amount of decoupling of > psql from the backend version. Certainly the describe.c code is the > stuff most subject to breakage across versions, but there are a lot of > other aspects of psql that could still break. One fairly obvious > example of backend-dependent psql code that won't be helped this way is > the tab completion code. > > While I don't have any strong objection to moving the guts of these > queries to the backend, I can't get real excited about it either; > I suspect that psql will still be pretty version-dependent. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
What about querying the information_schema? Chris > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Robert Treat > Sent: Tuesday, 14 January 2003 6:01 AM > To: Greg Copeland > Cc: Dave Page; greg@turnstep.com; PostgresSQL Hackers Mailing List > Subject: Re: [HACKERS] \d type queries - why not views in system > catalog?!? > > > You have to do it in functions because some of the \ commands use > multiple queries and logic inside the C code. > > Robert Treat > > On Mon, 2003-01-13 at 16:42, Greg Copeland wrote: > > Views or C-functions, I think the idea is excellent. It's the concept > > that I really like. > > > > Greg > > > > > > On Mon, 2003-01-13 at 15:00, Dave Page wrote: > > > > -----Original Message----- > > > > From: Greg Copeland [mailto:greg@CopelandConsulting.Net] > > > > Sent: 13 January 2003 20:56 > > > > To: Robert Treat > > > > Cc: greg@turnstep.com; PostgresSQL Hackers Mailing List > > > > Subject: Re: [HACKERS] \d type queries - why not views in > > > > system catalog?!? > > > > > > > > > > > > Oh! > > > > > > > > That's an excellent idea. Seemingly addresses the issue and > > > > has value-add. I'm not aware of any gotchas here. Is there > > > > something that is being overlooked? > > > > > > Why use functions instead of views? Most UIs will want to format the > > > output as they see fit so a recordset would be the appropriate output. > > > Yes, a function could do this, but surely views would be simpler to > > > implement and maintain. > > > > > > Regards, Dave. > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > -- > > Greg Copeland <greg@copelandconsulting.net> > > Copeland Computer Consulting > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
> -----Original Message----- > From: Robert Treat [mailto:xzilla@users.sourceforge.net] > Sent: 13 January 2003 22:01 > To: Greg Copeland > Cc: Dave Page; greg@turnstep.com; PostgresSQLHackers Mailing List > Subject: Re: [HACKERS] \d type queries - why not views in > system catalog?!? > > > You have to do it in functions because some of the \ commands > use multiple queries and logic inside the C code. pgAdmin lists far more comprehensive data the psql and does it from single queries 95% of the time. I'm sure someone more expert in SQL than me could get pretty much all the other 5% sorted... Regards, Dave.
On Tue, 2003-01-14 at 01:39, Christopher Kings-Lynne wrote: > What about querying the information_schema? Will information_schema be strictly SQL99 or will it also have PostgreSQL specific views/fields ? -- Hannu Krosing <hannu@tm.ee>
Hannu Krosing <hannu@tm.ee> writes:
> Will information_schema be strictly SQL99 or will it also have
> PostgreSQL specific views/fields ?
If it's not strictly conformant to the spec, I see no value in it at
all. We already have plenty of ability to query the catalogs via
non-standard queries.
regards, tom lane
On Tue, 2003-01-14 at 15:47, Tom Lane wrote: > Hannu Krosing <hannu@tm.ee> writes: > > Will information_schema be strictly SQL99 or will it also have > > PostgreSQL specific views/fields ? > > If it's not strictly conformant to the spec, I see no value in it at > all. I mean that it could have at least extra *views* for postgresql specific things. It could also have extra *fields* on standard views, but that might break some apps. I see no way how having extra views can break apps. > > We already have plenty of ability to query the catalogs via > non-standard queries. But would it not be nice to have some standard ones, so that each and every DB management app does not need to invent its own ? I agree that this could be done as a project at gborg rather than in information_schema, but it would still be good to have one standard place for examples at least. And the only way to keep the examples up-to-date is by using them in live projects. -- Hannu Krosing <hannu@tm.ee>