Error: postgres: column "oid" was not found for index "pg_subscription_oid_index"

[rinshadka]

Hello,

while using altas against GitLab Omnibus PostgreSQL service which is custom , I am getting this issue.

atlas schema inspect -u "postgres://gitlab:xxxx@10.10.10.171:5455/gitlabhq_production?sslmode=disable" --format '{{ sql . }}' > dev.sql
Error: referenced schema "pg_catalog" was not found in realm

Then I have added pg_catalog too in search path ,

╰─ $ atlas schema inspect -u "postgres://gitlab:xxxx@10.10.10.171:5455/gitlabhq_production?sslmode=disable" -s pg_catalog,public --format '{{ sql . }}' > dev.sql
Error: postgres: column "oid" was not found for index "pg_subscription_oid_index"

any thoughts,

Thank you.

[timbunce]

I'm a new user just starting out with atlas and have encountered the same problem:

$ atlas schema inspect -u postgres://xxx@xxx:5305/foo_dev
Error: referenced schema "pg_catalog" was not found in realm

I can access items in the pg_catalog schema when I connect with the same credentials. The postgres server version is 13.6. I installed atlas via homebrew: atlas version v0.17.1-75ae281-canary I have no atlas config file.

If I change the db name in the url from /foo_dev to /postgres then the command produces output for some table in that db. But that's not the db I want to inspect.

[a8m]

Thanks for reporting this, @timbunce and @rinshadka.

To debug this, can you share what PostgreSQL server are you using? If it's a Docker container, a reference to the Docker image will help.

[timbunce]

The postgres server version is 13.6 in my case.

[timbunce]

Is there a way to enable extra debug/logging/tracing?

[timbunce]

I've reproduced it using the official postgres:13.6-alpine docker image. I enabled statement logging and last statement executed before the error is:

2024-01-04 13:30:15.626 UTC [4897] LOG:  execute <unnamed>:
    SELECT DISTINCT
        parent_ns.nspname AS view_schema,
        parent_view.relname AS view_name,
        parent_view.relkind,
        child_ns.nspname AS referenced_schema,
        child_view.relname AS referenced_name,
        child_view.relkind
    FROM
        pg_catalog.pg_depend
        JOIN pg_catalog.pg_rewrite ON pg_rewrite.oid = pg_depend.objid
        JOIN pg_catalog.pg_class parent_view ON parent_view.oid = pg_rewrite.ev_class
        LEFT JOIN pg_catalog.pg_depend parent_dep ON parent_dep.objid = parent_view.oid AND parent_dep.deptype = 'e'
        JOIN pg_catalog.pg_class child_view ON child_view.oid = pg_depend.refobjid
        JOIN pg_catalog.pg_namespace parent_ns ON parent_ns.oid = parent_view.relnamespace
        JOIN pg_catalog.pg_namespace child_ns ON child_ns.oid = child_view.relnamespace
    WHERE
        parent_view.relkind IN ('v', 'm')
        AND parent_view.oid != child_view.oid
        AND parent_ns.nspname IN ($1)
        AND parent_dep.objid IS NULL

2024-01-04 13:30:15.626 UTC [4897] DETAIL:  parameters: $1 = 'public'

If I run that command manually with public as $1 I get this:

 view_schema |    view_name    | relkind | referenced_schema | referenced_name  | relkind
-------------+-----------------+---------+-------------------+------------------+---------
 public      | v_activity      | v       | pg_catalog        | pg_stat_activity | v
 public      | v_blocked_locks | v       | pg_catalog        | pg_locks         | v
 public      | v_blocked_locks | v       | pg_catalog        | pg_stat_activity | v
 public      | v_config        | v       | public            | bucket           | r
 public      | v_config        | v       | public            | config           | r
 public      | v_config        | v       | public            | v_property       | v
 public      | v_property      | v       | public            | bucket           | r
 public      | v_property      | v       | public            | config           | r
 public      | v_property      | v       | public            | property         | r

I suspected the trigger is having a view that refers to an entity in pg_catalog. So I deleted the v_activity and v_blocked_locks views and now the atlas schema inspect works.

[a8m]

Thanks for investigating this, @timbunce. This was already fixed in the "new way" we use for inspecting dependencies (which should be the default at some point). You can get access to it using the atlas login - let me know if this solves the issue.