Like anyone other RDBMS, Vertica also provides a large number of metadata tables which can be used for various purpose. In this tutorial, we will discuss how to check nodes in the cluster, Vertica vertion, active/past sessions and locks on the objects. These are some of the use cases but it can be used for much more than getting only this info.
SELECT VERSION(); Output: Vertica Analytic Database v8.1.1-4
SELECT count(*) FROM nodes; Output: 22 --this output will depends on the nodes in the cluster
SELECT node_id, node_name, node_state FROM nodes; UP -- Node is up & running DOWN -- Node is down RECOVERING -- Node was down & now in process of recovering. STANDBY -- Such nodes are stand by nodes and come into action if other node/s are geting down.
SELECT node_name, recover_epoch, recovery_phase, current_completed, is_running FROM recovery_status;
SELECT * FROM sessions;
SELECT * FROM current_session;
SELECT * FROM user_sessions;
Syntax: INTERRUPT_STATEMENT(session_id,statement_id) SELECT INTERRUPT_STATEMENT('session_id', 1);Note : Closing sessions are not always immediate.
SELECT * FROM locks;
SELECT * FROM locks;
SELECT * FROM dc_lock_attempts WHERE result='timeout';
|S||'Shared (S) lock' is needed for SELECT queries.
This mode allows queries to run concurrently and ensure one transaction does not affect other transaction.
It also creates the effect that transactions are running in serial order.
|I||'Insert (I) lock' is required to insert data into a table.
Object can be locked by multiple transactions simultaneously to enable multiple inserts.
|SI||'Shared Insert (SI) lock' is required for transactions where both read and insert occur at the same time.
This mode prohibits any delete/update operations.
|X||'Exclusive (X) lock' is required for performing deletes and updates.
Only operations with U locks mode can run concurrently on objects with X locks.
|U||'Usage (U) lock' is used for moveout and mergeout Tuple Mover operations.
These operations run automatically in the background, therefore, most other operations (except those requiring an O lock) can run when the object is locked in U mode.
|T||'Tuple Mover (T) lock' is used by Tuple mover for operations on delete vectors.
No other updates or deletes can happen concurrently once this lock is acquired.
|O||'Owner (O) lock' is the strongest Vertica lock mode. Owner lock is needed for DDL operations such as DROP_PARTITION, TRUNCATE TABLE and ADD COLUMN. O mode conflicts with all other lock modes.|
|IV||'Insert Validate (IV) lock' is needed for insert operations where the system performs constraint validation such as 'Primary key' or 'Unique key' constraints.|