0

We have the following scheduled query which runs every 15 mins.

    select schemaname as  schema_name,
           views.viewname view_name,
           views.definition
    from pg_catalog.pg_views views
    where views.schemaname not in ('information_schema', 'pg_catalog')

most times, the query runs within a second. But several times, it gets stuck for a long time (like 5 mins) and finally exits with an error message similar to

[2021-11-08 23:52:25] [XX000] ERROR: could not open relation with OID 73677085

We are using postgres v13 as a data warehouse. There are some queries which drop and recreate about half dozen tables, views and materialized views every hour or so. Is this putting some sort of pressure on pg_catalog tables?

Any help would be appreciated.

1 Answer 1

1

I don't think that is a performance problem, it is a locking problem.

A query on pg_views will block if there is an open transaction that redefines or drops a view. Similarly, a query on pg_matviews will block if there is an open transaction that redefines, drops or refreshes a materialized view.

1
  • awesome.. I can use this info..thanks!
    – sha
    Nov 9, 2021 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.