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 a table:

=> 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 argument to true. For example, the following statement clears the storage policy for a table and implements 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)

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 a location labeled 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 the table 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)