Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

PostgreSQL: Error Listing Tables on v8.4 Server

jnp's profile image jnp posted 5 years ago in General
I recently had to connect to an older PostgreSQL v8.4.2 server, and I noticed that HeidiSQL could not successfully list the tables on the server. Upon further investigation, the error appears to be with the following 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"='public';


The above query throws this error on a PostgreSQL v8.4.2 server:

/* ERROR:  function pg_table_size(text) does not exist
LINE 1: SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' |...
^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. */


Thee root cause of this error is that the pg_table_size function was not supported until PostgreSQL v9.0 (see http://www.postgresql.org/docs/9.0/static/functions-admin.html ). I tried a couple of alternate methods to get table size, but I was not successful in getting the same table size as the pg_table_size function (testing on a v9.2.4 server). However, the following link may contain some information that could prove useful in getting the table size by an alternate method for PostgreSQL servers before v9.0:

https://wiki.postgresql.org/wiki/Disk_Usage

Alternately, if you could handle the error and not display table sizes for PostgreSQL servers lower than v9.0, it would at least allow tables to be listed in HeidiSQL.

Thanks!

-jnp
ansgar's profile image ansgar posted 5 years ago
Done in r4876 . Leaves the table sizes away for pre-9.0 servers. Please test.
jnp's profile image jnp posted 5 years ago
Thanks again - seems to be working fine now.smile I sent you another token of my thanks to help keep you motivated (via PayPal). Keep up the good work. I greatly appreciate it.
ansgar's profile image ansgar posted 5 years ago
Thanks, jnp!

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