Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Cannot see tables in Postgressql placed in Azure

heidiNewbie's profile image heidiNewbie posted 2 months ago in General Permalink

Hi,

I'm new to HeidiSQL so maybe I am missing something, but I cannot see my tables anywhere. I'm connected to a PostgreSQL database located in Azure, and I can make queries. But "autocomplete" for tables shows an empty box, and also, I cannot see the tables anywhere in the GUI.

Shouldn't there be a ree view or something somewhere?

BR heidiNewbie

ansgar's profile image ansgar posted 2 months ago Permalink

So you don't see such a tree on the left?

Description

heidiNewbie's profile image heidiNewbie posted 2 months ago Permalink

If I connect to a localhost PostgreSQL I can see all tables in the tree to the left. But "autocomplete" in query editor does not work on localhost either. It shows an empty box: Description

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
heidiNewbie's profile image heidiNewbie posted 2 months ago Permalink

I don't see the tree for the Azure database (about 1300 tables) I get this:

SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"='public';
/* ERROR:  canceling statement due to statement timeout */
heidiNewbie's profile image heidiNewbie posted 2 months ago Permalink

It seems like it is "pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length" that takes all the time. Running this query:

SELECT *
  --, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length
  --, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"='public'
LIMIT 100;

cuts the execution time from approx 30 sec. to 50 msec! So pg_table_size(...) and pg_relation_size(...) is extremely expensive. Anyway to avoid including these functions in the query to get all table names for the tree view?

heidiNewbie's profile image heidiNewbie posted 2 months ago Permalink

Setting the query timeout to 400 secs made the tree view appear. But waiting approx. 400 secs for HeidiSQL to start is a long time :-)

ansgar's profile image ansgar posted 2 months ago Permalink

Yes, definitely :) Will try your query approach, whether it's not only working faster, but also does not optimize some feature away.

Code modification/commit 483f6a1 from Ansgar Becker <anse@heidisql.com>, 2 months ago, revision 5890
Support disabling "Full table status" on PostgreSQL. See https://www.heidisql.com/forum.php?t=35681
ansgar's profile image ansgar posted 2 months ago Permalink

So, my just pushed change now adds support for the otherwise unused checkbox "Get full table status" for PostgreSQL. Find it in the session manager's "Advanced" tab:

Description

The hint says it's for speeding up work with many InnoDB tables, which is of course MariaDB specific. On PostgreSQL, this enables/disables the use of the above mentioned two functions pg_table_size() and pg_relation_size().

heidiNewbie's profile image heidiNewbie posted 1 month ago Permalink

Nice - thanx a lot for the quick response and fix Thumbs up

BR heidiNewbir

Please login to leave a reply, or register at first.