
The RESOURCE_POOL_MOVE System Table displays the cascade event information on each node. There you can find helpful information like the source and target pools and why the cascading event occurred!
Example:
dbadmin=> CREATE RESOURCE POOL pool_long RUNTIMECAP '20 SECONDS';
CREATE RESOURCE POOL
dbadmin=> CREATE RESOURCE POOL pool_medium RUNTIMECAP '5 SECONDS' CASCADE TO pool_long;
CREATE RESOURCE POOL
dbadmin=> CREATE RESOURCE POOL pool_fast RUNTIMECAP '2 SECONDS' CASCADE TO pool_medium;
CREATE RESOURCE POOL
dbadmin=> CREATE USER rp_cascade_test;
CREATE USER
dbadmin=> GRANT USAGE ON RESOURCE POOL pool_fast TO rp_cascade_test;
GRANT PRIVILEGE
dbadmin=> ALTER USER rp_cascade_test RESOURCE POOL pool_fast;
ALTER USER
dbadmin=> GRANT USAGE ON SCHEMA public TO rp_cascade_test;
GRANT PRIVILEGE
dbadmin=> GRANT SELECT ON public.big_table_of_ints2 TO rp_cascade_test;
GRANT PRIVILEGE
dbadmin=> \q
[dbadmin@s18384357 ~]$ vsql -U rp_cascade_test
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
rp_cascade_test=> SHOW resource_pool;
name | setting
---------------+-----------
resource_pool | pool_fast
(1 row)
rp_cascade_test=> \d public.big_table_of_ints2;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+--------------------+--------+------+------+---------+----------+-------------+-------------
public | big_table_of_ints2 | c | int | 8 | | f | f |
(1 row)
rp_cascade_test=> SELECT current_trans_id();
current_trans_id
-------------------
45035996273909381
(1 row)
rp_cascade_test=> \timing on
Timing is on.
rp_cascade_test=> SELECT MAX(a.c) FROM big_table_of_ints2 a CROSS JOIN (SELECT c FROM big_table_of_ints2 LIMIT 20) b;
MAX
-------
97658
(1 row)
Time: First fetch (1 row): 11024.772 ms. All rows formatted: 11024.820 ms
rp_cascade_test=> SELECT move_timestamp, source_pool_name, target_pool_name, move_cause, source_cap, target_cap, success, result_reason
rp_cascade_test-> FROM resource_pool_move
rp_cascade_test-> WHERE transaction_id = 45035996273909381
rp_cascade_test-> ORDER BY move_timestamp;
move_timestamp | source_pool_name | target_pool_name | move_cause | source_cap | target_cap | success | result_reason
-------------------------------+------------------+------------------+---------------------+------------+------------+---------+-----------------------------------------------
2018-09-04 09:15:59.160713-04 | pool_fast | pool_medium | RunTimeCap Exceeded | 2000000 | 5000000 | t | Statement successfully moved to target pool.
2018-09-04 09:16:02.180322-04 | pool_medium | pool_long | RunTimeCap Exceeded | 5000000 | 20000000 | t | Statement successfully moved to target pool.
(2 rows)
Time: First fetch (2 rows): 14.359 ms. All rows formatted: 14.425 ms)
Helpful link:https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_POOL_MOVE.htm
Have fun!