Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

PostgreSQL COALESCE function used in automation triggers is used inaccurately

Description

When we first implemented the triggers that evaluated lastautomationtime and lasteventtime, the PostgreSQL versions we supported didn't include the GREATEST function so we used the next best thing which was COALESCE which favored lastautomationtime. However, since 8.1, PostgreSQL has supported the GREATEST function and it should be used as it would be more accurate to evaluate that most recent date of the two values:

 Example:

<trigger name="selectAlarmsToFullGarbageCollect" operator=">=" row-count="1" > <statement> SELECT alarmid, eventuei, reductionkey, now() AS _ts FROM alarms WHERE COALESCE(lastautomationtime, lasteventtime) < now() - interval '8 days' </statement> </trigger>

Should be:

<trigger name="selectAlarmsToFullGarbageCollect" operator=">=" row-count="1" > <statement> SELECT alarmid, eventuei, reductionkey, now() AS _ts FROM alarms WHERE GREATEST(lastautomationtime, lasteventtime) < now() - interval '8 days' </statement> </trigger> </code>

Environment

PostgreSQL 8.1 or higher

Acceptance / Success Criteria

None

Lucidchart Diagrams

Activity

Show:

Details

Assignee

Reporter

Labels

Components

Priority

PagerDuty

Created February 2, 2018 at 1:52 PM
Updated September 21, 2021 at 7:25 PM