Redshift Postgress with Heidi

Hi,

Is there any updates as to when Redshift Postgres would be supported in Heidi?

PostgreSQL is already supported by HeidiSQL. I never heard of Redshift Postgres. Is it a drop-in-replacement? Is HeidiSQL incompatible to it?

our DB is hosted via AWS Redshift,

i am able to add the connection and shows the databases/schema's but when i select it i get the following error.

Documentation says PG on Amazon Redshift supports search_path.

Probably the escaping using E prefix is just not supported. Could you try to fire that SET.. command without these Es in a query tab?

I tried that and i was able to get the query working, however i still cant browse table objects?

is there a way of fixing that?

I just pushed some changes for issue #62, which probably also helps here. At least if you say that query works without the E prefix. I removed this prefix, using a less error-prone approach for string escaping. So, the next build should have this slightly modified query:

SET search_path TO 'phoenix', '$user';

Please update HeidiSQL to the latest build and report back if that works now.

So i don't need to update the search path for the query anymore, but i am still unable to browse object tables.

I can see an error in your SQL log at the bottom saying

ERROR: function pg_relation_size(text) does not exist

I suppose that's the next issue here. Due to the complex solution here, you could be so nice and file an issue report in the bugtracker, please.

It seems this Redshift PostgreSQL is based on a very old PostgreSQL release, as the function pg_relation_size() was introduced in PG 8.1, back in 2005. Previous v8.0 versions (without support for pg_relation_size()) get no support since 2010.

Server release lifecycles and support: https://www.postgresql.org/support/versioning/

What does this command return on your server:

SELECT VERSION();

My Redshift return PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.10936

Ok, I guess this is then the compile date: 20041017 - I suppose you need to update.

I think that 20041017 is for gcc, not Redshift. I know that our Redshift is up to date.

Amazon Redshift and PostgreSQL: docs . aws . amazon.com / redshift/latest/dg/c_redshift-and-postgres-sql . html

Indeed, that date was for the compiler.

However, PG 8.0.2 is from 2005. I wonder why Amazon does not update Redshift then, just for the benefit of bugfixes.

Perhaps there is a backward compatible alternative to pg_relation_size()? Or, if there is none, I can probably make a version conditional for old PG versions, which just selects "0" instead. This will lead to zero index length displayed in the database tab, but at least you could browse your tables.

Found an alternative on https://wiki.postgresql.org/wiki/Disk_Usage

Just pushed that into HeidiSQL, so you could update to the latest build and try again in a few minutes.

I can see schemas but not tables. No errors.

/* Connected. Thread-ID: 16571 */
SET statement_timeout TO 30000;
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER;
/* ERROR:  pg_postmaster_start_time is an internal function */
SELECT "nspname" FROM "pg_catalog"."pg_namespace" ORDER BY "nspname";
/* Entering session "RedshiftDev" */
/* Loading file "\\csc.nycnet\mocs\mocs_user_data\Dmitriy.Burtsev\Application Data\HeidiSQL\Backups\query-tab-2019-11-06_10-11-29-536.sql" (17 B) into query tab #1 ... */
SELECT *, NULL AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"=E'dev';
/* ERROR:  type "e" does not exist */
SELECT "p"."proname", "p"."proargtypes" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"=E'dev';
/* ERROR:  type "e" does not exist */
SELECT *, NULL AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"=E'ods';
/* ERROR:  type "e" does not exist */
SELECT "p"."proname", "p"."proargtypes" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"=E'ods';
/* ERROR:  type "e" does not exist */

Well, you did not update Heidi.

To get the latest build, just click Help > Check for updates, then click "Download and install build xyz".

If your Heidi version is even not the latest release, you need to install the latest release from the download page before you can update to the latest build.

Check for updates works. Thank you,

"CREATE code" "NULL DEFAULT NULL" issue: CREATE TABLE "contract" ( "ctr_id" BIGINT NOT NULL DEFAULT "identity"(820777, 0, '1,1'::text), "doc_cd" VARCHAR(30) NOT NULL, "dept_cd" VARCHAR(30) NOT NULL, "doc_id" VARCHAR(30) NOT NULL, "doc_vers_nbr" INTEGER NOT NULL, "assoc_ctr_id" BIGINT NULL DEFAULT NULL, "intl_awd_nbr" VARCHAR(30) NULL DEFAULT NULL, "doc_rec_dt" TIMESTAMP NULL DEFAULT NULL,

It should be just "NULL"

Could you please state whether the above error is gone now?

yes it is working i can see the tables in the schema now!

but it appears to have more bugs, i cant even access the preference option. i have attached the bug report.

Oh, that crash was caused by my update of SynEdit, where I overwrote some required code.

Please update again to the latest build.

Can you please change the schema sql to

SELECT "nspname", "nspowner" FROM "pg_catalog"."pg_namespace"
WHERE "nspowner" != 1 OR "nspname" IN ('pg_catalog','information_schema')
ORDER BY "nspname";

Is there a reason? Do you get an error message due to nspowner=1?

I just pushed that WHERE clause for the next build.

Also I created ticket #802 where these details should better be posted.

"Is there a reason? Do you get an error message due to nspowner=1?" No error message

Other PostgreSQL/redshift GUI (Aginity Workbench for Redshift, dbForge Studio for PostgreSQL) filter those namespaces because only amazon engineers have full access to the internal redshift tables. We as a client have limited permission on system stuff. This is how Redshift if different from PostgreSQL. In PostgreSQL you can be a real admin and see/do everything.

Understand. So, are there any more incompatibilities in HeidiSQL with Redshift? Would be nice if we can bring this to an end soon.

External tables

-- check if svv_external_schemas view exist
SELECT TRUE WHERE EXISTS (SELECT * FROM information_schema.views WHERE table_name = 'svv_external_schemas')

-- if true show external schemas
select s.esoid, s.schemaname, CASE s.eskind WHEN 1 THEN 'Data catalog' WHEN 2 THEN 'Hive' ELSE '' END AS eskind, s.databasename,  u.usename 
FROM SVV_EXTERNAL_SCHEMAS s
JOIN pg_user u ON u.usesysid = s.esowner

-- show external tables for a particular schema
SELECT schemaname, tablename, location FROM SVV_EXTERNAL_TABLES;

Could you please write in whole sentences, so I can better understand what you want to say.

Is that an extension to normal PG versions?

There are external tables in Redshift database (foreign data in PostgreSQL). The system view 'svv_external_schemas' exist only in Redshift. If you have the same code for PostgreSQL and Redshift you may check if svv_external_schemas view exist. If not exist - we are not in Redshift. If exists - show information about external schemas and tables.

This is "nice to have" feature. Thank you.

Please file a new issue request in the bugtracker