Setting a Runtime Limit for Queries
You can set a limit for the amount of time a query is allowed to run. You can set this limit at three levels, listed in descending order of precedence:
- The resource pool to which the user is assigned.
- User profile with
- Session queries, set by
SET SESSION RUNTIMECAP
In all cases, you set the runtime limit with an interval value that does not exceed one year. When you set runtime limit at multiple levels, Vertica always uses the shortest value. If a runtime limit is set for a non-superuser, that user cannot set any session to a longer runtime limit. Superusers can set the runtime limit for other users and for their own sessions, to any value up to one year, inclusive.
user1 is assigned to the
ad_hoc_queries resource pool:
=> CREATE USER user1 RESOURCE POOL ad_hoc_queries;
user1 is set to 1 hour:
=> ALTER USER user1 RUNTIMECAP '60 minutes';
RUNTIMECAP for the
ad_hoc_queries resource pool is set to 30 minutes:
=> ALTER RESOURCE POOL ad_hoc_queries RUNTIMECAP '30 minutes';
In this example, Vertica terminates
user1's queries if they exceed 30 minutes. Although the
user1's runtime limit is set to one hour, the pool on which the query runs, which has a 30-minute runtime limit, has precedence.
If a secondary pool for the
ad_hoc_queries pool is specified using the
CASCADE TO function, the query executes on that pool when the
RUNTIMECAP on the
ad_hoc_queries pool is surpassed.