PostgreSQL: Error Listing Tables on v8.4 Server

[expired user #7335]'s profile image [expired user #7335] posted 9 years ago in General Permalink
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
Code modification/commit from ansgar.becker, 9 years ago, revision 4876
PostgreSQL: Make TPGConnection.FetchDbObjects compatible to pre-9.0 servers. See http://www.heidisql.com/forum.php?t=16996
ansgar's profile image ansgar posted 9 years ago Permalink
Done in r4876 . Leaves the table sizes away for pre-9.0 servers. Please test.
[expired user #7335]'s profile image [expired user #7335] posted 9 years ago Permalink
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 9 years ago Permalink
Thanks, jnp!

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