Clearing Storage Policies

You can clear a storage policy by object name after you have defined storage policies. To see existing policies, query the storage_policies system table, described in Viewing Storage Locations and Policies.

To clear a storage policy, use the CLEAR_OBJECT_STORAGE_POLICY function, specifying the object name associated with the labeled location:

=> select clear_object_storage_policy('lineorder');      clear_object_storage_policy       
-----------------------------------
 Default storage policy cleared.
(1 row)

Existing data is moved to the parent storage policy's location, or the default storage location if there is no parent policy.  Typically, the move occurs the next time the Tuple Mover runs. Alternatively, you can force the data move to occur in the current transaction by setting the optional enforce_storage_move parameter to true.

You can also use the ENFORCE_OBJECT_STORAGE_POLICY meta-function to trigger the move for all storage locations at once. Using this function equates to setting enforce_storage_move.

Speeding Up Data Migration

After you clear the storage policy, the Tuple Mover eventually migrates the object's data from the storage location to the database's default storage location. The TM moves the data when it performs a move-storage operation. This operation runs infrequently at low priority. Therefore, it might be some time before the data migrates out of the storage location.

You can speed up the data-migration process by:

  1. Calling the RETIRE_LOCATION function to retire the storage location on each host that defines it.
  2. Calling the MOVE_RETIRED_LOCATION_DATA function to move the location's data to the database's default storage location.
  3. Calling the RESTORE_LOCATION function to restore the location on each host that defines it. You must perform this step because you cannot drop retired storage locations.

The following example demonstrates clearing the object storage policy of a table stored on HDFS, then performing the steps to move the data off of the location. (See Using HDFS Storage Locations for additional background.)

=> SELECT * FROM storage_policies;
 schema_name | object_name | policy_details | location_label
-------------+-------------+----------------+----------------
 public      | test        | Table          | hdfs2
(1 row)

=> SELECT clear_object_storage_policy('test');
  clear_object_storage_policy
--------------------------------
 Object storage policy cleared.
(1 row)

=> SELECT retire_location('webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0001',
   'v_vmart_node0001');
                        retire_location
---------------------------------------------------------------
 webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0001 retired.
(1 row)

=> SELECT retire_location('webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0002',
   'v_vmart_node0002');
                        retire_location
---------------------------------------------------------------
 webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0002 retired.
(1 row)

=> SELECT retire_location('webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0003',
   'v_vmart_node0003');
                        retire_location
---------------------------------------------------------------
 webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0003 retired.
(1 row)

=> SELECT node_name, projection_name, location_label, total_row_count FROM 
   V_MONITOR.STORAGE_CONTAINERS WHERE projection_name ILIKE 'test%';
    node_name     | projection_name | location_label | total_row_count
------------------+-----------------+----------------+-----------------
 v_vmart_node0001 | test_b1         | hdfs2          |          333631
 v_vmart_node0001 | test_b0         | hdfs2          |          332233
 v_vmart_node0002 | test_b1         | hdfs2          |          332233
 v_vmart_node0002 | test_b0         | hdfs2          |          334136
 v_vmart_node0003 | test_b1         | hdfs2          |          334136
 v_vmart_node0003 | test_b0         | hdfs2          |          333631
(6 rows)

=> SELECT move_retired_location_data();
          move_retired_location_data
-----------------------------------------------
 Move data off retired storage locations done

(1 row)

=> SELECT node_name, projection_name, location_label, total_row_count 
   FROM V_MONITOR.STORAGE_CONTAINERS WHERE projection_name ILIKE 'test%';
    node_name     | projection_name | location_label | total_row_count
------------------+-----------------+----------------+-----------------
 v_vmart_node0001 | test_b0         |                |          332233
 v_vmart_node0001 | test_b1         |                |          333631
 v_vmart_node0002 | test_b0         |                |          334136
 v_vmart_node0002 | test_b1         |                |          332233
 v_vmart_node0003 | test_b0         |                |          333631
 v_vmart_node0003 | test_b1         |                |          334136
(6 rows)

=> SELECT restore_location('webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0001',
   'v_vmart_node0001');

                        restore_location
----------------------------------------------------------------
 webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0001 restored.
(1 row)

=> SELECT restore_location('webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0002',
   'v_vmart_node0002');
                        restore_location
----------------------------------------------------------------
 webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0002 restored.
(1 row)

=> SELECT restore_location('webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0003',
   'v_vmart_node0003');
                        restore_location
----------------------------------------------------------------
 webhdfs://hadoop:50070/user/dbadmin/v_vmart_node0003 restored.
(1 row)

Effects on Same-Name Storage Policies

The effects of clearing a storage policy depend on which policy you clear.

For example, consider the following storage configuration. The table lineorder has a storage policy for default storage to the location label F2. The table's partition ranges, also lineorder objects, have storage policies for other default storage locations:

=> 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)

For this example, clearing the storage policy for an objected named lineorder, removes the policy for the table, while retaining storage policies for its partitions, which have their own policies.

The function determines which lineorder object policy to clear because no partition range values are specified in the function call:

=> select clear_object_storage_policy('lineorder');
      clear_object_storage_policy       
-------------------------------------
 Default storage policy cleared.
(1 row)
release=> 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)

Further, using a partition key range with the lineorder object name clears the storage policy for only the specified partition range(s). The storage policy for the parent table objects, and other partition ranges persist:

=> select clear_object_storage_policy ('lineorder','0','3');
      clear_object_storage_policy       
-------------------------------------
 Default storage policy cleared.
(1 row)
release=> 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)