PROJECTION_REFRESHES

System table PROJECTION_REFRESHES records information about refresh operations, successful and unsuccessful. PROJECTION_REFRESHES retains projection refresh data until one of the following events occurs:

  • Another refresh operation starts on a given projection.
  • CLEAR_PROJECTION_REFRESHES is called and clears data on all projections.
  • The table's storage quota is exceeded.
Column Name Data Type Description
NODE_NAME VARCHAR Node where the refresh was initiated.
PROJECTION_SCHEMA VARCHAR Name of the projection schema.
PROJECTION_ID INTEGER

Catalog-assigned numeric value that uniquely identifies the projection.

PROJECTION_NAME VARCHAR Name of the refreshed projection.
ANCHOR_TABLE_NAME VARCHAR Name of the projection's anchor table.
REFRESH_STATUS VARCHAR

Status of refresh operations for this projection, one of the following:

  • Queued : Projection is queued for refresh.
  • Refreshing: Projection refresh is in progress.
  • Refreshed: Projection refresh is complete.
  • Failed: Projection refresh failed.
PERCENT_COMPLETE VARCHAR Shows the current percentage of completion for the refresh operation. When the refresh is complete, the column is set to NULL.
REFRESH_PHASE VARCHAR

Indicates how far the refresh has progressed:

  • Historical: Refresh reached the first phase and is refreshing data from historical data. This refresh phase requires the most amount of time.
  • Current: Refresh reached the final phase and is attempting to refresh data from the current epoch. To complete this phase, refresh must obtain a lock on the table. If the table is locked by another transaction, refresh is blocked until that transaction completes.

The LOCKS system table is useful for determining if a refresh is blocked on a table lock. To determine if a refresh has been blocked, locate the term "refresh" in the transaction description. A refresh has been blocked when the scope for the refresh is REQUESTED and other transactions acquired a lock on the table.

This field is NULL until the projection starts to refresh and is NULL after the refresh completes.

REFRESH_METHOD VARCHAR

Method used to refresh the projection:

  • Buddy: Projection refreshed from the contents of a buddy projection. This method maintains historical data, so the projection can used for historical queries.
  • Scratch: Projection refreshed without using a buddy projection. This method does not generate historical data, so the projection cannot participate in historical queries on data that precedes the refresh.
  • Rebalance: If the projection is segmented, it is refreshed from scratch; if unsegmented, it is refreshed from a buddy projection.
REFRESH_FAILURE_COUNT INTEGER Number of times a refresh failed for the projection. REFRESH_FAILURE_COUNT does not indicate whether the projection was eventually refreshed. See REFRESH_STATUS to determine whether the refresh operation is progressing.
SESSION_ID VARCHAR Unique numeric ID assigned by the Vertica catalog, which identifies the refresh session.
REFRESH_START TIMESTAMPTZ Time the projection refresh started.
REFRESH_DURATION_SEC INTERVAL SECOND (0) How many seconds the projection refresh ran.
IS_EXECUTING BOOLEAN Differentiates active and completed refresh operations.
RUNTIME_PRIORITY VARCHAR

Determines how many run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool, one of the following:

  • HIGH
  • MEDIUM
  • LOW
TRANSACTION_ID INTEGER

Identifier for the transaction within the session, if any; otherwise NULL.

The transaction_id is correlated with the execution plan only when refreshing from scratch. When refreshing from a buddy, multiple sub-transactions are created

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.