Adding Custom Projections

You can use the CREATE PROJECTION command to add a projection to a database when one or more of the tables in the projection query already contains data. However, the new projection is

out-of-date (not available for query processing) until you start a refresh operation. The

START_REFRESH function described in the SQL Reference Manual copies data into the new projection from other projections until it becomes

up-to-date.

A projection is up-to-date if it does not require a refresh in order to participate in query execution.

START_REFRESH transfers data to projections that are not able to participate in query execution due to missing or out-of-date data.

Syntax

START_REFRESH()

Notes

All nodes must be up in order to start a refresh.
A refresh can start when:
- a new projection is created for tables that already have data
- recovery has completed
- the MARK_DESIGN_KSAFE function is called
If a refresh is already running the function has no effect.
Shutting down the database ends the refresh.
To monitor the refresh operation, examine the log files on each node.

A projection is out-of-date if it requires a refresh in order to participate in query execution.

Before starting a refresh, make sure that the projection has a sufficient number of buddy projections (call

MARK_DESIGN_KSAFE) and that all nodes are up .

Use MARK_DESIGN_KSAFE to enable or disable automatic recovery in case of a failure. Before enabling automatic recovery, MARK_DESIGN_KSAFE queries the catalog to determine whether or not a cluster's physical schema design meets the following requirements:

Dimension tables are replicated on all nodes.
Fact table superprojections are segmented with each segment on a different node.
Each fact table projection has at least one "buddy" projection.
Each segment of each fact table projection exists on two nodes.
Two projections are considered to be buddies if they contain the same columns and have the same segmentation. They can have different sort orders.

MARK_DESIGN_KSAFE does not change the physical schema in any way.

Syntax

SELECT MARK_DESIGN_KSAFE(k)

Semantics

k	1 enables automatic recovery if the schema design meets requirements 0 disables automatic recovery

If you specify a k value of one (1), Vertica returns one of the following messages:

Marked design 1-safe

The schema does not meet requirements for K=1.

Fact table projection projection-name

has insufficient "buddy" projections..

The database's internal automatic recovery state persists across database restarts but is not checked at startup time.

To illustrate, consider the following sequence of events:

Your database has automatic recovery enabled.
You drop a table (and corresponding projections) in a running database.
You forget to MARK_DESIGN_KSAFE(0).
You shut down the database.
You start the database with automatic recovery enabled.
Recovery fails because the physical schema design no longer meets requirements.

Notes

In a newly-created database, automatic recovery is disabled by default.
If a database has had automatic recovery enabled, you must temporarily disable automatic recovery in order to create a new table.
For information about monitoring K-Safety, see VT_SYSTEM in the SQL Virtual Tables (Monitoring API).

Examples

> SELECT MARK_DESIGN_KSAFE(1);

mark_design_ksafe

----------------------

Marked design 1-safe

(1 row)

If the physical schema design is not K-safe, messages indicate which projections do not have a buddy:

> SELECT MARK_DESIGN_KSAFE(1);

The given K value is not correct; the schema is 0-safe

Projection pp1 has 0 buddies, which is smaller that the given K of 1

Projection pp2 has 0 buddies, which is smaller that the given K of 1

(1 row)

A refresh runs simultaneously on all nodes. During a refresh, the new projection:

You can view the progress of the refresh by monitoring the log files or calling the following functions:

When the refresh is complete, the data stored in the projection represents the table columns as of the epoch in which the refresh commits. As a result, the query optimizer may not choose the new projection for AT EPOCH queries that request historical data at epochs older than the refresh epoch of the projection..