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:

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,
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 ). 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:

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.


Code modification/commit from ansgar.becker, 9 years ago, revision
PostgreSQL: Make TPGConnection.FetchDbObjects compatible to pre-9.0 servers. See
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 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.