Clearing Storage Policies
The CLEAR_OBJECT_STORAGE_POLICY
meta-function clears a storage policy from a database, schema, table, or table partition. For example, the following statement clears the storage policy for the 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 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 the 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)
Consider using the ENFORCE_OBJECT_STORAGE_POLICY
meta-function 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 enforce-storage-move
to true.
Effects on Related Elements
Clearing a storage policy at one level, such as a table, does not necessarily affect storage policies at other levels, such as that table's partitions.
For example, the lineorder
table has a storage policy to store table data at location F2
. Various partitions in this table are individually assigned their own storage locations, as verified by querying the STORAGE_POLICIES
system table:
=> 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)
Clearing the current storage policy from the lineorder
table 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 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 key in a table, 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)