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)