PROJECTION_REFRESHES

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

Tables and projections can be dropped while a query runs against them. The query continues to run, even after the drop occurs. Only when the query finishes does it notice the drop, which might cause a rollback. The same is true for refresh queries. Thus, PROJECTION_REFRESHES might report that a projection failed to be refreshed before the refresh query completes. In this case, the REFRESH_DURATION_SEC column continues to increase until the refresh query completes.

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.
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.