Postgresql support improvments

Some thougths on improvements to postgresql specific support: h4. support showing grants on views h4. refine the ddl view If the user has no grants on a view or table the ddl view gives the message "... public.foo does not exist". The ddl is on the other hand shown with the psql command '\d'. h4. drop the levels "Databases" and Catalog in the browsing hierarchy Instead show "Schemas" under the connection node, like they are shown for Oracle databases. The two topmost levels show very little information and the database information (encoding, collation, acl, tablespace...) could be integrated in the connection object view. This would also reflect the fact that the driver only supports connecting to one database at a time. The information of all _other_ databses could be dropped altogether, IMHO. Thank You for a great product, Jonas Sundman Edited by: Jonas Sundman on 2011-sep-26 12:59 DDL broken on tables, too.

Hi Jonas, Thanks for your suggestions. > support showing grants on views I have opened a ticket for this. > refine the ddl view > If the user has no grants on a view or table the ddl view gives the message "... public.foo does not exist". The ddl is on the other hand shown with the psql command '\d'. This is kind of tricky to fix. DbVisualizer relies on the INFORMATION_SCHEMA views for the DDL info, and when there are no grants, they do not contain any info about the object. Why this is, I don't know. The "psql" command must use some other means to get the DDL, but it is not obvious how to extract it directly from the system tables. If you can find a way to do it, please let us know. > drop the levels "Databases" and Catalog in the browsing hierarchy > Instead show "Schemas" under the connection node, like they are shown for Oracle databases. The two topmost levels show very little information and the database information (encoding, collation, acl, tablespace...) could be integrated in the connection object view. This would also reflect the fact that the driver only supports connecting to one database at a time. The information of all _other_ databses could be dropped altogether, IMHO. I have opened a ticket for this. > Thank You for a great product, Glad you like it. Best Regards, Hans

> > refine the ddl view > > If the user has no grants on a view or table the ddl view gives the message "... public.foo does not exist". The ddl is on the other hand shown with the psql command '\d'. > > This is kind of tricky to fix. DbVisualizer relies on the INFORMATION_SCHEMA views for the DDL info, and when there are no grants, they do not contain any info about the object. Why this is, I don't know. The "psql" command must use some other means to get the DDL, but it is not obvious how to extract it directly from the system tables. If you can find a way to do it, please let us know. It really seems to be and let me share what I found out, so far. The INFORMATION_SCHEMA follows the SQL standard, which explicitly states that only tables and views to which the user has access should be shown (see eg. old bug disussion in [1]). The problem, as I understand it and interpret the situation, is that there is some misalignment in what metadata the jdbc driver (and psql) delivers as opposed what is shown in the INFORMATION_SCHEMA. For the moment (both in 8.4 and 9.0) a table which shows up in the browser and has some properties shown based on what the jdbc driver delivers won't have it's ddl shown since it's not showing up in the INFORMATION_SCHEMA. The information on objects are in postgres' own internal metadata schema PG_CATALOG and can be queried from there [2]. [1] http://grokbase.com/t/postgresql.org/pgsql-bugs/2005/10/bug-1937-parts-of-information-schema-only-accessible-to-owner/04j2nuthbsaq7otpvbkaqibpsdfy [2] http://www.alberton.info/postgresql_meta_info.html

Hi Jonas, Thanks for digging into this and posting what you have found. For now, we will not change how the DDL info is collected since it appears that the PostgreSQL community is aware of the problem with the INFORMATION_SCHEMA. Maybe you can revive the thread and see if it can be fixed at that end? Best Regards, Hans

Hi Jonas,

I just wanted to let you know that we have released DbVisualizer 9.5 today with fixes for some of the issues you reported. Please upgrade to the latest official version. Easiest is that you open Help->Check for Update in DbVisualizer to perform the upgrade.

An alternative is that you download from our web site: 

http://www.dbvis.com/download/

Best Regards,

Hans