Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Redshift Postgress with Heidi

feverpitch's profile image feverpitch posted 2 months ago in General Permalink

Hi,

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

ansgar's profile image ansgar posted 2 months ago Permalink

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

feverpitch's profile image feverpitch posted 2 months ago Permalink

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.

1 attachment(s):
  • error
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 2 months ago Permalink

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?

feverpitch's profile image feverpitch posted 2 months ago Permalink

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?

ansgar's profile image ansgar posted 2 months ago Permalink

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.

feverpitch's profile image feverpitch posted 2 months ago Permalink

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

1 attachment(s):
  • no-browse
ansgar's profile image ansgar posted 2 months ago Permalink

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.

ansgar's profile image ansgar posted 2 months ago Permalink

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();
dburtsev's profile image dburtsev posted 1 month ago Permalink

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

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 1 month ago Permalink

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

dburtsev's profile image dburtsev posted 1 month ago Permalink

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

ansgar's profile image ansgar posted 1 month ago Permalink

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.

Code modification/commit 7eb486f from Ansgar Becker <anse@heidisql.com>, 1 month ago, revision 5740
Alternative to pg_relation_size() for pre-8.1 PG servers. See https://www.heidisql.com/forum.php?t=34635
ansgar's profile image ansgar posted 1 month ago Permalink

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.

dburtsev's profile image dburtsev posted 1 month ago Permalink

I can see schemas but not tables. No errors.

dburtsev's profile image dburtsev posted 1 month ago Permalink
/* 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 */
ansgar's profile image ansgar posted 1 month ago Permalink

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.

dburtsev's profile image dburtsev posted 1 month ago Permalink

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"

ansgar's profile image ansgar posted 1 month ago Permalink

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

feverpitch's profile image feverpitch posted 4 weeks ago Permalink

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

feverpitch's profile image feverpitch posted 4 weeks ago Permalink

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

1 attachment(s):
ansgar's profile image ansgar posted 4 weeks ago Permalink

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

Please update again to the latest build.

dburtsev's profile image dburtsev posted 4 weeks ago Permalink

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";
ansgar's profile image ansgar posted 4 weeks ago Permalink

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

Code modification/commit fe7e402 from Ansgar Becker <anse@heidisql.com>, 4 weeks ago, revision 5745
Issue #802: Redshift: modify query for retrieving accessible databases. See https://www.heidisql.com/forum.php?t=34635#p34937
ansgar's profile image ansgar posted 4 weeks ago Permalink

I just pushed that WHERE clause for the next build.

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

dburtsev's profile image dburtsev posted 4 weeks ago Permalink

"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.

ansgar's profile image ansgar posted 4 weeks ago Permalink

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

Code modification/commit 586b34c from Ansgar Becker <anse@heidisql.com>, 4 weeks ago, revision 5747
Issue #802: filter away not owned databases only on Redshift servers. See https://www.heidisql.com/forum.php?t=34635#p34946
dburtsev's profile image dburtsev posted 4 weeks ago Permalink

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;
ansgar's profile image ansgar posted 4 weeks ago Permalink

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?

dburtsev's profile image dburtsev posted 4 weeks ago Permalink

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.

ansgar's profile image ansgar posted 4 weeks ago Permalink

Please file a new issue request in the bugtracker

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