SET_DEPOT_PIN_POLICY_PARTITION

Pins the specified partitions of a table or projection to a subcluster depot, or all database depots, to reduce exposure to depot eviction.

Eon Mode only

Pins the specified partitions of a table or projection to a subcluster depot, or all database depots, to reduce exposure to depot eviction.

Partition groups can be pinned only if all partitions within the group are pinned individually. If you alter or remove table partitioning, Vertica drops all partition pinning policies for that table. The table's pinning policy, if any, is unaffected.

For details on pinning policies and usage guidelines, see Pinning Depot Objects.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

SET_DEPOT_PIN_POLICY_PARTITION ( '[[database.]schema.]object-name', 'min-range-value', 'max-range-value' [, 'subcluster' ] [, 'download' ] )

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
object-name: Table or projection to pin. If you specify a projection, it must store the partition keys.

Note
After you pin a table or one of its projections to a subcluster, you cannot subsequently pin any of its partitions to that subcluster. Conversely, you can pin one or more table partitions to a subcluster, and then pin the table or one of its projections to that subcluster.
min-range-value max-range-value: Minimum and maximum value of partition keys in object-name to pin, where min-range-value must be ≤ max-range-value. To specify a single partition, min-range-value and max-range-value must be equal.

Note
If partition pinning policies on the same table specify overlapping key ranges, Vertica collates the partition ranges. For example, if you create two partition policies with key ranges of 1-3 and 2-4, Vertica creates a single pinning policy with a key range of 1-4.
subcluster: Sets this pinning policy on the subcluster depot. To set this policy on the default subcluster, specify default_subcluster. If you omit this parameter, the policy is set on all database depots.
download: Boolean, if set to true, SET_DEPOT_PIN_POLICY_PARTITION immediately queues the specified partitions for download from communal storage.
Default: false

Privileges

Superuser

Precedence of pinning policies

In general, partition management functions that involve two partitioned tables give precedence to the target table's pinning policy, as follows:

Function	Application of pinnning policy
COPY_PARTITIONS_TO_TABLE	Partition-level pinning is reliable if the source and target tables have pinning policies on the same partition keys. If the two tables have different pinning policies, then the partition pinning policies of the target table apply.
MOVE_PARTITIONS_TO_TABLE	Partition-level pinning policies of the target table apply.
SWAP_PARTITIONS_BETWEEN_TABLES	Partition-level pinning policies of the target table apply.

For example, the following statement copies partitions from table foo to table bar:

=> SELECT COPY_PARTITIONS_TO_TABLE('foo', '1', '5', 'bar');

In this case, the following logic applies:

If the two tables have different partition pinning policies, then the pinning policy of target table bar for partition keys 1-5 applies.
If table bar does not exist, then Vertica creates it from table foo, and copies foo's policy on partition keys 1-5. Subsequently, if you clear the partition pinning policy from either table, it is also cleared from the other.