Problems using Heidi with Postgres 8.4

I am connecting to a Postgres server (PostgreSQL 8.4.13 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit) successfully.

I can see the information_schema, pg_catalog, public, etc dbs and can see the list of tables in the lh column but when I try to select a table, the log shows the following SELECT running

STRING_AGG(distinct kcu.column_name, ',') AS columns,   ccu.table_schema AS ref_schema,   ccu.table_name AS ref_table,   
STRING_AGG(distinct ccu.column_name, ',') AS ref_columns,   
STRING_AGG(distinct kcu.ordinal_position::text, ',') AS ord_position 
FROM   information_schema.referential_constraints AS refc,   information_schema.key_column_usage AS kcu,   information_schema.constraint_column_usage AS ccu 
WHERE   refc.constraint_schema = 'public'   
    AND refc.constraint_name = kcu.constraint_name   
    AND refc.constraint_schema = kcu.table_schema   
    AND ccu.constraint_name = refc.constraint_name   
    AND kcu.table_name = 'activities' 
GROUP BY   refc.constraint_name,   refc.update_rule,   refc.delete_rule,   kcu.table_name,   ccu.table_schema,   ccu.table_name 
ORDER BY   ord_position;

and the error

LINE 2: STRING_AGG(distinct kcu.column_name, ',') AS columns,   ccu....*

followed by similar errors.

I also get the more obvious error

SELECT "pid", "usename", "client_addr", "datname", application_name , EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - "query_start")::INTEGER, "state", "query" FROM "pg_stat_activity";
/* ERROR:  column "pid" does not exist

Indeed, the column "pid" doesn't exist in pg_stat_activity - it should be "procpid" (and application_name and state don't exist)

So, it looks like my Heidi version (11.0.0.5919 (64 Bit) is not compatible with Postgres 8.4 - is there a way I can find out which version I should be using or is thereanother fix?

Thanks, Nick

These backwards compatibilities have their limits - it seems quite impossible to keep track of new things without breaking support for older servers. PG v8.4 has reached its end of life 6 years ago, or is it some newer patch release you are using?

I would agree with @ansgar that it is not reasonable to expect compatibility with servers as old as mine - I was just hoping to get a pointer to a document that would list compatibilities. I'm glad to say that by de-installing the latest version and re-installing HeidiSQL_10.0.0.5460, all seems well so far.

and Yes, I know we should update our PG instance - it will be done soon.

Is there a way to modify the both above queries in a minimal way, so it again works with v8 servers? I would want to avoid larger code changes, but if it's simple and does not affect newer server connections, then I'd give it a try.

I've just installed the latest version 10 (10.3.0.5771) which seems to be working correctly so its the changes from 10.3 to 11.0 that have caused the problem.

The queries run when a table is selected are very different. I've pasted one of the queries from V11.0 above - 2 queries are run in V10.3

SELECT   DISTINCT a.attname AS column_name,   a.attnum,   a.atttypid,   FORMAT_TYPE(a.atttypid, a.atttypmod) AS data_type,   
CASE a.attnotnull WHEN false THEN 'YES' ELSE 'NO' END AS IS_NULLABLE,   
com.description AS column_comment,   pg_get_expr(def.adbin, def.adrelid) AS column_default,   NULL AS character_maximum_length 
FROM pg_attribute AS a 
JOIN pg_class AS pgc ON pgc.oid = a.attrelid 
    LEFT JOIN pg_description AS com ON (pgc.oid = com.objoid AND a.attnum = com.objsubid) 
    LEFT JOIN pg_attrdef AS def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum) 
WHERE   a.attnum > 0   AND pgc.oid = a.attrelid   AND pg_table_is_visible(pgc.oid)   
    AND NOT a.attisdropped   AND pgc.relname = 'clubs' 
ORDER BY a.attnum;
SELECT "c"."conname" AS "CONSTRAINT_NAME", 
CASE "c"."contype" WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END AS "CONSTRAINT_TYPE", 
"a"."attname" AS "COLUMN_NAME" 
FROM "pg_constraint" AS "c" 
    LEFT JOIN "pg_class" "t" ON "c"."conrelid"="t"."oid" 
    LEFT JOIN "pg_attribute" "a" ON "t"."oid"="a"."attrelid" 
    LEFT JOIN "pg_namespace" "n" ON "t"."relnamespace"="n"."oid" 
WHERE c.contype IN ('p', 'u') AND "a"."attnum"=ANY("c"."conkey") AND "n"."nspname"='public' AND "t"."relname"='clubs' 
ORDER BY "a"."attnum";

A CASE based on PG version could obviously be used to select the required query but I don't know what the changes are designed to achieve - running the V10 SELECTS in V11 could break something else.

I don't have access to a PG9 installation but looking at PG documentation, pg_stat_activity also has the procpid field in PG9.0 and PG9.1 rather than pid so I would expect that Heidi V11 doesn't support PG9.0 or PG9.1 either

I would be happy to test queries on my PG 8.4 if it would help.

These changes in v11 were required to fix some bugs, especially non the working foreign key detection from v10.

Can you please tell what consequences the non working query has on the table designer? Is there some error message box or does that error just sit in the lower SQL log panel? I'm asking because that special query is just for getting foreign keys, which were anyway not detected correctly in v10.

There is an error message box.

See issue #158 for the foreign key problem.

I could probably just silence the error message box if the query fails, so it runs again on older servers, without foreign key support.

But I recall you mentioned a second one also failing - can you tell me which one that was?

I've re-installed V11.0. I cannot find what I clicked that resulted in the second problem I did copy the code in case you can grep the source

SELECT "pid", "usename", "client_addr", "datname", application_name , EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - "query_start")::INTEGER, "state", "query" FROM "pg_stat_activity";
/* ERROR:  column "pid" does not exist

I did come across a couple more issues that create error popups

The query when selecting Catalog Objects from information_schema

SELECT   refc.constraint_name,   refc.update_rule,   refc.delete_rule,   kcu.table_name,   
STRING_AGG(distinct kcu.column_name, ',') AS columns,   ccu.table_schema AS ref_schema,   ccu.table_name AS ref_table,   
STRING_AGG(distinct ccu.column_name, ',') AS ref_columns,   STRING_AGG(distinct kcu.ordinal_position::text, ',') AS ord_position 
FROM   information_schema.referential_constraints AS refc,   
information_schema.key_column_usage AS kcu,   
information_schema.constraint_column_usage AS ccu 
WHERE   refc.constraint_schema = 'information_schema'   AND refc.constraint_name = kcu.constraint_name   
    AND refc.constraint_schema = kcu.table_schema   AND ccu.constraint_name = refc.constraint_name   
    AND kcu.table_name = 'columns' 
GROUP BY   refc.constraint_name,   refc.update_rule,   refc.delete_rule,   kcu.table_name,   ccu.table_schema,   ccu.table_name 
ORDER BY   ord_position;

and clicking the 'Manage User Authentication and priviliges' icon

FLUSH PRIVILEGES;
/* ERROR:  syntax error at or near "FLUSH"
LINE 1: FLUSH PRIVILEGES

In this case V10.3 also uses FLUSH PRIVILEGES so I would just avoid using the Manage User Authentication and Priviliges function.

"I could probably just silence the error message box if the query fails "

In my case, I would stick with v10.3 - I monitor pg_log and email out alerts when we get errors and wouldn't this to happen every time I use Heidi