Re: [HACKERS] Views, views, views! (long)
On Wed, 4 May 2005 21:37:40 -0700, Josh Berkus josh@agliodbs.com wrote: As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. I don't think that this is doable. :-( You might want to put the system views into a version specific schema, say pg_views81. The next PG version will contain a new schema pg_views82 plus a version of 8.1 views that have been adapted to new features and catalog structures as far as possible without breaking compatibility. Ideally the views in pg_views81 and pg_views82 will look the same, but most likely there will be some differences. In PG 8.3 we will have schemas pg_views81, pg_views82, and pg_views83 ... Obviously it will get harder and harder to maintain older system view schemas with each new Postgres version. If in PG 8.7 it becomes clear that carrying on pg_views81 doesn't make sense any more, you simply drop it. By that time tool vendors should have had enough time to make their tools compatible with pg_views8x, for some x = 2. Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
Am Montag, 9. Mai 2005 00:41 schrieb Andrew - Supernews: c) In most places, system objects are segregated from user objects, e.g. pg_user_indexes I think that is a bad idea as it goes against the fundamental design of PostgreSQL. In what way? Please elaborate. PostgreSQL does not really distinguish between system and user things. How will you do that? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
Am Freitag, 6. Mai 2005 12:20 schrieb Andreas Pflug: and the information_schema is next to useless for these things since it doesn't have PostgreSQL specific things in it. And the restriction to current user owned objects reduces usability to zero. The information schema restricts the views to the objects to which you have some access right, which doesn't seem all that useless. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
Am Freitag, 6. Mai 2005 12:20 schrieb Andreas Pflug: and the information_schema is next to useless for these things since it doesn't have PostgreSQL specific things in it. And the restriction to current user owned objects reduces usability to zero. The information schema restricts the views to the objects to which you have some access right, which doesn't seem all that useless. Not useless at all, I couldn't' agree with you more. In fact, there is a good security argument to be made here as well. In the current state of things, a user with no rights at all can see the entire database schema including all the source code to the stored procedures. There are means to circumvent this but they are very coarse and can cause unexpected problems. I think the information_schema approach to system metadata is ultimately the correct one...but I also agree with Josh in that the various client tools such as pgadmin and perhaps even pg_dump should be transitioned to using views. After that the true system columns will no longer need public access and everything becomes very elegant. To me, this is a much finer grained security model and nobody complains about extra security features. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
Peter, Merlin, Andrew, And the restriction to current user owned objects reduces usability to zero. The information schema restricts the views to the objects to which you have some access right, which doesn't seem all that useless. There's a difference between restricting it to objects on which you have rights (which our views do as well) vs. restricting it to objects you *own*, which is what the information_schema does, according to Andrew. Yes? More in next e-mail. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
On Tue, May 10, 2005 at 04:55:40PM +0200, Peter Eisentraut wrote: Am Montag, 9. Mai 2005 00:41 schrieb Andrew - Supernews: c) In most places, system objects are segregated from user objects, e.g. pg_user_indexes I think that is a bad idea as it goes against the fundamental design of PostgreSQL. In what way? Please elaborate. PostgreSQL does not really distinguish between system and user things. How will you do that? It's currently done using this function: create or replace function _pg_sv_system_schema(name) returns boolean as 'select $1 in (name ''pg_catalog'', name ''pg_toast'', name ''pg_sysviews'', name ''information_schema'')' language sql immutable strict; Objects that are in one of those schemas are considered system objects. This is how pg_dump does it (except for casts, which are considered system objects if the source type, destination type, and conversion function are ALL in system schemas). psql also distinguishes between system and user tables, although it restricts this to pg_catalog. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
On 2005-05-10, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Freitag, 6. Mai 2005 12:20 schrieb Andreas Pflug: and the information_schema is next to useless for these things since it doesn't have PostgreSQL specific things in it. And the restriction to current user owned objects reduces usability to zero. The information schema restricts the views to the objects to which you have some access right, which doesn't seem all that useless. Actually that varies according to the individual view. Some restrict to objects to which you have access, others restrict to objects that you own. Furthermore, in the latter case there is no exception for superusers. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
FW: [HACKERS] Views, views, views! (long)
my bad [thanks Greg] Peter, Merlin, Andrew, And the restriction to current user owned objects reduces usability to zero. The information schema restricts the views to the objects to which you have some access right, which doesn't seem all that useless. There's a difference between restricting it to objects on which you have rights (which our views do as well) vs. restricting it to objects you *own*, which is what the information_schema does, according to Andrew. Yes? Good point, although I'll take either over the current behavior. Basically, I feel very strongly that *all* interface to database metadata should be through views unless you happen to database root. I have personal reasons for this but I also think it's the correct philosophy in general, even if the views are the incredibly simple: create view pg_class_view as select * from pg_class; As a side note I also like the idea of set returning functions because of the extra flexibility of security invoker/definer...but I'll be a huge cheerleader for any effort that gets user applications off of dependency on internal system tables. In a previous similar discussion Simon noted that the Terradata database had different view configurations for different security requirements. my 0.02$ (and that's about all it's worth :-) ) Merlin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, May 10, 2005 at 04:55:40PM +0200, Peter Eisentraut wrote: PostgreSQL does not really distinguish between system and user things. How will you do that? It's currently done using this function: create or replace function _pg_sv_system_schema(name) returns boolean as 'select $1 in (name ''pg_catalog'', name ''pg_toast'', name ''pg_sysviews'', name ''information_schema'')' language sql immutable strict; Objects that are in one of those schemas are considered system objects. This is how pg_dump does it Peter's point still stands though: the *system* isn't making that distinction. pg_dump needs to make a distinction so that it doesn't dump built-in objects; which is not necessarily the same distinction that a user might want to make. Thus, the fact that psql does it a bit differently isn't necessarily a bug. I think the real problem here is that it's hard to be all things to all people. If you suppress display of certain objects, that may be nice suppression of clutter for one user, yet render the view useless from the perspective of another user --- or even the same user on a different day, when he is looking for a particular built-in function for instance. (I know it's always bugged the heck out of me that \df editorializes on which functions it thinks I want to see.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
Tom, I think the real problem here is that it's hard to be all things to all people. If you suppress display of certain objects, that may be nice suppression of clutter for one user, yet render the view useless from the perspective of another user --- or even the same user on a different day, when he is looking for a particular built-in function for instance. (I know it's always bugged the heck out of me that \df editorializes on which functions it thinks I want to see.) But all of the views we've composed as pg_user_* also have a pg_all_*. So users can do what they want. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Views, views, views! (long)
On Friday the 6th of May 2005, Mr. Treat opined: I also don't think it is any harder to learn to query the system tables than it would be to learn to query these new views (with a few caevets that I will come back to) and it might actually be better. Admin tools are in a sense already a gui view into the database, so whether they elect to use these new views seems rather immaterial to this discussion, to me at least. The more important aspect to me is as a user. As someone last week pointed out (but it seems to have been lost in the tool discussion) is that these views give the user easy access to system information from the command line. This is where typing a multi-join querey against the system catalog is error-prone and a PITA. An even bigger point is that these views can be used in scripts or procedures where the results can be used in another query or put into a table. The '\d' command might show me a list of tables, but I for one find the Oracle-style views such as ALL_TABLES, etc. more useful. I've always found the slash-style commands to be basically useful in showing what's there but using them to build reports or extract data for use in other queries is painful compared to having queriable views of the same data. Darren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
Robert, As Jim points out, their current long term goal is to be a replacement for the current system views (hence *new* system views), and the current project was created to facilitate development. What I am thinking is that the project take on a different goal, mainly that it be an add on that intends to work along side the current system views and be both backward and forward compatible (hence *enhanced* system views). It's a subtle difference. *what* current system views? We appear to have 8 of them. 2 we're not touching at this time. The other 6 are all deficient in various ways, and those ways are not fixable in a backwards-compatible fashion. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
On Fri, May 06, 2005 at 05:44:43PM -0400, Robert Treat wrote: Sorry, but I'm still in the admin tools wont use these camp since I don't believe these views can solve an admin tools need to support multiple versioning within its code. I also don't think it is any harder to learn to query the system tables than it would be to learn to query these new views (with a few caevets that I will come back to) and it might actually be better. If I'm building an admin tool, I have to know that tablespaces I find it hard to believe that it's easier to write a 30 line query instead of just selecting out of a single view. But, even if an admin tool does want to 'go direct to the source' and query the system tables, ISTM that having a reference implementation (the system views) would be very valuable. aren't supported on some older versions, and I think it is easier to figure this out if my query breaks on tablespace information rather than if my query just silently sends me some special data (NULL?) that I have to interpret to mean not supported. Well, these views don't prevent you from using version() to know what is and isn't supported, but if you can think of other means to indicate what features are and aren't available I'm all ears. That said, some admin tools already have a requirment that you install some little piece of schema into your database to support them, they could include this package along with thier software if they felt strongly about it. The cavet I am thinking about from above is things like the relacl bits of pg_class, which are a total poop to work with. Adding a couple of new system views to help make that information more transparent would be a good thing. Actually I am thinkinga couple of parts of this stuff could be used as an enhancement to the current system views if people weren't interested in a wholesale replacement. It's certainly not decided that these views would replace anything. I'm in favor of always keeping these views in their own schema so that it's up to the user to decide what exactly they want to query. If they want stuff out of the current catalog, then use pg_catalog. If they want these new views, then use pg_sysviews. There is the possibility of eventually replacing some of the old system views, but that would be several versions away, if it were to ever happen. And of course these would not replace the system tables. But yes, the intention is to continue to support backwards compatability as much as possible. Currently I believe that compatability stops at versions that don't support schemas, though that could change. I'm curious, are the queries between various versions actually all that different? I can't imagine that you can present a stable interface going back 3 versions that is relevant to all three versions that also requires serious query changes between each version. I suggest taking a gander at the '_compat' files at http://lnk.nu/cvs.pgfoundry.org/251/. Basically, features that are new in 8.0 (ie: tablespaces) have an abstraction layer. The code under that layer is version specific, but the code above it is generic. So _compat74.sql creates a bogus pg_tablespace (though now that I'm thinking about it, we should probably use a different name for that, such as _pg_tablespace). Of course, we could certainly go the route of having completely different view definitions for different versions, but I'm not sure that's an improvement. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
On 2005-05-08, Jim C. Nasby [EMAIL PROTECTED] wrote: I suggest taking a gander at the '_compat' files at http://lnk.nu/cvs.pgfoundry.org/251/. Basically, features that are new in 8.0 (ie: tablespaces) have an abstraction layer. The code under that layer is version specific, but the code above it is generic. So _compat74.sql creates a bogus pg_tablespace (though now that I'm thinking about it, we should probably use a different name for that, such as _pg_tablespace). Remember that this is still an alpha version. In the longer term I think we should look at splitting it into two schemas, one with the views themselves and another with the support functions and other implementation details. (There are other ways to handle pg_tablespace too, that just happened to be the convenient one for proof-of-concept testing.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
On 2005-05-05, Peter Eisentraut [EMAIL PROTECTED] wrote: I would suggest that you align your terminology with the information schema as much as possible, so it would be type_schema and not type_schema_name, and ordinal_position instead of column_position. Otherwise we'll have a lot of confusion ahead if we instroduced a third parallel set of terminology. Personally I'm open to suggestions on this; we didn't entirely agree on the naming conventions when writing the stuff so far. c) In most places, system objects are segregated from user objects, e.g. pg_user_indexes I think that is a bad idea as it goes against the fundamental design of PostgreSQL. In what way? Please elaborate. g) All views are as normalized as possible, using child views rather than arrays, and providing keys and consistent join columns. You still seem to have a bunch of arrays in there. Anything with an array is never normalized. There are 6 array columns in there at the moment. One looks pointless and might get removed (database_config). The others are all intentional and cover cases where the denormalized view is (a) already easily available within the query and (b) substantially useful. The normalized versions are available too in all cases. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
On Fri, May 06, 2005 at 02:43:19AM -, Andrew - Supernews wrote: On 2005-05-06, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs are not consistent after a database reload and names are. I can see your point though; what do other people think? Well phpPgAdmin is unable to use the pg_tables view, for instance, because we have no way of getting the table comment using the information in that view... If you look at the columns lists, you'll find that oids are exposed in a number of places. In general, I didn't make a point of exposing them everywhere, but I _did_ expose them in cases where I thought it likely that querying by or for the oid in particular might be needed. (OIDs are, after all, exposed quite a bit by the wire protocol and by libpq.) Whether the balance is correct here is something I'm open to suggestions about. Perhaps it makes sense to expose the OIDs of each object in it's view. IE: pg_tables would have table_oid, pg_types would have type_oid, etc. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
On Thu, May 05, 2005 at 06:55:38PM +0200, Andreas Pflug wrote: Josh Berkus wrote: Frankly, this is sounding a lot like Who needs OpenOffice.org? Use vi! Frankly, this is sounding a lot like Who needs pgadmin/phppgadmin/pgaccess/younameit? use SELECT * FROM pg_somewot in psql instead. And what about users who's interface to PostgreSQL is psql? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views! (long)
On Thu, May 05, 2005 at 11:29:34PM -0400, Robert Treat wrote: I was starting to think this... like this should be a project on foundry called enhanced system views that would be fairly database version independant and people could install into any databases they needed them in. The pgFoundry project started as a means for those of us working on this to coordinate our efforts and has grown somewhat from that. The original intention was to make this part of initdb, and support older versions through the pgFoundry project. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
On Thu, May 05, 2005 at 05:38:16PM -0700, Dann Corbit wrote: See Andrew's post. There is a whole lot of stuff not covered by I_S in a way that is useful to PGSQL users. Also this would require making information_schema part of the default user path. It is supposed to be part of the default user path for all users of the database. Huh? That seems like a really bad idea. Anything with names not starting with pg_ really needs to not be in the search_path by default. Speaking of which, any suggestions for names of internal-use objects? Right now we've got some things named _pg_* to signify that they're subject to change, but afaik the only naming convention PostgreSQL has laid claim to is pg_*. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
On 5/6/05, Greg Sabino Mullane [EMAIL PROTECTED] wrote: As long as they are in a separate schema (like information_schema, but hopefully not as long). pg_views? pg_info? information_skema? :) But if you think that nobody needs these views, it's because you haven't had much contact with end users lately. Well, who really *does* need these? After all, end users should be using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's the job of the people writing those interfaces to know the system catalogs well and present them to the users in a pretty fashion. If people want an easy way to look up the information, they use an interface. If not, they should learn the system catalogs. /devilsadvocate We... Lets assume that young DBA needs to get a list of primary keys for each table. If she's smart she'll probably run psql -E and get queries like: SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '6338115' ORDER BY inhseqno ASC SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE a.attrelid = '6338117' AND a.attnum 0 AND NOT a.attisdropped AND a.attrelid = i.indexrelid ORDER BY a.attnum SELECT i.indisunique, i.indisprimary, i.indisclustered, a.amname, c2.relname, pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a WHERE i.indexrelid = c.oid AND c.oid = '6261315' AND c.relam = a.oid AND i.indrelid = c2.oid ...and so on. Then refashion them to do the needed query. Then again she may look inside information_schema.* (columns?), but it is not as natural as one would like. And then again, as most people are lazy, she would probably use: select schemaname,tablename,attname from pg_stats where n_distinct = -1 and schemaname='public'; Which is simply the stupidest way, and of course the wrong one. Yet it gives an illusion of returning quite right data the easy way... Sometimes it may be terribly tempting... I would certainly like to see these views in PostgreSQL. Maybe as a contrib package (just as there are tsearch2 or intarray). I think such views would not be of much use for, say pgAdmin. Yet again for querying from perl/php or over human carrier it would be benefitial, I guess. My 0.03 PLN. ;) Regards, Dawid ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
Christopher Kings-Lynne wrote: The unimaginable craziness of currently trying to support multiple versions of postgresql is pretty bad, Hu? So you suggest version specific admin tools? *scratch head* Just for curiosity: pgAdmin CVS currently has 80 version checked pieces of code to support 7.3/7.4/8.0, and the vast majority is about version specific CREATE options, i.e. DDL commands, not for querying system catalogs. I remember only a single occurrence when a system catalog change had an impact on pgAdmin's usability (dropped datpath). and the information_schema is next to useless for these things since it doesn't have PostgreSQL specific things in it. And the restriction to current user owned objects reduces usability to zero. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 And what about users who's interface to PostgreSQL is psql? Backslash commands. -BEGIN PGP SIGNATURE- iD8DBQFCeqzEvJuQZxSWSsgRAgkPAKC1V0Sm3Umi1eGFnoj1P5Qt26V32wCeMjLh +3LX4eUjgKdy+SOKHSRzRxQ= =mSNP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
On Thursday 05 May 2005 23:45, Joshua D. Drake wrote:
I was starting to think this... like this should be a project on foundry
called enhanced system views that would be fairly database version
independant and people could install into any databases they needed them
in.
You mean like:
http://pgfoundry.org/projects/newsysviews/
As Jim points out, their current long term goal is to be a replacement for the
current system views (hence *new* system views), and the current project was
created to facilitate development. What I am thinking is that the project
take on a different goal, mainly that it be an add on that intends to work
along side the current system views and be both backward and forward
compatible (hence *enhanced* system views). It's a subtle difference.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
On Fri, May 06, 2005 at 11:34:49AM -, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 And what about users who's interface to PostgreSQL is psql? Backslash commands. There's a ton of cases the backslash commands don't cover, which others have given examples of. Even if we went to town and added a whole bunch of extra backslash commands, there's still going to be things that just aren't covered. Aside from that, it's currently rather silly that every admin tool has to code up a very complex set of queries to get info from the system catalog. It makes much more sense to put that complexity into a set of system views that are maintained as part of the backend, instead of pushing that effort out to everyone who writes tools. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
Jim C. Nasby [EMAIL PROTECTED] writes: Aside from that, it's currently rather silly that every admin tool has to code up a very complex set of queries to get info from the system catalog. It makes much more sense to put that complexity into a set of system views that are maintained as part of the backend, instead of pushing that effort out to everyone who writes tools. So instead, they should code up complex queries to get info from the system views? Your claim only makes sense if you know exactly what every admin tool is going to need, what format they are going to want it in, and other things that I doubt you are really prescient enough to get 100% right. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Views, views, views! (long)
Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Aside from that, it's currently rather silly that every admin tool has to code up a very complex set of queries to get info from the system catalog. It makes much more sense to put that complexity into a set of system views that are maintained as part of the backend, instead of pushing that effort out to everyone who writes tools. So instead, they should code up complex queries to get info from the system views? Your claim only makes sense if you know exactly what every admin tool is going to need, what format they are going to want it in, and other things that I doubt you are really prescient enough to get 100% right. Well I think you're wrong. We really should have a view like this, I'll provide more to include them in pgsql8.1: CREATE VIEW pg_dependent_objects_for_pga3 AS SELECT DISTINCT deptype, classid, cl.relkind, CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind WHEN tg.oid IS NOT NULL THEN 'T'::text WHEN ty.oid IS NOT NULL THEN 'y'::text WHEN ns.oid IS NOT NULL THEN 'n'::text WHEN pr.oid IS NOT NULL THEN 'p'::text WHEN la.oid IS NOT NULL THEN 'l'::text WHEN rw.oid IS NOT NULL THEN 'R'::text WHEN co.oid IS NOT NULL THEN 'C'::text || contype ELSE '' END AS type, COALESCE(coc.relname, clrw.relname) AS ownertable, COALESCE(cl.relname, conname, proname, tgname, typname, lanname, rulename, ns.nspname) AS refname, COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname FROM pg_depend dep LEFT JOIN pg_class cl ON dep.objid=cl.oid LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid LEFT JOIN pg_proc pr on dep.objid=pr.oid LEFT JOIN pg_namespace nsp ON pronamespace=nsp.oid LEFT JOIN pg_trigger tg ON dep.objid=tg.oid LEFT JOIN pg_type ty on dep.objid=ty.oid LEFT JOIN pg_namespace nst ON typnamespace=nst.oid LEFT JOIN pg_constraint co on dep.objid=co.oid LEFT JOIN pg_class coc ON conrelid=coc.oid LEFT JOIN pg_namespace nso ON connamespace=nso.oid LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class LEFT JOIN pg_namespace nsrw ON cl.relnamespace=nsrw.oid LEFT JOIN pg_language la ON dep.refobjid=la.oid LEFT JOIN pg_namespace ns ON dep.objid=ns.oid Isn't it a shame that this widely usable query isn't included in pgsql since 7.0? ;-) Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
On Fri, May 06, 2005 at 12:21:55PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Aside from that, it's currently rather silly that every admin tool has to code up a very complex set of queries to get info from the system catalog. It makes much more sense to put that complexity into a set of system views that are maintained as part of the backend, instead of pushing that effort out to everyone who writes tools. So instead, they should code up complex queries to get info from the system views? Your claim only makes sense if you know exactly what every admin tool is going to need, what format they are going to want it in, and other things that I doubt you are really prescient enough to get 100% right. Actually, given the amount of info provided by the views, I'd be surprised if there's anything that is either missing (except for OIDs in some places), or in the 'wrong format' for admin tools. If there is then I'd like to hear about it so we can consider changes. And btw, I'm not suggesting that these views will mean that admin tools will never have to do any joining of tables, but they shouldn't require anything nearly as complex as what's currently required. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views! (long)
On Fri, May 06, 2005 at 09:08:10AM -0400, Robert Treat wrote: On Thursday 05 May 2005 23:45, Joshua D. Drake wrote: I was starting to think this... like this should be a project on foundry called enhanced system views that would be fairly database version independant and people could install into any databases they needed them in. You mean like: http://pgfoundry.org/projects/newsysviews/ As Jim points out, their current long term goal is to be a replacement for the current system views (hence *new* system views), and the current project was created to facilitate development. What I am thinking is that the project take on a different goal, mainly that it be an add on that intends to work along side the current system views and be both backward and forward compatible (hence *enhanced* system views). It's a subtle difference. What I don't like about that idea (assuming you're intending that these views are never brought into initdb) is it means that admin tools (like psql) then either require the user to install the views by hand, or they don't use them and keep doing things the hard (and error-prone) way. But yes, the intention is to continue to support backwards compatability as much as possible. Currently I believe that compatability stops at versions that don't support schemas, though that could change. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
On Fri, May 06, 2005 at 01:20:09AM -0500, Jim C. Nasby wrote: On Fri, May 06, 2005 at 02:43:19AM -, Andrew - Supernews wrote: On 2005-05-06, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs are not consistent after a database reload and names are. I can see your point though; what do other people think? Well phpPgAdmin is unable to use the pg_tables view, for instance, because we have no way of getting the table comment using the information in that view... If you look at the columns lists, you'll find that oids are exposed in a number of places. In general, I didn't make a point of exposing them everywhere, but I _did_ expose them in cases where I thought it likely that querying by or for the oid in particular might be needed. (OIDs are, after all, exposed quite a bit by the wire protocol and by libpq.) Whether the balance is correct here is something I'm open to suggestions about. Perhaps it makes sense to expose the OIDs of each object in it's view. IE: pg_tables would have table_oid, pg_types would have type_oid, etc. And this is exactly what we are doing. The table view has a tableoid. The type view has the type oid, etc. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
Actually bad and good are appropriate. The structure of the system catalogs dates back to the grad student's theses and is not really good. But it is stable and does the job. It really is not user friendly, however. I reassert that I have seen only one decent schema drawing of the system catalogs and it is obtuse at best. The short comings wrt user friendliness in the system catalogs cannot be adequately handled by better documentation. The complex queries people will have to write to extract the information means that each person will have to write their own set of system views, correctly or not. This is the current state of affairs. With regards to the backslash commands, yes, let us improve them! And by the way, the same views and queries will have to be written for improved backslash commands are the same as are being written for the newsysviews. The additional interface of SQL is also imperative for interfaces that want to create scripts and/or do operations on the data found in the system catalog. e.g. drop all foreign keys linked to table foo. Elein [EMAIL PROTECTED] On Thu, May 05, 2005 at 09:22:40PM -0700, Josh Berkus wrote: Tom, Andrew, Robert, More to the point: how can you build a good interface on top of a bad one? Whatever fundamental shortcomings exist in the latter cannot be hidden by the former. I think bad and good are pretty irrelevant myself. The system tables are very good at what they do: support the postgresql code base. They are not *meant* to be user-friendly. That's why we need a different interface to be good for a different purpose. Which is what we're trying to do. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
On 2005-05-06, Jim C. Nasby [EMAIL PROTECTED] wrote: But yes, the intention is to continue to support backwards compatability as much as possible. Currently I believe that compatability stops at versions that don't support schemas, though that could change. I have made no attempt to support pre-7.4. It _might_ be possible to do 7.3, but I haven't checked what functionality we currently rely on is missing there. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
On Friday 06 May 2005 13:43, Jim C. Nasby wrote:
On Fri, May 06, 2005 at 09:08:10AM -0400, Robert Treat wrote:
On Thursday 05 May 2005 23:45, Joshua D. Drake wrote:
I was starting to think this... like this should be a project on
foundry called enhanced system views that would be fairly database
version independant and people could install into any databases they
needed them in.
You mean like:
http://pgfoundry.org/projects/newsysviews/
As Jim points out, their current long term goal is to be a replacement
for the current system views (hence *new* system views), and the current
project was created to facilitate development. What I am thinking is
that the project take on a different goal, mainly that it be an add on
that intends to work along side the current system views and be both
backward and forward compatible (hence *enhanced* system views). It's a
subtle difference.
What I don't like about that idea (assuming you're intending that these
views are never brought into initdb) is it means that admin tools (like
psql) then either require the user to install the views by hand, or they
don't use them and keep doing things the hard (and error-prone) way.
Sorry, but I'm still in the admin tools wont use these camp since I don't
believe these views can solve an admin tools need to support multiple
versioning within its code. I also don't think it is any harder to learn to
query the system tables than it would be to learn to query these new views
(with a few caevets that I will come back to) and it might actually be
better. If I'm building an admin tool, I have to know that tablespaces
aren't supported on some older versions, and I think it is easier to figure
this out if my query breaks on tablespace information rather than if my query
just silently sends me some special data (NULL?) that I have to interpret to
mean not supported.
That said, some admin tools already have a requirment that you install some
little piece of schema into your database to support them, they could
include this package along with thier software if they felt strongly about
it.
The cavet I am thinking about from above is things like the relacl bits of
pg_class, which are a total poop to work with. Adding a couple of new system
views to help make that information more transparent would be a good thing.
Actually I am thinkinga couple of parts of this stuff could be used as an
enhancement to the current system views if people weren't interested in a
wholesale replacement.
But yes, the intention is to continue to support backwards compatability
as much as possible. Currently I believe that compatability stops at
versions that don't support schemas, though that could change.
I'm curious, are the queries between various versions actually all that
different? I can't imagine that you can present a stable interface going back
3 versions that is relevant to all three versions that also requires serious
query changes between each version.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
Hi Josh, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: 05 May 2005 05:38 To: PostgreSQL-development Subject: [HACKERS] Views, views, views! (long) This has kept the pgAdmin and phpPgAdmin teams busy since 7.2, and means that GUI tools which fall out of maintenance (like Xpg) soon stop working. This is easily remedied through a set of system views which will remain consistent regardless of changes in the underlying system tables. 6.3/6.4 for pgAdmin. 7.2 is positively recent :-) However, I cannot see us using these views for a couple of reasons: 1) Users may drop or change them, something they cannot do easily with system catalogs. Yes, I know this is then their fault, but it will stop admin tools that use the views from working even though the database itself is actually OK. If a user does manage to alter a system catalog, they are far more likely to see breakage in other places as well. 2) Catalog changes are infrequent but significant in other areas when they do occur. Consider the 7.2 - 7.3 namespace changes. Regardless of using views or the catalogs we still have significant work to do to support namespaces. Other smaller changes will likely require GUI updates or internal code changes that will also be necessary whether using views or the catalogs. 3) One example of a catalog change that has caused a number of bug reports for us is the removal of pg_database.datpath. Whilst your views could have prevented the error itself, we would still have had to modify pgAdmin to prevent it displaying the path on newer servers as it is completely meaningless - however, do you proprose that your views would have retained this column forever? If so, it seems they could get very messy, and cluttered with notes in the docs telling users that a given column was only relevant up till version X. After 8 or so years of dealing with problem, I'm really don't think we would gain anything worthwhile from the views you propose. However, I'm sure some end users may well find them useful, so I do not believe your work is in vain. Regards, Dave. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
Josh, it's very difficult to read your messages (I'm using Pine), because of some symbols (~Z on my xterm) which broke formatting. Is't known problem of pine (4.62) or your mailer ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
Josh Berkus wrote: a) all view and column names are as explicit and as readable as possible (e.g. type_schema_name, not typnsname) I would suggest that you align your terminology with the information schema as much as possible, so it would be type_schema and not type_schema_name, and ordinal_position instead of column_position. Otherwise we'll have a lot of confusion ahead if we instroduced a third parallel set of terminology. c) In most places, system objects are segregated from user objects, e.g. pg_user_indexes I think that is a bad idea as it goes against the fundamental design of PostgreSQL. d) Columns may be added to the system views, but never dropped or changed in incompatible ways. Likewise, views will be added but not dropped or renamed. Dave Page already pointed out an example where this is a bad idea. When a feature is removed, we can't keep claiming it exists. g) All views are as normalized as possible, using child views rather than arrays, and providing keys and consistent join columns. You still seem to have a bunch of arrays in there. Anything with an array is never normalized. That said, I don't particularly care for this proposal. If you want a human-readable version of the system catalogs, I suggest you work on extensions of the information schema, not a completely new interface. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
Josh Berkus josh@agliodbs.com writes: Tom, To put it more bluntly: exactly what are you accomplishing here that isn't already accomplished, in a *truly* standard fashion, by the INFORMATION_SCHEMA? Why do we need yet another nonstandard view on the underlying reality? To quote myself: Q: Why not just use information_schema? A: Because the columns and layout of information_schema is strictly defined by the SQL standard. This prevents it from covering all PostgreSQL objects, or from covering the existing objects adequately to replicate a CREATE statement. As examples, there is no types table in information_schema, and the constraints table assumes that constraint names are universally unique instead of table-unique as they are in PG. So? If you want reality, look at the catalogs. I think that in a release or three, these views will be just as distorted a representation of the underlying reality as the information_schema is now. Either that or you'll be changing them incompatibly. You can't have both truth and a greater degree of stability than the underlying catalogs. So my opinion remains what's the point?. All you have really accomplished is some editorialization on table/column names. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
Dave Page dpage@vale-housing.co.uk writes: 3) One example of a catalog change that has caused a number of bug reports for us is the removal of pg_database.datpath. Whilst your views could have prevented the error itself, we would still have had to modify pgAdmin to prevent it displaying the path on newer servers as it is completely meaningless - however, do you proprose that your views would have retained this column forever? It's worth noting that we could have left datpath in the catalogs. Its removal was deliberate: I *wanted* to break any applications that were looking at it, to ensure that they got updated. If we'd left it there but nonfunctional, we'd have had subtle bugs in apps instead of obvious ones. We will have the same tradeoff to make with respect to these views, anytime the underlying reality changes in incompatible ways ... which it surely will. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
Tom, Peter, That said, I don't particularly care for this proposal. If you want a human-readable version of the system catalogs, I suggest you work on extensions of the information schema, not a completely new interface. So, both of your would prefer that we break the SQL spec with the information schema? In order to cover all PG objects? Because that's what your proposing. Either the information schema adheres to the spec, or it only covers 25% of PostgreSQL objects. There isn't a 3rd alternative. I'm fine with merging this with the information_schema (some of these views are derived from the same code) but it's either/or. So? If you want reality, look at the catalogs. The system catalogs are NOT user-friendly, nor are they meant to be. The purpose of the new system views is to answer questions like, what objects does user gregory have permissions on? and do any of my fuctions use custom type 'joebert' before I change it? and I need a way to query all of my functions in a loop so that I can change their permissions. You can get that info from the system catalogs, but only if you're a SQL wizard and know them very well. These are all things that users (NOT pg hackers) have to do for applications daily, and that we currently don't provide any easy, comprehensible way to access. It's certainly easy for pg hackers to say, oh, use the system tables but those tables are baffling and awkward for the many thousands of users who are not PG hackers. Elein's series on General Bits covering queries which were the inspiration for many of the views was immensely popular. Frankly, this is sounding a lot like Who needs OpenOffice.org? Use vi! Further, Jim and I went over the purpose of the views on this list two months ago and even Tom seemed positive to the idea. What the hell changed? No wonder the discussion of the decision process dragged on so. If we introduce a proposal, do an implementation, and come back with it 2 months later, I don't expect a bunch of core members to act like they've never heard of it before. If there are things (like not dropping columns) that you want to change about the spec, fine. But if you think that nobody needs these views, it's because you haven't had much contact with end users lately. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views! (long)
This is the kind of thing that support engineers love. Instead of having to email a bunch of complex SQL statements to a customer that is having trouble, they can verbally walk through a system catalog type query. My proprietary database has a very simple system catalog that very human readable, which saves time and improves accuracy. So, it isn't just end users, it is those that touch them as well. -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 11:11 AM Cc: PostgreSQL-development Subject: Re: Views, views, views! (long) [...snip...] If there are things (like not dropping columns) that you want to change about the spec, fine. But if you think that nobody needs these views, it's because you haven't had much contact with end users lately. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Views, views, views! (long)
Why not tack on the missing functionality to the INFORMATION_SCHEMA views? A couple of new tables and foreign keys should do it, n'est ce pas? -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: Wednesday, May 04, 2005 10:02 PM To: Tom Lane Cc: PostgreSQL-development Subject: Re: [HACKERS] Views, views, views! (long) Tom, To put it more bluntly: exactly what are you accomplishing here that isn't already accomplished, in a *truly* standard fashion, by the INFORMATION_SCHEMA? Why do we need yet another nonstandard view on the underlying reality? To quote myself: Q: Why not just use information_schema? A: Because the columns and layout of information_schema is strictly defined by the SQL standard. This prevents it from covering all PostgreSQL objects, or from covering the existing objects adequately to replicate a CREATE statement. As examples, there is no types table in information_schema, and the constraints table assumes that constraint names are universally unique instead of table-unique as they are in PG. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
Josh Berkus wrote: Frankly, this is sounding a lot like Who needs OpenOffice.org? Use vi! Frankly, this is sounding a lot like Who needs pgadmin/phppgadmin/pgaccess/younameit? use SELECT * FROM pg_somewot in psql instead. As Dave already pointed out, serious admin tools will avoid views. We have to deal with version specific issues anyway. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
Andreas, As Dave already pointed out, serious admin tools will avoid views. We have to deal with version specific issues anyway. Actually, I don't think that's what Dave said. He simply said that modifying pgAdmin to keep up with pg_catalog changes hasn't actually been a problem. And, as an increasing number of 3rd-party tools support PostgreSQL (like Embarcadero) they need a simple comprehensible API for system objects -- more objects than are included in the information_schema. I'm currently working on the integration of a major DSS tool with PostgreSQL, and we're already using the alpha version of the system views because we need them. A 3rd party proprietary vendor is not going to learn about OIDs, and they're not going to use pgAdmin. When we discussed this on this list 2 months ago, I was under the impression that extending the information_schema was verboten becuase it would break the SQL spec. If that's not the case, I personally would love to not duplicate objects. But let's establish that. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views! (long)
Josh Berkus wrote: And, as an increasing number of 3rd-party tools support PostgreSQL (like Embarcadero) they need a simple comprehensible API for system objects -- more objects than are included in the information_schema. There are only two choices: Creating a minimal subset tool, which will rely on INFORMATION_SCHEMA (or a schema API as in ODBC) as standardized by SQL specs, or making it specifically for every DBMS, whether using some fancy views or not. A 3rd party proprietary vendor is not going to learn about OIDs, Doing it seriously, it probably needs the internal DBMS object identifiers (oid in the case of pgsql), to uniquely identify objects even after a rename. Hiding the OIDs in schema views will reduce their usability. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views! (long)
Andreas, There are only two choices: Creating a minimal subset tool, which will rely on INFORMATION_SCHEMA (or a schema API as in ODBC) as standardized by SQL specs, or making it specifically for every DBMS, whether using some fancy views or not. Thing is, INFORMATION_SCHEMA doesn't hold a lot of information that people need to know. Like permissions, comments, object owners, functions, types, etc. If adding columns and views to the Information schema ... and changing keys in a couple of places ... is OK, then we have somewhere to go. Unfortunately, PostgreSQL does not have a seat on the ANSI committee, so we're not going to get the standard changed. The standard lately belongs to Oracle and DB2 and we have to suffer under it. Doing it seriously, it probably needs the internal DBMS object identifiers (oid in the case of pgsql), to uniquely identify objects even after a rename. Hiding the OIDs in schema views will reduce their usability. Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs are not consistent after a database reload and names are. I can see your point though; what do other people think? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: Thursday, May 05, 2005 10:49 AM To: Andreas Pflug Cc: PostgreSQL-development Subject: Re: [HACKERS] Views, views, views! (long) Andreas, There are only two choices: Creating a minimal subset tool, which will rely on INFORMATION_SCHEMA (or a schema API as in ODBC) as standardized by SQL specs, or making it specifically for every DBMS, whether using some fancy views or not. Thing is, INFORMATION_SCHEMA doesn't hold a lot of information that people need to know. Like permissions, COLUMN_PRIVILEGES Has one row for each column level permission granted to or by the current user TABLE_PRIVILEGES Has one row for each table level permission granted to or by the current user comments, These tables contain commentary information: CREATE TABLE SQL_FEATURES CREATE TABLE SQL_IMPLEMENTATION_INFO CREATE TABLE SQL_LANGUAGES CREATE TABLE SQL_SIZING_PROFILES CREATE VIEW SQL_FEATURES CREATE VIEW SQL_IMPLEMENTATION_INFO CREATE VIEW SQL_IMPL_INFO CREATE VIEW SQL_PACKAGES CREATE VIEW SQL_SIZING CREATE VIEW SQL_SIZING_PROFILES CREATE VIEW SQL_SIZING_PROFS And you can tack on more tables as needed. object owners, Ownership stuff is contained in these: CREATE TABLE SCHEMATA CREATE VIEW ASSERTIONS CREATE VIEW ATTRIBUTES CREATE VIEW CHARACTER_SETS CREATE VIEW CHECK_CONSTRAINTS CREATE VIEW COLLATIONS CREATE VIEW COLUMNS CREATE VIEW COLUMN_DOMAIN_USAGE CREATE VIEW COLUMN_UDT_USAGE CREATE VIEW CONSTRAINT_COLUMN_USAGE CREATE VIEW CONSTRAINT_TABLE_USAGE CREATE VIEW DIRECT_SUPERTABLES CREATE VIEW DIRECT_SUPERTYPES CREATE VIEW DOMAINS CREATE VIEW DOMAIN_CONSTRAINTS CREATE VIEW DOMAIN_UDT_USAGE CREATE VIEW KEY_COLUMN_USAGE CREATE VIEW METHOD_SPECIFICATIONS CREATE VIEW METHOD_SPECIFICATION_PARAMETERS CREATE VIEW PARAMETERS CREATE VIEW REFERENTIAL_CONSTRAINTS CREATE VIEW ROUTINES CREATE VIEW ROUTINE_COLUMN_USAGE CREATE VIEW ROUTINE_TABLE_USAGE CREATE VIEW SCHEMATA CREATE VIEW SCHEMATA_S CREATE VIEW TABLES CREATE VIEW TABLE_CONSTRAINTS CREATE VIEW TRANSFORMS CREATE VIEW TRANSLATIONS CREATE VIEW TRIGGERED_UPDATE_COLUMNS CREATE VIEW TRIGGERS CREATE VIEW TRIGGER_COLUMN_USAGE CREATE VIEW TRIGGER_TABLE_USAGE CREATE VIEW USER_DEFINED_TYPES CREATE VIEW VIEWS CREATE VIEW VIEW_COLUMN_USAGE CREATE VIEW VIEW_TABLE_USAGE If you need more than what is here, create an ownership table that is connected to the others using key relationships. functions, ROUTINES Lists one row for each stored procedure or user-defined function ROUTINE_COLUMNS Contains one row for each column returned by any table-valued functions types, DOMAIN_CONSTRAINTS Lists the user-defined datatypes that have rules bound to them DOMAINS Lists the user-defined datatypes etc. If adding columns and views to the Information schema ... and changing keys in a couple of places ... is OK, then we have somewhere to go. Create a new relation that is tied to the table of interest with a key. Unfortunately, PostgreSQL does not have a seat on the ANSI committee, so we're not going to get the standard changed. The standard lately belongs to Oracle and DB2 and we have to suffer under it. Doing it seriously, it probably needs the internal DBMS object identifiers (oid in the case of pgsql), to uniquely identify objects even after a rename. Hiding the OIDs in schema views will reduce their usability. Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs are not consistent after a database reload and names are. I can see your point though; what do other people think? Imagine (if you will) 100 different database systems, each of which has a different way to access the system tables, and each of which changes the tables whenever they want. If this picture is firm in mind, then the absolute necessity of INFORMATION_SCHEMA will crystallize. Whether or not OID values are published pales in comparison. Of course, if they do become visible, they should not pollute the INFORMATION_SCHEMA. IMO-YMMV. --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
Josh Berkus wrote: Either the information schema adheres to the spec, or it only covers 25% of PostgreSQL objects. There isn't a 3rd alternative. I'm fine with merging this with the information_schema (some of these views are derived from the same code) but it's either/or. I can think of a couple of ways offhand about how the information schema could be extended without breaking the SQL standard. You could just add columns where needed. Or you could add tables that are joined to the standard tables and contain the extra information. Or you could create a information_schema_2 that contains a copy of the original information schema with the extra information added somewhere, so users can easily switch back and forth. If you look closer, there isn't really all that much that cannot be gotten from the information schema. Figuring out exactly what that is might be instructive before deciding how to go forward. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
Peter, I can think of a couple of ways offhand about how the information schema could be extended without breaking the SQL standard. You could just add columns where needed. Or you could add tables that are joined to the standard tables and contain the extra information. Or you could create a information_schema_2 that contains a copy of the original information schema with the extra information added somewhere, so users can easily switch back and forth. If you look closer, there isn't really all that much that cannot be gotten from the information schema. Figuring out exactly what that is might be instructive before deciding how to go forward. This makes sense; I do wish that someone had mentioned it when I originally raised the subject of new system views. It would have saved us some work. --Josh -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
On Thu, May 05, 2005 at 14:26:56 +0400, Oleg Bartunov oleg@sai.msu.su wrote: Josh, it's very difficult to read your messages (I'm using Pine), because of some symbols (~Z on my xterm) which broke formatting. Is't known problem of pine (4.62) or your mailer ? There were a lot of \240 characters. I use mutt. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
Guys, it's very difficult to read your messages (I'm using Pine), because of some symbols (~Z on my xterm) which broke formatting. Is't known problem of pine (4.62) or your mailer ? There were a lot of \240 characters. I use mutt. Yeah, sorry, it's a KMail problem. I'm not sure how to fix it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
On 2005-05-05, Peter Eisentraut [EMAIL PROTECTED] wrote: Josh Berkus wrote: Either the information schema adheres to the spec, or it only covers 25% of PostgreSQL objects. There isn't a 3rd alternative. I'm fine with merging this with the information_schema (some of these views are derived from the same code) but it's either/or. I can think of a couple of ways offhand about how the information schema could be extended without breaking the SQL standard. You could just add columns where needed. How are you going to add a column to a view defined in the standard without risking conflict with future versions of that standard? How are you going to deal with the fact that the visibility rules for information_schema are sometimes completely wrong? The primary reason why I started writing catalog views for my own application usage was exactly _because_ those rules were wrong - I needed, for example, to be able to see which were the primary key columns for accessible tables (and all tables for the superuser), whereas information_schema limits the constraint views to tables owned by the current user. Or you could add tables that are joined to the standard tables and contain the extra information. Still the visibility problem. Or you could create a information_schema_2 that contains a copy of the original information schema with the extra information added somewhere, so users can easily switch back and forth. easily? information_schema is not something you ever want to put in your search path, so having an information_schema_2 would be no more convenient for users than our proposal. If you look closer, there isn't really all that much that cannot be gotten from the information schema. Figuring out exactly what that is might be instructive before deciding how to go forward. The first obvious thing is that the information schema tells you nothing at all that relates to _implementation_ rather than _semantics_. For example, there is nothing at all in it about indexes, since those are only an implementation detail. Nor does it tell you anything about tablespaces, the sizes of tables, aggregate functions, casts, databases, rules, sequences, or table inheritance. It tells you almost nothing about user-defined data types. It doesn't allow lookups based on OIDs that you received via the wire protocol (which exposes table and type OIDs quite a lot). information_schema is also known to be broken as regards looking up some constraints, thanks to the lack of schema-wide uniqueness of constraint names. In fact, it's possible to create foreign-key constraints that don't appear in information_schema at all, or which appear multiple times. information_schema also scales poorly with the size of the schema, since the use of standardised types interferes with the use of the system indexes. We deliberately decided to retain the name type for object names in our views in order to avoid this. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Views, views, views! (long)
On 2005-05-05, Josh Berkus josh@agliodbs.com wrote: This makes sense; I do wish that someone had mentioned it when I originally raised the subject of new system views. It would have saved us some work. I'd have raised it myself if I thought there was any mileage in it. As you can probably guess, I don't. information_schema is fine at what it is _intended_ for - as a standardized way of accessing a standard subset of the available metadata. In that sense it is still necessary - however it is not sufficient, and I don't believe that either the raw catalogs nor any reasonable extension of information_schema actually fills that gap. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
On Thu, May 05, 2005 at 08:15:27PM -, Andrew - Supernews wrote: On 2005-05-05, Josh Berkus josh@agliodbs.com wrote: This makes sense; I do wish that someone had mentioned it when I originally raised the subject of new system views. It would have saved us some work. I'd have raised it myself if I thought there was any mileage in it. As you can probably guess, I don't. information_schema is fine at what it is _intended_ for - as a standardized way of accessing a standard subset of the available metadata. In that sense it is still necessary - however it is not sufficient, and I don't believe that either the raw catalogs nor any reasonable extension of information_schema actually fills that gap. The information schema has the same problem that every other system built for every database does: it has a minimal set of abstract information, which prevents it, by design, from having application- specific functionality. Kudos to the New System Views people for their hard work thus far :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The purpose of the new system views... As long as they are in a separate schema (like information_schema, but hopefully not as long). pg_views? pg_info? information_skema? :) But if you think that nobody needs these views, it's because you haven't had much contact with end users lately. Well, who really *does* need these? After all, end users should be using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's the job of the people writing those interfaces to know the system catalogs well and present them to the users in a pretty fashion. If people want an easy way to look up the information, they use an interface. If not, they should learn the system catalogs. /devilsadvocate - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200505050632 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCefgSvJuQZxSWSsgRAlhmAKCXhqS9b5n5PgmWutFAzR6D5rg5SQCfQpuL qpOQ/PBghtBbI8RkJ2tXM7s= =0d0+ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
Greg, Well, who really *does* need these? After all, end users should be using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's the job of the people writing those interfaces to know the system catalogs well and present them to the users in a pretty fashion. If people want an easy way to look up the information, they use an interface. If not, they should learn the system catalogs. /devilsadvocate Well, because of who worked on it we were primarily thinking of custom applications developers and integrators, who fall somewhere between dumb user and pg hacker, and thus need an interface which falls between pgAdmin and pg_class. Also database designers (not all database designers like using GUIs). And, as Lance points out, these system views would help as a diagnostic view for support engineers. Actually, I'll second that; I've had cause to recommend the CVS system views more than a dozen times to pgsql newbies on IRC. For example, there is an easy query you can do using the new system views (thanks, Andrew!) to check what unindexed foriegn keys you have. So, who would they be useful to? is everyone *except* the people on this list. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
On 2005-05-05, Greg Sabino Mullane [EMAIL PROTECTED] wrote: The purpose of the new system views... As long as they are in a separate schema (like information_schema, but hopefully not as long). pg_views? pg_info? information_skema? :) The proof-of-concept implementation puts them in pg_sysviews. This is by no means cast in stone. But if you think that nobody needs these views, it's because you haven't had much contact with end users lately. Well, who really *does* need these? After all, end users should be using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's the job of the people writing those interfaces to know the system catalogs well and present them to the users in a pretty fashion. If people want an easy way to look up the information, they use an interface. If not, they should learn the system catalogs. One thing that has become _absolutely_ clear to me in the process of writing these views is that telling people to use the system catalogs is a _really_ bad idea. I've seen a number of apps now that have been doing incorrect catalog lookups and breaking to a greater or lesser extent as a result; furthermore, writing the views has often required delving into details of the backend implementation that are not well documented. (See a recent discussion here on typmods for an example.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
On N, 2005-05-05 at 22:43 +, Greg Sabino Mullane wrote: The purpose of the new system views... As long as they are in a separate schema (like information_schema, but hopefully not as long). pg_views? pg_info? information_skema? :) But if you think that nobody needs these views, it's because you haven't had much contact with end users lately. Well, who really *does* need these? After all, end users should be using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). Perhaps they can be thouhgt of as another interface of some sort ;) They may be even not installed by default, but bundled and as easy to install as any (other) contrib module. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
On 2005-05-05, Josh Berkus josh@agliodbs.com wrote: And, as Lance points out, these system views would help as a diagnostic view for support engineers. Actually, I'll second that; I've had cause to recommend the CVS system views more than a dozen times to pgsql newbies on IRC. For example, there is an easy query you can do using the new system views (thanks, Andrew!) to check what unindexed foriegn keys you have. Another popular query that gets asked on IRC is how do I find out what permissions have been granted to user X? Try doing _that_ one in the system catalogs. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
There are several things to address in the flurry of messages. The first thing that the qa/support team did at Illustra was to write a series of views on the system catalog. It was the most pressing thing to do. Every single db engineer probably has one or two or seven views to look at objects in PostgreSQL. I have seen one decent schema diagram of the system catalogs and have despaired at creating one myself even though I know the catalogs pretty well. Using the -E option on psql does not even help much anymore because the queries are so complex they've been broken into smaller queries even though one should suffice. The \ options have improved but some, like df are still useless. Asking users to create their own views is not very user friendly. I have tried and modestly succeeded to steer the newview project to answer people's questions. This has also been the point of the system catalog views I have published on General Bits. These have been views I've written for my own toolset. What are my constraints? What functions operate on a particular data type? What are the functions underlying this aggregate? What foreign keys link to table x? These are just small samples which may or may not be answered by our set of views so far. There are lots of questions like this that are difficult for most people to get the answers to, yet the answers are crucial to managing the design of their database. The Information Schema is very good. But it is a set of views For All Databases and does not answer PostgreSQL specific questions. A set of views specifically for PostgreSQL, without the restraint of highly funded committees, is what is needed. Still we should, as Peter suggested, borrow terminology and techniques where ever possible and maintain some kind of consistency. There is no question in my mind that a simple SQL interface to answering the most pressing questions regarding the database is necessary. They may have to be updated with the system catalogs, but as Tom said, no one changes the catalogs unless it is absolutely necessary. Usability is a feature we don't put enough emphasis on, ever. New system views would help people be more productive with PostgreSQL, enable new interfaces to have better packaged information and help all of the people required to support a PostgreSQL database. Elein = [EMAIL PROTECTED]Varlena, LLCwww.varlena.com (510)655-2584(o) (510)543-6079(c) PostgreSQL Consulting, Support Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ == I have always depended on the [QA] of strangers. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Andrew - Supernews Sent: Thursday, May 05, 2005 4:55 PM To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Views, views, views! (long) On 2005-05-05, Josh Berkus josh@agliodbs.com wrote: And, as Lance points out, these system views would help as a diagnostic view for support engineers. Actually, I'll second that; I've had cause to recommend the CVS system views more than a dozen times to pgsql newbies on IRC. For example, there is an easy query you can do using the new system views (thanks, Andrew!) to check what unindexed foriegn keys you have. Equally interesting would be: How many duplicate keys do I have? An interesting database option might be: For any foreign key, if there is no existing inversion entry index then build one. Another interesting database option might be: Check the system catalogs for duplicate indexes and remove them if there are any two completely identical (in columns, asc/desc and type). Another popular query that gets asked on IRC is how do I find out what permissions have been granted to user X? Try doing _that_ one in the system catalogs. Obviously, this is a clear oversight by the standards committee. PostgreSQL is clearly not the only database with this important need. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views! (long)
INFORMATION_SCHEMA is what should be exposed to the end-users of PostgreSQL. Pg_schema (for lack of a better name for internal metadata) can be useful as well for all sorts of special purposes. Probably, INFORMATION_SCHEMA (as designed by the SQL Standards committee) does not need to worry about table inheritance, for example. The only danger I see is Pg_schema forging ahead and INFORMATION_SCHEMA lacking some crucial piece of data. Pg_schema should NOT be the end-user database catalog. I would even advocate hiding it from anything but internal processes. Anything that is totally crucial as a piece of functionality should be added to INFORMATION_SCHEMA as a kind of enhancement, if it cannot be stored in that schema as-is. I suspect that: 1. There is not a whole lot of stuff that cannot be directly stored in the INFORMATION_SCHEMA location without modifying it. 2. Almost all of the information that cannot fit will be useful to other database systems as well, and should be suggested to the ANSI/ISO committee. Since INFORMATION_SCHEMA is a very new idea (only two adopters that I know of so far) I expect it will need to grow and PostgreSQL could be one of the contributors. Whether the enhancements are accepted or not, it would be good to at least attempt to get them noticed. 3. Of the fragment that does not fall under 1 and 2, it should be a carefully documented extension that lives in the same place (INFORMATION_SCHEMA) so that we know where to look to find it. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of elein Sent: Thursday, May 05, 2005 4:55 PM To: PostgreSQL-development Cc: Josh Berkus; Peter Eisentraut Subject: Re: [HACKERS] Views, views, views! (long) There are several things to address in the flurry of messages. The first thing that the qa/support team did at Illustra was to write a series of views on the system catalog. It was the most pressing thing to do. Every single db engineer probably has one or two or seven views to look at objects in PostgreSQL. I have seen one decent schema diagram of the system catalogs and have despaired at creating one myself even though I know the catalogs pretty well. Using the -E option on psql does not even help much anymore because the queries are so complex they've been broken into smaller queries even though one should suffice. The \ options have improved but some, like df are still useless. Asking users to create their own views is not very user friendly. I have tried and modestly succeeded to steer the newview project to answer people's questions. This has also been the point of the system catalog views I have published on General Bits. These have been views I've written for my own toolset. What are my constraints? What functions operate on a particular data type? What are the functions underlying this aggregate? What foreign keys link to table x? These are just small samples which may or may not be answered by our set of views so far. There are lots of questions like this that are difficult for most people to get the answers to, yet the answers are crucial to managing the design of their database. The Information Schema is very good. But it is a set of views For All Databases and does not answer PostgreSQL specific questions. A set of views specifically for PostgreSQL, without the restraint of highly funded committees, is what is needed. Still we should, as Peter suggested, borrow terminology and techniques where ever possible and maintain some kind of consistency. There is no question in my mind that a simple SQL interface to answering the most pressing questions regarding the database is necessary. They may have to be updated with the system catalogs, but as Tom said, no one changes the catalogs unless it is absolutely necessary. Usability is a feature we don't put enough emphasis on, ever. New system views would help people be more productive with PostgreSQL, enable new interfaces to have better packaged information and help all of the people required to support a PostgreSQL database. Elein = [EMAIL PROTECTED]Varlena, LLCwww.varlena.com (510)655-2584(o) (510)543-6079(c) PostgreSQL Consulting, Support Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ == I have always depended on the [QA] of strangers. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
Dann, 1. There is not a whole lot of stuff that cannot be directly stored in the INFORMATION_SCHEMA location without modifying it. See Andrew's post. There is a whole lot of stuff not covered by I_S in a way that is useful to PGSQL users. Also this would require making information_schema part of the default user path. 2. Almost all of the information that cannot fit will be useful to other database systems as well, and should be suggested to the ANSI/ISO committee. Since INFORMATION_SCHEMA is a very new idea (only two adopters that I know of so far) I expect it will need to grow and PostgreSQL could be one of the contributors. Whether the enhancements are accepted or not, it would be good to at least attempt to get them noticed. I like that idea, actually. However, there's not a whelk's chance in a supernova of our suggestions getting read, let alone accepted. But we could make the effort. You volunteering? 3. Of the fragment that does not fall under 1 and 2, it should be a carefully documented extension that lives in the same place (INFORMATION_SCHEMA) so that we know where to look to find it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
-Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 5:35 PM To: Dann Corbit Cc: elein; PostgreSQL-development; Peter Eisentraut Subject: Re: [HACKERS] Views, views, views! (long) Dann, 1. There is not a whole lot of stuff that cannot be directly stored in the INFORMATION_SCHEMA location without modifying it. See Andrew's post. There is a whole lot of stuff not covered by I_S in a way that is useful to PGSQL users. Also this would require making information_schema part of the default user path. It is supposed to be part of the default user path for all users of the database. 2. Almost all of the information that cannot fit will be useful to other database systems as well, and should be suggested to the ANSI/ISO committee. Since INFORMATION_SCHEMA is a very new idea (only two adopters that I know of so far) I expect it will need to grow and PostgreSQL could be one of the contributors. Whether the enhancements are accepted or not, it would be good to at least attempt to get them noticed. I like that idea, actually. However, there's not a whelk's chance in a supernova of our suggestions getting read, let alone accepted. But we could make the effort. You volunteering? It's probably like ANSI/ISO C and C++ committees -- they only listen if you actually show up to the meetings. I doubt if I could pay to go and attend in person. Maybe some of the Redhat people can, since they maintain the Redhat database. For sure, if the DB/2 or Oracle or MS or ... people hear about it and see that it is a good idea, they will push for it also. Otherwise, the competitors who adopt it will have a competitive advantage. 3. Of the fragment that does not fall under 1 and 2, it should be a carefully documented extension that lives in the same place (INFORMATION_SCHEMA) so that we know where to look to find it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views! (long)
On Thu, May 05, 2005 at 05:24:43PM -0700, Dann Corbit wrote: INFORMATION_SCHEMA is what should be exposed to the end-users of PostgreSQL. Pg_schema (for lack of a better name for internal metadata) can be useful as well for all sorts of special purposes. Probably, INFORMATION_SCHEMA (as designed by the SQL Standards committee) does not need to worry about table inheritance, for example. The only danger I see is Pg_schema forging ahead and INFORMATION_SCHEMA lacking some crucial piece of data. Pg_schema should NOT be the end-user database catalog. I would even advocate hiding it from anything but internal processes. Anything that is totally crucial as a piece of functionality should be added to INFORMATION_SCHEMA as a kind of enhancement, if it cannot be stored in that schema as-is. I suspect that: 1. There is not a whole lot of stuff that cannot be directly stored in the INFORMATION_SCHEMA location without modifying it. On close examination of PostgreSQL's extensibility capabilities will show you that this is actually not the case. 2. Almost all of the information that cannot fit will be useful to other database systems as well, and should be suggested to the ANSI/ISO committee. Since INFORMATION_SCHEMA is a very new idea (only two adopters that I know of so far) I expect it will need to grow and PostgreSQL could be one of the contributors. Whether the enhancements are accepted or not, it would be good to at least attempt to get them noticed. The SQL committee is s l o w l y catching up to the OR capability that was designed into Postgres back in '86. As other databases finally get around to adopting similar models the items are added to the standards. In many ways, SQL2003 is finally catching up with some of what Postgres was designed to do. We already have been influencing the committee for years indirectly. 3. Of the fragment that does not fall under 1 and 2, it should be a carefully documented extension that lives in the same place (INFORMATION_SCHEMA) so that we know where to look to find it. This is the bulk of what we are working on. Some is duplicated work with the information schema but it usually has more information or context. --elein [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of elein Sent: Thursday, May 05, 2005 4:55 PM To: PostgreSQL-development Cc: Josh Berkus; Peter Eisentraut Subject: Re: [HACKERS] Views, views, views! (long) There are several things to address in the flurry of messages. The first thing that the qa/support team did at Illustra was to write a series of views on the system catalog. It was the most pressing thing to do. Every single db engineer probably has one or two or seven views to look at objects in PostgreSQL. I have seen one decent schema diagram of the system catalogs and have despaired at creating one myself even though I know the catalogs pretty well. Using the -E option on psql does not even help much anymore because the queries are so complex they've been broken into smaller queries even though one should suffice. The \ options have improved but some, like df are still useless. Asking users to create their own views is not very user friendly. I have tried and modestly succeeded to steer the newview project to answer people's questions. This has also been the point of the system catalog views I have published on General Bits. These have been views I've written for my own toolset. What are my constraints? What functions operate on a particular data type? What are the functions underlying this aggregate? What foreign keys link to table x? These are just small samples which may or may not be answered by our set of views so far. There are lots of questions like this that are difficult for most people to get the answers to, yet the answers are crucial to managing the design of their database. The Information Schema is very good. But it is a set of views For All Databases and does not answer PostgreSQL specific questions. A set of views specifically for PostgreSQL, without the restraint of highly funded committees, is what is needed. Still we should, as Peter suggested, borrow terminology and techniques where ever possible and maintain some kind of consistency. There is no question in my mind that a simple SQL interface to answering the most pressing questions regarding the database is necessary. They may have to be updated with the system catalogs, but as Tom said, no one changes the catalogs unless it is absolutely necessary. Usability is a feature we don't put enough emphasis on, ever. New system views would help people be more productive with PostgreSQL, enable new interfaces to have better packaged information and help all of the people required to support a PostgreSQL database. Elein
Re: [HACKERS] Views, views, views! (long)
As Dave already pointed out, serious admin tools will avoid views. We have to deal with version specific issues anyway. I don't see why phpPgAdmin would avoid using the views, unless some serious randomness happened that we had to support. The unimaginable craziness of currently trying to support multiple versions of postgresql is pretty bad, and the information_schema is next to useless for these things since it doesn't have PostgreSQL specific things in it. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs are not consistent after a database reload and names are. I can see your point though; what do other people think? Well phpPgAdmin is unable to use the pg_tables view, for instance, because we have no way of getting the table comment using the information in that view... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
On 2005-05-06, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs are not consistent after a database reload and names are. I can see your point though; what do other people think? Well phpPgAdmin is unable to use the pg_tables view, for instance, because we have no way of getting the table comment using the information in that view... If you look at the columns lists, you'll find that oids are exposed in a number of places. In general, I didn't make a point of exposing them everywhere, but I _did_ expose them in cases where I thought it likely that querying by or for the oid in particular might be needed. (OIDs are, after all, exposed quite a bit by the wire protocol and by libpq.) Whether the balance is correct here is something I'm open to suggestions about. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
Dann Corbit [EMAIL PROTECTED] writes: 2. Almost all of the information that cannot fit will be useful to other database systems as well, and should be suggested to the ANSI/ISO committee. Since INFORMATION_SCHEMA is a very new idea (only two adopters that I know of so far) I expect it will need to grow and PostgreSQL could be one of the contributors. [ raised eyebrow... ] INFORMATION_SCHEMA is in the SQL92 spec. If only two systems have adopted it in the last 13 years, it's a failure and the SQL committee is unlikely to want to spend any time on it. In any case, they just issued a spec and so there is very unlikely to be another update for another five or so years. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
2. Almost all of the information that cannot fit will be useful to other database systems as well, and should be suggested to the ANSI/ISO committee. Since INFORMATION_SCHEMA is a very new idea (only two adopters that I know of so far) I expect it will need to grow and PostgreSQL could be one of the contributors. [ raised eyebrow... ] INFORMATION_SCHEMA is in the SQL92 spec. If only two systems have adopted it in the last 13 years, it's a failure and the SQL committee is unlikely to want to spend any time on it. In any case, they just issued a spec and so there is very unlikely to be another update for another five or so years. INFORMATION_SCHEMA is also in MySQL 5, so that makes 3 :D Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views! (long)
On Thursday 05 May 2005 19:37, Hannu Krosing wrote:
On N, 2005-05-05 at 22:43 +, Greg Sabino Mullane wrote:
The purpose of the new system views...
As long as they are in a separate schema (like information_schema,
but hopefully not as long). pg_views? pg_info? information_skema? :)
But if you think that nobody needs these views, it's because you
haven't had much contact with end users lately.
Well, who really *does* need these? After all, end users should be
using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc).
Perhaps they can be thouhgt of as another interface of some sort ;)
They may be even not installed by default, but bundled and as easy to
install as any (other) contrib module.
I was starting to think this... like this should be a project on foundry
called enhanced system views that would be fairly database version
independant and people could install into any databases they needed them in.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views! (long)
On Thursday 05 May 2005 19:17, Andrew - Supernews wrote:
On 2005-05-05, Greg Sabino Mullane [EMAIL PROTECTED] wrote:
furthermore, writing the views has often required
delving into details of the backend implementation that are not well
documented. (See a recent discussion here on typmods for an example.)
This argument at least is a red herring. The answer to a lack of
documentation about a widget is never to build more widgets that hide the
original widget and require more documentation, the answer is to better
document the orginal widget.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
I was starting to think this... like this should be a project on foundry called enhanced system views that would be fairly database version independant and people could install into any databases they needed them in. You mean like: http://pgfoundry.org/projects/newsysviews/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views! (long)
On 2005-05-06, Robert Treat [EMAIL PROTECTED] wrote: On Thursday 05 May 2005 19:17, Andrew - Supernews wrote: On 2005-05-05, Greg Sabino Mullane [EMAIL PROTECTED] wrote: furthermore, writing the views has often required delving into details of the backend implementation that are not well documented. (See a recent discussion here on typmods for an example.) This argument at least is a red herring. The answer to a lack of documentation about a widget is never to build more widgets that hide the original widget and require more documentation, the answer is to better document the orginal widget. I disagree. If you have a bad interface, the fix is to provide a better one, not aggravate the problem by encouraging even more use of the bad interface. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views! (long)
Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-05-06, Robert Treat [EMAIL PROTECTED] wrote: On Thursday 05 May 2005 19:17, Andrew - Supernews wrote: furthermore, writing the views has often required delving into details of the backend implementation that are not well documented. (See a recent discussion here on typmods for an example.) This argument at least is a red herring. I disagree. If you have a bad interface, the fix is to provide a better one, not aggravate the problem by encouraging even more use of the bad interface. Bad and poorly documented are two quite different aspersions. More to the point: how can you build a good interface on top of a bad one? Whatever fundamental shortcomings exist in the latter cannot be hidden by the former. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views! (long)
Tom, Andrew, Robert, More to the point: how can you build a good interface on top of a bad one? Whatever fundamental shortcomings exist in the latter cannot be hidden by the former. I think bad and good are pretty irrelevant myself. The system tables are very good at what they do: support the postgresql code base. They are not *meant* to be user-friendly. That's why we need a different interface to be good for a different purpose. Which is what we're trying to do. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Views, views, views! (long)
PG hackers, AndrewSN, Jim Nasby, Elein and I have been working for the last couple of months on a new set of system views for PostgreSQL. (primarily Andrew, who did the lion's share of the work and came up with many clever SQL workarounds) We'd like to include them in the 8.1 release, so we're going to post most of the definitions for your feedback now. Let me summarize: Goals of the New System Views --- 1. To be easily human-readable, unlike the system tables. 2. To provide a consistent API to PostgreSQL object definitions which will seldom (if ever) be changed, only added to. 3. To provide queryable definitions for all PostgreSQL objects. In more detail: 1. The current system tables are designed around performance and code requirements, and as such are optimized for code access, not comprehensability. Column names are obscure, special system data types are used, and everything is OIDs and typids. This is perfect for our code, but too many user-space applications are using these tables for comfort. Our first system views (pg_tables, for example) only went halfway in providing a user-friendly interface. So the new system views have the following requirements: a) all view and column names are as explicit and as readable as possible (e.g. type_schema_name, not typnsname) b) OIDs, typids, and other system codes are avoided wherever possible in preference to full object names c) In most places, system objects are segregated from user objects, e.g. pg_user_indexes 2. One of the issues with user applications querying the system tables is that they can be subject to significant changes from version to version. This has kept the pgAdmin and phpPgAdmin teams busy since 7.2, and means that GUI tools which fall out of maintenance (like Xpg) soon stop working. This is easily remedied through a set of system views which will remain consistent regardless of changes in the underlying system tables. This has the beneficial effect of giving us more freedom to make changes to the system tables. Further, we discovered when we proposed dropping the old system views that once these views are created we're stuck with them for several years, if not forever; people's not-easily-recoded tools rely on them. d) Columns may be added to the system views, but never dropped or changed in incompatible ways. Likewise, views will be added but not dropped or renamed. e) Users and app developers should be actively encouraged to use the system views rather than the system tables in the documentation. f) Existing projects, features and add-ons, where appropriate, should gradually be shifted to use the system views to minimize version maintenance. 3. The new system views (unlike, for example, \d) are designed to be a SQL interface to system objects. This means that: g) All views are as normalized as possible, using child views rather than arrays, and providing keys and consistent join columns. h) Each view or set of views provides all of the data required to replicate the appropriate CREATE statement. i) Column names are designed to be universal to a particular type of data, where this does not cause duplication. For example, pg_user_tables has schema_name rather than table_schema. This was done to make joins easier (i.e. USING, NATURAL JOIN) Additional assumptions we worked with include: j) all view names are plural in order to prevent conflict with system tables. k) no procedural languages are used, as we don't want to make PLs mandatory. Currently everything is SQL (really!) and we may move a few functions to a C library eventually. l) internal functions required for the system views are named using a _pg_ convention. m) We will be offering a back-patch for 7.4 and 8.0 via pgFoundry. What We Need From Hackers -- (other than patch approval, that is) As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. As such, we really can't afford to do major refactoring of the column names and structure once they're released. So it's really, really, important for everyone on hackers to look over the definitions below and find stuff that we've missed or doesn't make any sense. Also, we'd like to know about 8.1 changes that affect these views. There are two additional other questions to discuss that our team as not settled: I) Should the new views be part of /contrib before they become part of the main source? II) Should the new views be in their own schema? This would make them easier to manage for DBAs who want to restrict access or dump them, but would add a second system schema to the template.
Re: [HACKERS] Views, views, views! (long)
Josh Berkus josh@agliodbs.com writes: As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. I dislike to burst your bubble, but this claim is ridiculous on its face. We don't whack the system catalogs around from release to release just because we'd like to break as many user applications as possible ... quite the contrary. When we change the catalogs it's because there is some fairly fundamental change in functionality involved. I think the idea that some views in front of the catalogs can hide this problem is the merest pipe dream. To put it more bluntly: exactly what are you accomplishing here that isn't already accomplished, in a *truly* standard fashion, by the INFORMATION_SCHEMA? Why do we need yet another nonstandard view on the underlying reality? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
Tom, To put it more bluntly: exactly what are you accomplishing here that isn't already accomplished, in a *truly* standard fashion, by the INFORMATION_SCHEMA? Why do we need yet another nonstandard view on the underlying reality? To quote myself: Q: Why not just use information_schema? A: Because the columns and layout of information_schema is strictly defined by the SQL standard. This prevents it from covering all PostgreSQL objects, or from covering the existing objects adequately to replicate a CREATE statement. As examples, there is no types table in information_schema, and the constraints table assumes that constraint names are universally unique instead of table-unique as they are in PG. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Views, views, views! (long)
Josh Berkus wrote: PG hackers, [snip] What We Need From Hackers -- (other than patch approval, that is) As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. As such, we really can't afford to do major refactoring of the column names and structure once they're released. So it's really, really, important for everyone on hackers to look over the definitions below and find stuff that we've missed or doesn't make any sense. Also, we'd like to know about 8.1 changes that affect these views. This all looks good to me, from a quick read through. I don't claim to have examined the details, but the general idea is definitely something that would be very worth having. A nice thing to add would be a more human-comprehensible view of the pg_locks table. I keep meaning to write a view for it myself, but haven't ever gotten a round tuit. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views! (long)
Tim, A nice thing to add would be a more human-comprehensible view of the pg_locks table. I keep meaning to write a view for it myself, but haven't ever gotten a round tuit. Jim Nasby is working on that; see his other posts. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
