I cannot use older postgresql

Hi Ansgar, I have a problem with an old database. I am using postgresql 9.3 locally but the remote server has 8.3.0.

I can connect to the database. I connect the "public" schema and the tree opens with Tables, Views, etc. If I don't click the "Tables" but doubleclick it then I get an error message. If
I click the little triangle in front of the "Tables" instead then tables appear. But when I click the name of a table then I get the same error message again.

The message is:
ERROR: syntax error at or near "'public'"
LINE1: SET SCHEMA 'public'

Could you fix it, please?

Thank you so much and really appreciate your work. :)

...and I use the newest version, r4961. With (much) older versions I think it worked.

I know already why. The following syntax is supported only as of 8.4:
SET SCHEMA 'my_schema'

Before 8.4, the following syntax should be used:
SET search_path TO my_schema, public;

Please, compare the following pages for further details:
http://www.postgresql.org/docs/8.4/static/sql-set.html
http://www.postgresql.org/docs/8.3/static/sql-set.html

Thanks,

Csongor

Looks like I can fix that with a version conditional in HeidiSQL's SetDatabase method. Thanks for the hint.

Hi Ansgar, is there anything news about this?

r4968 should fix that. I'm now just using the old syntax "SET search_path TO xyz" for all versions, as "SET SCHEMA xyz" is just an alias for that newer syntax.

The schema problem disappeared but I still cannot use the database. If I click a table name then such lines appear in the bottom panel (see below). The reason is that the WITH syntax was introduced by 8.4. Can you do anything against it? I tried it with r.4970. Thanks.

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 E'YES' ELSE E'NO' END AS IS_NULLABLE, com.description AS column_comment, def.adsrc 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 = E'users' ORDER BY a.attnum;
WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class WHERE pg_class.relname = E'users' AND pg_class.oid = pg_index.indrelid ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
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 E'YES' ELSE E'NO' END AS IS_NULLABLE, com.description AS column_comment, def.adsrc 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 = E'users' ORDER BY a.attnum;
WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class WHERE pg_class.relname = E'users' AND pg_class.oid = pg_index.indrelid ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
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 E'YES' ELSE E'NO' END AS IS_NULLABLE, com.description AS column_comment, def.adsrc 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 = E'users' ORDER BY a.attnum;
WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class WHERE pg_class.relname = E'users' AND pg_class.oid = pg_index.indrelid ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
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 E'YES' ELSE E'NO' END AS IS_NULLABLE, com.description AS column_comment, def.adsrc 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 = E'users' ORDER BY a.attnum;
WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class WHERE pg_class.relname = E'users' AND pg_class.oid = pg_index.indrelid ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
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 E'YES' ELSE E'NO' END AS IS_NULLABLE, com.description AS column_comment, def.adsrc 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 = E'users' ORDER BY a.attnum;
WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class WHERE pg_class.relname = E'users' AND pg_class.oid = pg_index.indrelid ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
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 E'YES' ELSE E'NO' END AS IS_NULLABLE, com.description AS column_comment, def.adsrc 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 = E'users' ORDER BY a.attnum;
WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class WHERE pg_class.relname = E'users' AND pg_class.oid = pg_index.indrelid ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */
/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */

Ok, then that's another issue now.

The "WITH ndx_list" query was proposed by a user in another thread for detecting indexes in a table. I do not have a solution for older PG servers, I even didn't know that this would not work. Probably you find some alternative for older servers, or ask somewhere else?

The 'WITH' clauses can (to some degree) be replicated with nested SELECTs. They're harder to read and maintain (IMO), and I'm not sure how well Postgres optimizes the same SELECT appearing twice in a statement...

That said, here's the plain SQL equivalent for the CTE version of the query to list the indices for a Postgres table:

SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME
FROM pg_attribute a
JOIN (
SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx,
CASE i.indisprimary WHEN TRUE
THEN E'PRIMARY'
ELSE
CASE i.indisunique WHEN TRUE
THEN E'UNIQUE' ELSE E'KEY'
END
END AS CONSTRAINT_TYPE, pg_class.oid
FROM pg_class
JOIN pg_index i ON (pg_class.oid = i.indexrelid)
JOIN (
SELECT pg_index.indexrelid, pg_class.oid
FROM pg_index, pg_class
WHERE pg_class.relname = E'TABLE_NAME_HERE' AND pg_class.oid = pg_index.indrelid
) AS ndx_list ON (pg_class.oid = ndx_list.indexrelid)
) AS ndx_cols ON (a.attnum = ndx_cols.col_ndx)
JOIN (SELECT pg_index.indexrelid, pg_class.oid
FROM pg_index, pg_class
WHERE pg_class.relname = E'TABLE_NAME_HERE' AND pg_class.oid = pg_index.indrelid
) AS ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);

This is equivalent to the one currently being used in Heidi. If you're already doing a version check to send <= 8.3 special queries, this would work. (Or you could use it for all versions - it still works on 9.4.x)


I don't know if I should list this in a separate forum topic, but these queries do return the name of the index in Postgres, but Heidi does not correctly display it in the Table > Indexes tabs. (It shows the index name as blank.) Columns in the index are listed in the right order though, which is the original bug that got me interested in this.

Separate request:

Heidi does a 'SET search_path TO my_schema' whenever I click / change to my_schema. Unfortunately, if my_schema is not named 'public', then I lose access to all functions defined in the public schema (which is usually where e.g. hstore and json functions are defined), causing some queries to fail that rely on those functions.

I propose a better alternative to be 'SET search_path TO my_schema, public' any time 'my_schema' != 'public'. I think this would be more normative Postgres behavior, since all uses in a Postgres database have access to the public schema by default anyway.

Sounds reasonable, for the problem you're describing. But that would suddenly show tables, functions and everything else from the public schema in all other schemata, right?

I think it would, but it looks like you're already qualifying your queries for schema information with the schema name.

e.g. the one to get table sizes that begins with "SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA)..." already ends with "...WHERE t."table_schema"=E'schema_name';" so those will continue to work correctly. I tested that query preceded by one that sets the schema path to what I would want and it worked as I would want it to.

I'm not sure which queries you're using to get the function list, etc., but if you're doing something similar it should work correctly.

Right... I'll give that a try.

Done in r4978

Hi, still getting this message from a 8.3 server:

/* ERROR: syntax error at or near "WITH ndx_list"
LINE 1: WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.o...
^ */

Using r5005.

I guess the old query from r4909 does not work on v8.3 either?

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"='Schema' 
AND "t"."relname"='Name' 
ORDER BY "a"."attnum"

Probably it does run, then we might still run into problems, detecting the right order of columns in a key. But this is better than nothing I guess.

You could give r4909 a try and report back what you think.

I've downloaded r4909 and I got the error message "syntax error at or near 'public' LINE 1: SET SCHEMA 'public'" on the session dialog, but everything seemed to work fine after.

I guess the old query from r4909 does not work on v8.3 either?

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"='Schema' 
AND "t"."relname"='Name' 
ORDER BY "a"."attnum"

Probably it does run, then we might still run into problems, detecting the right order of columns in a key. But this is better than nothing I guess.

You could give r4909 a try and report back what you think.

The query runs fine, replacing 'schema' for 'public' and 'name' for 'existing table' returns 1 row with, I might guess, is the expected result.

Sorry, that 'Schema' and 'Name' value should have contained schema and table name.

But I just see the replacement query from jamesvl, in the 9th post above:

SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME
FROM pg_attribute a
JOIN (
SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx,
CASE i.indisprimary
WHEN TRUE THEN E'PRIMARY'
ELSE
CASE i.indisunique
WHEN TRUE THEN E'UNIQUE'
ELSE E'KEY'
END
END AS CONSTRAINT_TYPE, pg_class.oid
FROM pg_class
JOIN pg_index i ON (pg_class.oid = i.indexrelid)
JOIN (
SELECT pg_index.indexrelid, pg_class.oid
FROM pg_index, pg_class
WHERE pg_class.relname = E'TABLE_NAME_HERE' AND pg_class.oid = pg_index.indrelid
) AS ndx_list ON (pg_class.oid = ndx_list.indexrelid)
) AS ndx_cols ON (a.attnum = ndx_cols.col_ndx)
JOIN (SELECT pg_index.indexrelid, pg_class.oid
FROM pg_index, pg_class
WHERE pg_class.relname = E'TABLE_NAME_HERE' AND pg_class.oid = pg_index.indrelid
) AS ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);

I only wonder where I have to put the schema name. However, if that works, we don't even need a version conditional for old servers.

Could you please verify whether that query works on v8.3?

No, that one doesn't work:

ERROR:  function unnest(int2vector) does not exist
LINE 1: ...OM pg_attribute a JOIN ( SELECT pg_class.relname, UNNEST(i.i...
^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

UNNEST doesn't exist on 8.3

Ok, then I should probably just use the version conditional approach and the query from r4909 for server version below 9.0

Done in r5008

r5008 working fine on PostgreSQL 8.3