Database browser empty in PostgreSQL

Hi,

On one of the PostgreSQL databases I am connecting to, the database browser is empty.
I have realized that this problem comes from the fact that the queries used to populate the database browser refer to "information_schema". But "information_schema" shows any schemas owned by roles you have the ability to SET
ROLE to. If you don't have enough privileges, the queries on these schemas are empty, and the browser to.

This privileges restriction does not exist when querying on pg_catalog.
Could you convert queries used in HeidiSQL using "information_schema" in order to query "pg_catalog" instead ?
Because this could result in having nothing showed by HeidiSQL in a database browser when a user has just read privileges.

Thanks in advance.
Regards,
Cedric Duprez

Just tell me an exact query and I will see how to integrate that.

This is the original query:

SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME)) AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME)) AS index_length, c.reltuples, obj_description(c.oid) AS COMMENT
FROM "information_schema"."tables" AS t
LEFT JOIN "pg_namespace" n ON t.table_schema = n.nspname
LEFT JOIN "pg_class" c ON n.oid = c.relnamespace AND c.relname=t.table_name
WHERE t."table_schema"='pg_catalog';

... and this one looks like a promising alternative, does it?

SELECT *, pg_table_size(QUOTE_IDENT(t.schemaname) || '.' || QUOTE_IDENT(t.tablename)) AS data_length, pg_relation_size(QUOTE_IDENT(t.schemaname) || '.' || QUOTE_IDENT(t.tablename)) AS index_length, c.reltuples, obj_description(c.oid) AS COMMENT
FROM "pg_catalog"."pg_tables" AS t
LEFT JOIN "pg_namespace" n ON t.schemaname = n.nspname
LEFT JOIN "pg_class" c ON n.oid = c.relnamespace AND c.relname=t.tablename
WHERE t."schemaname"='pg_catalog';

Please test and give feedback.

OK, I see a little difference between the 2 queries. The original one retrieves 2 different table types : "BASE TABLE" and "VIEW", whereas the alternative one only retrieves information of "BASE TABLE" types.
I will investigate it further.

Hi - I posted this message yesterday to the wrong thread ("Experimental PostgreSQL support"). I though it might add more to this discussion, since I am experiencing the same PostgreSQL permission issue described here. Like cduprez, my database browser tree is also empty when I connect to a PostgreSQL database as a user who does not own any objects and has only read privileges.

"I am trying to connect to a PostgreSQL database (which is successful), but I am not able to get a listing of objects (tables, views, functions, etc.) in the PostgreSQL database. The tree view on the left is blank, and I assume that this is the user I am logging in as is read-only and owns no objects. I see that the query you are using to display PostgreSQL objects is:

SELECT "schema_name" FROM "information_schema"."schemata" ORDER BY "schema_name";

However, because I am not logged in as an admin, the query returns no records. Would it be possible to implement an alternative method for returning objects when the initial query returns no results?

I found a somewhat-comprehensive tutorial on how to accomplish this both by using the information_schema and (alternatively) using PostgreSQL-specific (pg_) tables:

http://www.alberton.info/postgresql_meta_info.html

Hopefully this will give you a running start if you decide to pursue this.

Thanks again for your hard work on HeidiSQL. Your efforts are greatly appreciated!"

-JP

This may also be part of my problem. The following query (based on information_schema) returns no rows for me (logged in as a user with only read privilege):

select schema_name
from information_schema.schemata;

But this query (based on pg_catalog) successfully lists all of the available schemas:

select nspname
from pg_catalog.pg_namespace;

-JP

r4840 queries pg_namespace instead of is.schemata. Please verify if that works for you.

Great! For me, it is OK. The treeview shows schemas and tables.
Good job.

OK.
Now still remains on all the queries that HeidiSQL uses for auto-completion in the query browser: they all refer to information_schema and should be converted to pg_catalog.

You're now talking about a different query, do you? Is it this one:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME='mytable';

If so, does that query have the same privilege problem as the other one? Do *all* queries on INFORMATION_SCHEMA tables have this problem?

Ooops, no.
Sorry, it seems to be OK for autocompletion.

Build r4840 is working very well for me now. Thank you very much for making the change so quickly! I really appreciate it - now I can use HeidiSQL for most of my PostgreSQL work, which will save me a ton of time. I just sent you a token of my thanks via PayPal. :-)

Build r4840 is working very well for me now. Thank you very much for making the change so quickly! I really appreciate it - now I can use HeidiSQL for most of my PostgreSQL work, which will save me a ton of time. I just sent you a token of my thanks via PayPal. :-)

Thanks a lot, jnp!

ansgar - you asked cduprez if all of the queries on INFORMATION_SCHEMA tables have problems. I think the answer is often (but not always) yes, but only if you are logged in as a read-only user (which I am).

For example, the following INFORMATION_SCHEMA query works for me (although there are NULLs in some columns):

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='my_schema' AND TABLE_NAME='my_table';

But this query returns no results (because INFORMATION_SCHEMA.TABLE_CONSTRAINTS returns no records for my read-only user):

SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, K.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.CONSTRAINT_NAME = K.CONSTRAINT_NAME AND K.TABLE_NAME='my_table' AND K.TABLE_SCHEMA='my_schema' WHERE C.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') ORDER BY K.ORDINAL_POSITION;

And this query returns a NULL record (which cannot be parsed and causes an error in HeidiSQL):

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='my_table' AND TABLE_SCHEMA='my_schema';

When I write the equivalent query against pg_views, it returns results for me:

SELECT DEFINITION FROM pg_views WHERE SCHEMANAME = 'my_schema' AND VIEWNAME = 'my_table';

As a general rule, it appears that more information is available from the pg_ tables regardless of permissions, while the information available from INFORMATION_SCHEMA is very permission-dependent.

Although I am sure it would be a significant effort, you would probably get better results for supporting PostgreSQL by using pg_ tables rather than INFORMATION_SCHEMA tables. As I mentioned in an earlier post, the following tutorial would be a good starting point, as it shows how to retrieve information from both the INFORMATION_SCHEMA and the pg_ tables:

http://www.alberton.info/postgresql_meta_info.html

Thanks again for all of your hard work.

-jnp

r4844 uses pg_views for getting a view definition:

SELECT DEFINITION FROM pg_views WHERE SCHEMANAME = 'my_schema' AND VIEWNAME = 'my_table';

What would be an appropriate pg_* alternative to this query, where I need to get primary and other keys, and one row per column name:

SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, K.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.CONSTRAINT_NAME = K.CONSTRAINT_NAME AND K.TABLE_NAME='my_table' AND K.TABLE_SCHEMA='my_schema' WHERE C.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') ORDER BY K.ORDINAL_POSITION;

Below is an equivalent query using pg_* tables. Note that you will need to use pg_constraint.confkey rather than pg_constraint.conkey if you want to get a list of the referenced FOREIGN KEY columns (see http://www.postgresql.org/docs/9.3/static/catalog-pg-constraint.html)

SELECT 
c.conname AS CONSTRAINT_NAME,
CASE c.contype
WHEN 'c' THEN 'CHECK'
WHEN 'f' THEN 'FOREIGN KEY'
WHEN 'p' THEN 'PRIMARY KEY'
WHEN 'u' THEN 'UNIQUE'
END AS CONSTRAINT_TYPE,
a.attname AS COLUMN_NAME
FROM pg_constraint c
LEFT JOIN pg_class t        ON c.conrelid  = t.oid
LEFT JOIN pg_attribute a    ON t.oid = a.attrelid
LEFT JOIN pg_namespace n    ON t.relnamespace = n.oid
WHERE c.contype IN ('p','u')    /* gets PRIMARY KEY and UNIQUE constraints */
AND a.attnum = ANY(c.conkey)    /* matches any item in the conkey array */
AND n.nspname = 'my_schema'     /* the schema name */
AND t.relname = 'my_table'      /* the table name */
ORDER BY a.attnum               /* this seems to put things in the right order,                   */
/* but the order of items in the c.conkey array may be more reliable */
;

Thanks a lot, I'll give that a try.

Done in r4845. Please update and check if that works for you.

That seems to have fixed it, as best I can tell. Thanks for the quick change.

However, in my testing I came across another issue with the more recent r4848 build. It was one of the first builds to use the SUBSTR function when getting table data, which is working fine overall. But this brought to light another issue - HeidiSQL does not seem to fully support the "interval" data type in PostgreSQL. HeidiSQL treats the "interval" data type as a VARCHAR, when it actually a Date/Time data type. Because of this, when the table browser attempts to do a SUBSTR on an "interval" data type column, the database raises an error and the table data is not displayed.

Here is some info on the "interval" data type in PostgreSQL:

http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

Perhaps a workaround would be to ensure any columns with an "interval" data type are treated as a TIMESTAMP rather than a VARCHAR. Thanks again for your help.

-jnp