REFRESH

Synchronously refreshes one or more table projections in the foreground, and updates system table PROJECTION_REFRESHES. If you run REFRESH with no arguments, it refreshes all projections that contain stale data.

To understand projection refresh in detail, go to Refreshing Projections.

Syntax

REFRESH ( [ '[[database.]schema.]table‑name[,…]' ] )

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
table‑name

The anchor table of the projections to refresh. If you specify multiple tables, REFRESH attempts to refresh them in parallel. Such calls are part of the Database Designer deployment (and deployment script).

Returns

If REFRESH does not refresh any projections, it returns a header string with no results.

Column… Returns…
Projection Name

The projection targeted for refresh.
Anchor Table The projection's associated anchor table.
Status Projections' refresh status:
  • queued: Queued for refresh.
  • refreshing: Refresh is in process.
  • refreshed: Refresh successfully completed.
  • failed: Refresh did not successfully complete.
Refresh Method Method used to refresh the projection.
Error Count Number of times a refresh failed for the projection.
Duration (sec) How long (in seconds) the projection refresh ran.

Privileges

Refresh Methods

Vertica can refresh a projection from one of its buddies, if one is available. In this case, the target projection gets the source buddy's historical data. Otherwise, the projection is refreshed from scratch with data of the latest epoch at the time of the refresh operation. In this case, the projection cannot participate in historical queries on any epoch that precedes the refresh operation.

To determine the method used to refresh a given projection, query REFRESH_METHOD from system table PROJECTION_REFRESHES.

Examples

The following example refreshes the projections in tables t1 and t2:

=> SELECT REFRESH('t1, t2');
                                             REFRESH
----------------------------------------------------------------------------------------
Refresh completed with the following outcomes:

Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------

"public"."t1_p": [t1] [refreshed] [scratch] [0] [0]"public"."t2_p": [t2] [refreshed] [scratch] [0] [0]

This next example shows that only the projection on table t was refreshed:

=> SELECT REFRESH('allow, public.deny, t');
                                               REFRESH
----------------------------------------------------------------------------------------

Refresh completed with the following outcomes:

Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"n/a"."n/a": [n/a] [failed: insufficient permissions on table "allow"] [] [1] [0]
"n/a"."n/a": [n/a] [failed: insufficient permissions on table "public.deny"] [] [1] [0]
"public"."t_p1": [t] [refreshed] [scratch] [0] [0]

See Also