5 Replies Latest reply: Jan 8, 2010 4:24 AM by Heiko Rupp RSS

PostgreSQL ERROR: canceling statement due to statement timeout

Richard Stephan Newbie
Within the PostgreSQL database that represents the repository for the JON the statement_timeout has been set to 30 seconds as recommended by the installation documentation.  However, recently an hourly maintenance job has been generating an ERROR in the database.

ERROR:  canceling statement due to statement timeout

STATEMENT:      INSERT INTO RHQ_MEASUREMENT_BLINE ( id, BL_MIN, BL_MAX, BL_MEAN, BL_COMPUTE_TIME, SCHEDULE_ID )          SELECT nextval('RHQ_MEASUREMENT_BLINE_ID_SEQ'),                 MIN(data1h.minvalue) AS bline_min,                 MAX(data1h.maxvalue) AS bline_max,                 AVG(data1h.value) AS bline_mean,                 $1 AS bline_ts,                 data1h.SCHEDULE_ID AS bline_sched_id            FROM RHQ_MEASUREMENT_DATA_NUM_1H data1h       INNER JOIN RHQ_MEASUREMENT_SCHED sched               ON data1h.SCHEDULE_ID = sched.id       INNER JOIN RHQ_MEASUREMENT_DEF def              ON sched.definition = def.id LEFT OUTER JOIN RHQ_MEASUREMENT_BLINE bline              ON sched.id = bline.SCHEDULE_ID            WHERE ( def.numeric_type = 0 )             AND ( bline.id IS NULL )             AND ( data1h.TIME_STAMP BETWEEN $2 AND $3 )        GROUP BY data1h.SCHEDULE_ID          HAVING data1h.SCHEDULE_ID in ( SELECT mdata.SCHEDULE_ID                                           FROM RHQ_MEASUREMENT_DATA_NUM_1H mdata                                           WHERE mdata.TIME_STAMP <= $4 )           LIMIT 100000

I have changed the statement_timeout to 120 seconds to stop this error from occurring.  Is there a better way to handle this?