distal-attribute
distal-attribute
distal-attribute
distal-attribute

Cannot retrieve public in PostgreSQL

CarloTargaryen posted 2 months ago in General

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 posted 2 months ago

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.

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