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: Various Errors on v7.4 Server

jnp posted 5 years ago in General
I recently tried to connect to an older PostgreSQL v7.4.8 server using HeidiSQL. I found that I was unable to list any objects (tables, views, etc.). I got the following errors:

SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER;
/* ERROR:  function pg_postmaster_start_time() does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts. */


SET SCHEMA 'mySchema';
/* ERROR:  syntax error at or near "'mySchema'"
LINE 1: SET SCHEMA 'mySchema'


SELECT *, NULL 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"='cricket';
/* ERROR:  function pg_relation_size(text) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts. */


I'm still doing some research, but it seems that PostgreSQL 7.4 does not support the SET SCHEMA syntax. As a possible work-around, I found the following information:

(taken from http://www.postgresql.org/docs/9.1/static/sql-set.html)

"SET SCHEMA 'value' is an alias for SET search_path TO value. Only one schema can be specified using this syntax."

So perhaps you could change the
SET SCHEMA 'mySchema';

call to
SET search_path TO 'mySchema';


I'll provide some more details as I come across them. Thanks.

-jnp
JohnC posted 4 years ago
I got the same error when using Greenplum (PostgreSQL8.2)
ansgar posted 4 years ago
So, shall I introduce a version conditional up to PG 8.2, using "searchpath TO" instead of "schema" ? Should that fix the above mentioned problems?
jnp posted 4 years ago
If I am understanding the aforementioned article correctly, the
SET search_path TO 'mySchema';
syntax should work on all versions of PostgreSQL. I don't think version-specific coding should be necessary.

I believe this change will fix the listing of objects in the database, but there are still some functions that are being used in other statements (details listed in my original post) which are not supported in PostgresSQL v7.4.8. These are:

pg_postmaster_start_time()
pg_relation_size(text)


I suspect that there may be others, but these are the only ones I am aware of currently. As I am able to, I will continue to seek out alternatives to these functions that are supported by all versions of PostgreSQL, but so far I have not discovered any. For now, perhaps a solution would be to implement error-handling for these unsupported functions that would disable certain features (such as listing object sizes) when the function call fails. Thanks.

-jnp

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