Refresh Job Takes Too Long After Adding a New ColumnProperties Action

Issue

After adding a ColumnProperties action with the expression {hvr_integ_tstamp} at time zone 'America/Chicago', refresh jobs take much longer than expected. For example, a table refresh that previously took 2 minutes may take several hours.

Environment

• HVR version: 6.1.0, 6.2.0, or 6.3.0
• Source: Any RDBMS
• Target: PostgreSQL

Resolution

To resolve this issue, add the following entries to the PostgreSQL section of $HVR_HOME/etc/constsqlexpr.pat:

• current_timestamp(3) at time zone 'America/Chicago'
• current_timestamp(3) at time zone 'America/Chicago' '*'

For example, update this section:

# PostgreSQL based (e.g. Greenplum)
now()
now() at time zone '*'
now() at time zone ('*')
getdate() at time zone '*'
getdate() at time zone current_setting('timezone') at time zone '*'
localtimestamp([0-9]*)

To:

# PostgreSQL based (e.g. Greenplum)
now()
now() at time zone '*'
now() at time zone ('*')
current_timestamp(3) at time zone 'America/Chicago'
current_timestamp(3) at time zone 'America/Chicago' '*'
getdate() at time zone '*'
getdate() at time zone current_setting('timezone') at time zone '*'
localtimestamp([0-9]*)

Cause

This issue occurs when an integrate expression does not match a pattern in $HVR_HOME/etc/constsqlexpr.pat. When this happens, the refresh job uses row-by-row execution instead of a single bulk operation for the table.