REFRESH
Performs a synchronous, optionally-targeted refresh of a specified table's projections.
Information about a refresh operation—whether successful or unsuccessful—is maintained in system table PROJECTION_REFRESHES
until the function CLEAR_PROJECTION_REFRESHES
executes or the storage quota for the table is exceeded. The table's IS_EXECUTING
column returns a boolean value that indicates whether the refresh is running now (t) or occurred earlier (f).
Syntax
REFRESH ( '[schema.]table‑name[,...]' )
Parameters
schema |
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject |
table‑name |
The anchor table of the projections to refresh. If you specify multiple tables, |
Returns
Column Name | Description |
---|---|
Projection Name
|
The projection targeted for refresh. |
Anchor Table
|
The projection's associated anchor table. |
Status
|
Status of the projection:
|
Refresh Method
|
Method used to refresh the projection:
|
Error Count
|
Number of times a refresh failed for the projection. |
Duration (sec)
|
Length of time that the projection refresh ran in seconds. |
Privileges
- Superuser
- Owner of the specified tables
Notes
- Unlike START_REFRESH which runs in the background, REFRESH() runs in the foreground of the caller's session.
- If you run REFRESH() without arguments, it refreshes all non up-to-date projections. If the function returns a header string with no results, then no projections needed refreshing.
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]