DROP RESOURCE POOL

Drops a user-created resource pool.

Drops a user-created resource pool. All memory allocated to the pool is returned to the GENERAL pool.

Syntax

DROP RESOURCE POOL resource-pool [ FOR { SUBCLUSTER subcluster | CURRENT SUBCLUSTER } ]

Parameters

resource-pool

Name of the resource pool to drop.

FOR {SUBCLUSTER subcluster | CURRENT SUBCLUSTER}

Eon Mode only, drops resource-pool from the specified subcluster, one of the following:

SUBCLUSTER subcluster: Drops resource-pool from the named subcluster . You cannot be connected to this subcluster, otherwise Vertica returns an error.
CURRENT SUBCLUSTER: Drops resource-pool from the subcluster you are connected to.

If you omit this parameter, the resource pool is dropped from all subclusters. If a resource pool was created for an individual subcluster, you must explicitly drop it from that subcluster with this parameter; otherwise, Vertica returns an error.

Privileges

Superuser

Resource pool transfers

Requests that are queued against the dropped pool are transferred to the GENERAL pool according to the priority of the pool compared to the GENERAL pool. If the pool’s priority is higher than the GENERAL pool, the requests are placed at the head of the queue; otherwise, transferred requests are placed at the end of the queue.

Users who are assigned to the dropped pool are reassigned to the default user resource pool as set by DefaultResourcePoolForUser. The DROP request returns with a notice like this:

NOTICE:  Switched the following users to the <name> pool: <username>

If any user lacks permission to use the default user resource pool, Vertica rolls back the drop operation.

Existing sessions are transferred to the GENERAL pool regardless of whether the session user has privileges to use that pool. This can result in additional user privileges if access to the dropped pool is more restrictive than the GENERAL pool. In this case, you can prevent giving users additional privileges as follows:

Revoke permissions on the target resource pool from all users.
Close any sessions that had permissions on the resource pool.
Drop the resource pool.

Restrictions

If you try to drop a resource pool that is the secondary pool for another resource pool, Vertica returns an error. The error lists the resource pools that depend on the secondary pool you tried to drop. To drop a secondary resource pool, first set the CASCADE TO parameter to DEFAULT on the primary resource pool, and then drop the secondary pool.

For example, you can drop resource pool rp2, which is a secondary pool for rp1, as follows:
```
=> ALTER RESOURCE POOL rp1 CASCADE TO DEFAULT;
=> DROP RESOURCE POOL rp2;
```
You cannot drop a resource pool that is configured as the default user resource pool by the DefaultResourcePoolForUsers parameter.

Examples

Drop a user-defined resource pool:

=> DROP RESOURCE POOL ceo_pool;

Get the name of the current subcluster for an Eon Mode database, then drop its resource pool:

=> SELECT CURRENT_SUBCLUSTER_NAME();
 CURRENT_SUBCLUSTER_NAME
-------------------------
 analytics_1
(1 row)

=> DROP RESOURCE POOL dashboard FOR CURRENT SUBCLUSTER;
DROP RESOURCE POOL