DROP RESOURCE POOL

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

Syntax

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

Parameters

pool-name Specifies the resource pool to drop.
[ SUBCLUSTER subcluster-name | CURRENT SUBCLUSTER ]

Eon Mode only. Specifies the subcluster that you are dropping the resource pool from. When omitted, the resource pool is dropped globally. Attempting to drop a global resource pool while specifying a subcluster returns an error.

  • SUBCLUSTER — Use this keyword to drop the resource pool for a subcluster with the name subcluster-name if you are not connected to it, and it exists.
  • CURRENT SUBCLUSTER — Drops the specified resource pool from the subcluster that you are currently connected to.

Privileges

Superuser

Dropping a Secondary Pool

If you try to drop a resource pool that is a 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;

Transferring Resource Requests

Any requests queued against the 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 the requests are placed at the end of the queue.

Any users who are using the pool are switched to use the GENERAL pool with a NOTICE:

NOTICE:  Switched the following users to the General pool: username

DROP RESOURCE POOL returns an error if the user does not have permission to use the GENERAL pool. Existing sessions are transferred to the GENERAL pool regardless of whether the session's user has permission to use the GENERAL pool. This can result in additional user privileges if the pool being dropped is more restrictive than the GENERAL pool. To prevent giving users additional privileges, follow this procedure to drop restrictive pools:

  1. Revoke the permissions on the pool for all users.
  2. Close any sessions that had permissions on the pool.
  3. Drop the resource pool.

Examples

This example drops a user-defined resource pool:

=> DROP RESOURCE POOL ceo_pool;

This Eon Mode example returns the current subcluster, then drops a user-defined resource pool for the current subcluster:

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

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

See Also