ERROR - canceling statement due to statement timeout

Products

• VMware Smart Assurance

Issue/Introduction

ERROR "canceling statement due to statement timeout" is thrown in NCM 10.1.x deployment during database maintenance operations such as database-utility.pl execution, VACUUM FULL, REINDEX TABLE, ANALYZE operations execution.

For example:

Cannot determine if this is a large data set or not!
Moving on with default settings...
org.postgresql.util.PSQLException: ERROR: canceling statement due to statement timeout
  Where: SQL statement "SELECT 1 FROM ONLY "voyence"."cm_task_action" x WHERE "action_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
SQL statement "DELETE FROM voyence.cm_task_action AS a USING voyence.cm_task AS b WHERE a.task_id = b.task_id AND b.job_id = job_id_p"
PL/pgSQL function dbutil.process_job_records_for_job_id(bytea,character varying) line 62 at SQL statement
SQL statement "SELECT dbutil.process_job_records_for_job_id(job_number,module_name)"
PL/pgSQL function dbutil.process_job_records_batched(integer,integer,character varying,character varying) line 15 at PERFORM

 

Environment

NCM - 10.1.x

Cause

In NCM 10.1.1 version release, the parameter 'statement_timeout' was set as 10s in postgres.conf file to invalidate session upon user logout or other session termination, as part of PostgreSQL STIG hardening reported issues & subsequent fixes (V-73037). It is documented in 10.1.1-security-configuration-guide.

Resolution

Users are recommended to disable the "statement_timeout" parameter in $VOYENCE_HOME/db/controldb/data/postgresql.conf file in NCM 10.1.x database server or combination server prior to performing database maintenance activity. Steps to disable "statement_timeout" parameter is detailed as follows:

• Open $VOYENCE_HOME/db/controldb/data/postgresql.conf file and change existing entry of "statement_timeout = 10000" to "statement_timeout = 0", save and exit the file.
• Execute command "service controldb restart" for the changes to be taken into effect or refer KB article Reload config in postgres for instructions on reloading postgresql.conf file without database restart.
  
  

Additional Information

The error is automatically thrown interrupting database query execution during maintenance activity when large number of datasets are extracted from postgres DB, exceeding the 10s duration and resulting query execution timeout.