postgres introspection error

[aNull404]

Error:
internal error: relation "utl_file_dir" does not exist

---

VSCode Version: 1.105.1
Extension Version: 1.32.0
OS: win32
Arch: x64
Database Version: PostgreSQL 11.3 (Release-build@0eb93d45fd0 on release-4.0.x) on x86_64-linux, compiled by clang version 17.0.6 (Alibaba Cloud Compiler 17.0.6.5-25.05.29.alios7) - 11.3

[mikeburgh]

Can you run the following statements and let me know which ones fail (I am thinking the first one should and others wont).

SELECT sequencename FROM pg_sequences LIMIT 1;

SELECT count(*) FROM pg_catalog.pg_class LIMIT 1;

SELECT relname FROM pg_catalog.pg_class WHERE relkind = 'S' LIMIT 1;

[aNull404]

Can you run the following statements and let me know which ones fail (I am thinking the first one should and others wont).

SELECT sequencename FROM pg_sequences LIMIT 1;

SELECT count(*) FROM pg_catalog.pg_class LIMIT 1;

SELECT relname FROM pg_catalog.pg_class WHERE relkind = 'S' LIMIT 1;

1. chat_model_info_id_seq
2. 43492
3. chat_model_info_id_seq

[mikeburgh]

Thanks, that's useful. It rules out sequences as the cause, so the utl_file_dir reference is coming from somewhere else.

DBCode never references utl_file_dirdirectly, so on your compatible build it must be baked into some view or function definition that we read while introspecting. Can you run these two and tell me what comes back?

-- functions/procedures whose body references utl_file_dir
SELECT n.nspname AS schema, p.proname AS name, 'function' AS kind
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosrc ILIKE '%utl_file_dir%';

-- views whose definition references utl_file_dir
SELECT schemaname, viewname, 'view' AS kind
FROM pg_catalog.pg_views
WHERE definition ILIKE '%utl_file_dir%';

That should point straight at the object that's pulling it in.

One more thing that'll help narrow it down: when does the error actually show up? On connecting, on expanding the database, or on expanding a particular schema? If it's tied to a specific schema, let me know which one.

[aNull404]

1. Both queries returned empty results.
2. The error occurred when expanding the database.
3. The database I’m connected to is Hologres, which is fully compatible with PostgreSQL network protocol and standard SQL syntax.

[mikeburgh]

Thanks, my is blocked for some reason (Your account is flagged for security risks. Login access is restricted by Alibaba Cloud. Contact support for resolution.), and I cannot contact support without logging in.. fun times… guessing they do not like me just spinning up and closing db's when testing there options while I see if I can get it unblocked to test.

Can you try these statements:

  -- 1. foreign servers
  SELECT s.oid, s.srvname, fdw.fdwname, s.srvtype, s.srvversion, s.srvoptions,
         obj_description(s.oid, 'pg_foreign_server')
  FROM pg_catalog.pg_foreign_server s
  JOIN pg_catalog.pg_foreign_data_wrapper fdw ON fdw.oid = s.srvfdw
  ORDER BY s.srvname;

  -- 2. foreign tables
  SELECT ft.ftrelid, n.nspname, c.relname, ft.ftserver, fs.srvname, ft.ftoptions
  FROM pg_catalog.pg_foreign_table ft
  JOIN pg_catalog.pg_class c ON ft.ftrelid = c.oid
  JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
  JOIN pg_catalog.pg_foreign_server fs ON ft.ftserver = fs.oid
  ORDER BY n.nspname, c.relname;

  -- 3. types
  SELECT t.oid, n.nspname, t.typname, t.typlen
  FROM pg_type t
  JOIN pg_namespace n ON n.oid = t.typnamespace;

  -- 4. schemas
  SELECT oid, nspname FROM pg_catalog.pg_namespace
  WHERE nspname !~ '^pg_toast' AND nspname !~ '^pg_temp_'
  ORDER BY nspname;

[aNull404]

3. The data volume is too large to export.

oid	nspname
22153	dbms_alert
22218	dbms_assert
21974	dbms_output
22040	dbms_pipe
22246	dbms_random
22172	dbms_utility
22529	hg_internal
22531	hg_presto_funcs
22934	hg_recyclebin
22932	hologres
22973	hologres_object_table
22974	hologres_sample
22976	hologres_statistic
24608	hologres_streaming_mv
13097	information_schema
21932	oracle
11	pg_catalog
22227	plunit
22131	plvchr
22073	plvdate
22175	plvlex
21927	plvstr
22163	plvsubst
2200	public
22177	utl_file

[mikeburgh]

Thanks, and sorry for the delay, still trying to get my cloud account unblocked so I can debug this myself.. but not getting very far..

Can you run this query and let me know if it throws the error:

select t.oid as type_id, nspname, typname, typlen,
       (select coalesce(to_json(array_agg(ev)),'null'::json) as "values"
        from (select 'value' as type, oid as id, enumlabel as name
              from pg_enum where enumtypid = t.oid order by enumsortorder) ev)
from pg_type t
join pg_namespace n on n.oid = t.typnamespace;