PostgreSQL - Database Configuration Issues - OPS0009 ALERT

Pega-as-a-Service
, , , , , , ,
1

Issue Description:

[MSG][Error in RDB-Delete][STACK][com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: 42P01 Message: ERROR: relation “pg_stat_statements” does not exist
Position: 48
DatabaseException caused by prior exception: org.postgresql.util.PSQLException: ERROR: relation “pg_stat_statements” does not exist
Position: 48
| SQL Code: 0 | SQL State: 42P01

From: (BHFFYJ9XUVGOVHIY4E50MIRFNLIS1W294A)
SQL: SELECT count(pg_stat_statements_reset() ) from pg_stat_statements limit 1

Caused by SQL Problems.
Problem 1, SQLState 42P01, Error code 0: org.postgresql.util.PSQLException: ERROR: relation “pg_stat_statements” does not exist

2

@SaikishoreS16598123

Root Cause: The issue occurs sometimes during the upgrades.

you have to specify the schema of pg_stat_statements and validate the result with below query.

For example: SELECT count(public.pg_stat_statements_reset() ) from public.pg_stat_statements limit 1;

Solution: In-order to fix the issue,

  1. We need to validate the relation of existing schemas has relation to public level from the Search_path is pointed to correct schemas or not.

  2. Need to check with DBA if pg_stat_statements is enabled in the environment or not. If not, make sure it gets enabled by DBA.

  3. Last step: Identify the Operator ID configured in DB to access the application and excute the below query to alter the role

ALTER ROLE admin SET search_path TO pg_catalog,public,,public;

Note: Operator ID is admin in our scenario.

For More Reference: https://www.postgresql.org/docs/11/pgstatstatements.html

Note: The above solution is applied in our environment and the issue has been resolved successfully. The Post will be kept open for any other best solutions and comments.