Clearing Storage Policies
Vertica meta-function CLEAR_OBJECT_STORAGE_POLICY clears a storage policy from a database, schema, table, or table partition. For example, the following statement clears the storage policy for table store.store_sales_fact:
=> SELECT CLEAR_OBJECT_STORAGE_POLICY ('store.store_sales_fact');
CLEAR_OBJECT_STORAGE_POLICY
--------------------------------
Object storage policy cleared.
(1 row)
The Tuple Mover moves existing storage containers to the parent storage policy's location, or the default storage location if there is no parent policy. By default, this move occurs after all pending mergeout tasks return.
You can also force the data to move immediately by setting the function's enforce-storage-move parameter to true. For example, the following statement clears the storage policy for table store.store_orders_fact and specifies to implement the move immediately:
=> SELECT CLEAR_OBJECT_STORAGE_POLICY ('store.store_orders_fact', 'true');
CLEAR_OBJECT_STORAGE_POLICY
-----------------------------------------------------------------------------
Object storage policy cleared.
Task: moving storages
(Table: store.store_orders_fact) (Projection: store.store_orders_fact_b0)
(Table: store.store_orders_fact) (Projection: store.store_orders_fact_b1)
(1 row)
Tip: Consider using meta-function ENFORCE_OBJECT_STORAGE_POLICY to relocate the data of multiple database objects as needed, to bring them into compliance with current storage policies. Using this function is equivalent to calling CLEAR_OBJECT_STORAGE_POLICY successively on multiple database objects and setting parameter enforce-storage-move to true.
Effects on Same-Name Storage Policies
The effects of clearing a storage policy depend on which policy you clear.
For example, table lineorder has a storage policy that specifies to store table data at location F2. Various partitions in this table are individually assigned their own storage locations, as verified by querying system table STORAGE_POLICIES:
=> SELECT * from v_monitor.storage_policies;
schema_name | object_name | policy_details | location_label ------------------+-------------------+------------------+---------------- | public | Schema | F4 public | lineorder | Table | F2 public | lineorder | Partition [0, 0] | F1 public | lineorder | Partition [1, 1] | F2 public | lineorder | Partition [2, 2] | F4 public | lineorder | Partition [3, 3] | M1 public | lineorder | Partition [4, 4] | M3 (7 rows)
Clearing the current storage policy from table lineorder, doing so has no effect on the storage policies of its individual partitions. For example, given the following CLEAR_OBJECT_STORAGE_POLICY statement:
=> SELECT CLEAR_OBJECT_STORAGE_POLICY ('lineorder');
CLEAR_OBJECT_STORAGE_POLICY
-------------------------------------
Default storage policy cleared.
(1 row)
The individual partitions in table lineorder retain their storage policies:
=> SELECT * from v_monitor.storage_policies; schema_name | object_name | policy_details | location_label -------------+-------------+------------------+---------------- | public | Schema | F4 public | lineorder | Partition [0, 0] | F1 public | lineorder | Partition [1, 1] | F2 public | lineorder | Partition [2, 2] | F4 public | lineorder | Partition [3, 3] | M1 public | lineorder | Partition [4, 4] | M3 (6 rows)
If you clear storage policies from a range of partitions keys in table lineorder, the storage policies of parent objects and other partition ranges are unaffected. For example, the following statement clears storage policies from partition keys 0 through 3:
=> SELECT CLEAR_OBJECT_STORAGE_POLICY ('lineorder','0','3');
clear_object_storage_policy
-------------------------------------
Default storage policy cleared.
(1 row)
=> SELECT * from storage_policies;
schema_name | object_name | policy_details | location_label
-------------+-------------+------------------+----------------
| public | Schema | F4
public | lineorder | Table | F2
public | lineorder | Partition [4, 4] | M3
(2 rows)
