VMware Smart Assurance NCM: ERROR - canceling statement due to statement timeout
book
Article ID: 345109
calendar_today
Updated On:
Products
VMware Smart Assurance
Issue/Introduction
Symptoms: 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.
Environment
VMware Smart Assurance - NCM
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 NCM 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-
1. 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. 2. Execute command "service controldb restart" for the changes to be taken into effect or refer KB article https://kb.vmware.com/s/article/491405?lang=en_US for instructions on reloading postgresql.conf file without database restart.
Additional Information
Impact/Risks: 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.