DROP RESOURCE POOL

Drops a user-created 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-spec ]

Parameters

pool-name
Specifies the resource pool to drop.
FOR subcluster-spec
Eon Mode only, drops this resource pool from the specified subcluster, where subcluster-spec is one of the following:
  • SUBCLUSTER subcluster-name: Drops pool-name from the named subcluster . You cannot be connected to this subcluster, otherwise Vertica returns an error.
  • CURRENT SUBCLUSTER: Drops pool-name from the subcluster that 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 by specifying this parameter; otherwise, Vertica returns an error.

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