Dropping Storage Locations

To drop a storage location, use the DROP_LOCATION() function. The following example shows how to drop a storage location on v_vmartdb_node0002 that was used to store temp files:

=> SELECT DROP_LOCATION('/secondVerticaStorageLocation/' , 'v_vmartdb_node0002');

When you drop a storage location, the operation cascades to associated objects including any granted privileges to the storage.

Dropping a storage location is a permanent operation and cannot be undone. Subsequent queries on storage used for external table access fail with a COPY COMMAND FAILED message.

Because dropping a storage location cannot be undone, Vertica recommends that you first retire a storage location (see Retiring Storage Locations). Retiring a storage location before dropping it lets you verify that there will be no adverse effects on any data access. Additionally, you can restore a retired storage location (see Restoring Retired Storage Locations).

Altering Storage Locations Before Dropping Them

You can drop only storage locations containing temp files. Thus, you must alter a storage location to the TEMP usage type before you can drop it. However, if data files still exist on the storage, Vertica prevents you from dropping the storage location. Deleting data files does not clear the storage location and can result in database corruption. To handle a storage area containing data files so that you can drop it, use one of these options:

  • Manually merge out the data files.
  • Wait for the ATM to mergeout the data files automatically.
  • Retire the location, and force the changes to take effect immediately.
  • Manually drop partitions

Dropping USER Storage Locations

Storage locations that you create with the USER usage type can contain only data files, not temp files. However, you can drop a USER location, regardless of any remaining data files. This behavior differs from that of a storage location not designated for USER access.

Checking Location Properties

You can check the properties of a storage location, such as whether it is a USER location or is being used only for TEMP files, in the STORAGE_LOCATIONS system table. You can also use this table to verify that a location has been retired.