OSP0005-2 events (PostgreSQL DB going into recovery mode)

General
1

We are currently facing OSP0005-2 events (PostgreSQL DB going into recovery mode).

From PDC analysis, we identified a few UDF-based reports and optimized the top ones causing the issue. This provided temporary relief. However, when users navigate through different paths, other reports are getting triggered and the issue reoccurs.

While optimizing all UDF reports seems to be the right long-term approach, we have a large number of such reports and it will take time. Also, this issue has only started occurring in the last couple of days.

For the short term, is there anything we can do to prevent the DB from going into recovery/failure state when such reports are triggered?

2

Hi, since you are looking for short term support, you can setup a time out option in your database,

ex: ALTER DATABASE your_database_name SET statement_timeout= ā€˜30sā€™; you can modify accordingly if any roles are specified in your report, which can further streamline this.

Note : 1. queries that hit this limit will be aborted. 2.you can change in postgresql.config file but that is not recommended as it will have a global impact.

1 Like