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?
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.
For a temporary relif. We need to add the more resources at DB end , for that a CC ticket needs to raised from client end and in the ticket we need to mention to optimize the DB.
Post optimize the reports then we need to raise the another CC to revert the changes.
This change will require a brief downtime of approximately 10 minutes.
i get your concern, i have few other suggestions you can try if it is applicable in your case.
try to disable export to excel functionality if it exists for the reports, as well as “select all”
if possible set schedule (ca use Job schedulers) for the reports to run (which dont need manual intervention), can also disable “run on demand” option (if checked) this tells the system to pick up the report when DB has some overhead.
you can try setting a time out on Pega’s prconfig.xml or via a DSS (server restart would be required following this)