PROJECTION_REFRESHES

Provides information about refresh operations for projections. This table retains information about a refresh operation—whether successful or unsuccessful—until the function CLEAR_PROJECTION_REFRESHES executes, or the storage quota for the table is exceeded.

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 could 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 schema associated with the projection.

 
PROJECTION_ID

INTEGER

A unique numeric ID assigned by the Vertica catalog, which identifies the projection.

 
PROJECTION_NAME

VARCHAR

Name of the projection that is targeted for refresh. 

ANCHOR_TABLE_NAME

VARCHAR

Name of the projection's associated anchor table.

 
REFRESH_STATUS

VARCHAR

Status of the projection:

  • Queued — Indicates that a projection is queued for refresh.
  • Refreshing — Indicates that a refresh for a projection is in process.
  • Refreshed — Indicates that a refresh for a projection has successfully completed.
  • Failed — Indicates that a refresh for a projection did not successfully complete.
 
REFRESH_PHASE

VARCHAR

Indicates how far the refresh has progressed:

  • Historical – Indicates that the refresh has reached the first phase and is refreshing data from historical data. This refresh phase requires the most amount of time.
  • Current – Indicates that the refresh has reached the final phase and is attempting to refresh data from the current epoch. To complete this phase, refresh must be able to obtain a lock on the table. If the table is locked by some other transaction, refresh is put on hold until that transaction completes.

The LOCKS system table is useful for determining if a refresh has been 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 one or more other transactions have acquired a lock on the table.

Note: The REFRESH_PHASE 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 – Uses the contents of a buddy to refresh the projection. This method maintains historical data. This enables the projection to be used for historical queries.
  • Scratch – Refreshes the projection without using a buddy. This method does not generate historical data. This means that the projection cannot participate in historical queries from any point before the projection was refreshed.
  • Rebalance – If the projection is segmented it is refreshed from scratch; if unsegmented it is refreshed from buddy. 
REFRESH_FAILURE_COUNT

INTEGER

Number of times a refresh failed for the projection. FAILURE_COUNT does not indicate whether the projection was eventually refreshed. See REFRESH_STATUS to determine how 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 (provided as a timestamp).

 
REFRESH_DURATION_SEC

INTERVAL

SECOND (0)

Length of time that the projection refresh ran in seconds.

 
IS_EXECUTING

BOOLEAN

Distinguishes between active (t) and completed (f) refresh operations.

 
RUNTIME_PRIORITY

VARCHAR

Determines the amount of run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool. Valid values are:

  • HIGH
  • MEDIUM
  • LOW
 
transaction_id
 Int

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

Note: 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

No explicit privileges are required. You only see the records for tables that you have privileges to view.