\d type queries - why not views in system catalog?!?

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

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, Pennsylvania 19073

What about querying the information_schema?

Chris

-----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>