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.

Cannot retrieve public in PostgreSQL

CarloTargaryen's profile image CarloTargaryen posted 2 years ago in General Permalink

Hello, I have a PostgreSQL database created with Django that I've using for years and connecting with HeidiSQL.

Now when I connect:

/* Delimiter changed to ; */
/* Connecting to 127.0.0.1 via PostgreSQL (experimental), username -------, using password: Yes ... */
SELECT VERSION();
/* Connected. Thread-ID: 8120 */
SET statement_timeout TO 0;
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER;
SELECT "nspname" FROM "pg_catalog"."pg_namespace" ORDER BY "nspname";
/* Entering session "PostgreSQL en WSL" */

...and try to retrieve the public schema it doesn't work although in pgAdmin I still can retrieve it correctly. I can see these messages:

SET search_path TO E'public';
SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME)):: BIGINT 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'public';
/* Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. */
/* Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. */
/* Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. */
SELECT "p"."proname"
FROM "pg_catalog"."pg_namespace" AS "n"
JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid"
WHERE "n"."nspname"=E'public';

I understand that the big query starting with SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) should return all the tables but it returns nothing.

Can anybody direct me about what to consult to try to pinpoint my problem?

Thank you.

1 attachment(s):
  • heidiSQL1
CarloTargaryen's profile image CarloTargaryen posted 2 years ago Permalink

It works now. I thought that the messages:

/ Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. / / Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. / / Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. /

had something to do with not showing 'public'. I had re-configured the Session without specifying wich database to connect.

jakub_kli's profile image jakub_kli posted 1 month ago Permalink

Hey, sorry for posting in such an old thread but I've encountered very similar problem. I cannot fetch public and get exactly the same errors. However, I am able to execute a query so the problem might be only with database structure in the left hand side panel. I'm using the newest version and have tried to reinstall Heidi. I would apreciate any help on that.

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

Yes, this thread is really old. Could you please re-explain the issue and how it shows to you. I changed much of the code for PG in the meantime, so be sure to use the latest build (not release, I mean the nightly build!) for reproducing, and post a reproduction recipe here.

notHeidi's profile image notHeidi posted 3 days ago Permalink

Just add, I have the same issue. New install of PostgreSQL SELECT VERSION() --> PostgreSQL 10.11, compiled by Visual C++ build 1800, 64-bit (this being the latest version given as compatible for Windows 10) And Heidi 10.2.0.5752 (64 bit) All on Windows 10 (pro version, or whatever they call it now)

Then, when I try to create a database:

CREATE DATABASE "wx"; SELECT "nspname" FROM "pg_catalog"."pg_namespace" ORDER BY "nspname"; / Entering session "LocalPostgreSQL" / SET search_path TO 'public', '$user'; SELECT , pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || 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"='public'; / Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. / / Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. / / Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. / / Unknown datatype oid #194 for "relpartbound". Fall back to UNKNOWN. */ 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"='public';

Further, the database does not show on the left pane, but if I try to run the CREATE statement again I get an error saying it already exists!

If I then close and re-open Heidi, I see this:

/ Connecting to 127.0.0.1 via PostgreSQL (TCP/IP), username postgres, using password: Yes ... / SELECT NOW(); SELECT VERSION(); / Connected. Thread-ID: 7932 / SET statement_timeout TO 30000; SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER; SHOW ssl; SELECT "nspname" FROM "pg_catalog"."pg_namespace" ORDER BY "nspname"; SET search_path TO 'public', '$user'; / Entering session "LocalPostgreSQL" / SELECT , pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || 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"='public'; / Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. / / Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. / / Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. / / Unknown datatype oid #194 for "relpartbound". Fall back to UNKNOWN. / 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"='public'; / Access is denied */

and my new database still doesn't show, even though I still get the "database already exists" error if I try to run the CREATE command again.

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