Vertica Metadata & data dictionary related queries for nodes, sessions & locks.

Vertica Metadata Queries (Part 1)

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.

→ Version: Getting the current Vertica version.

SELECT VERSION(); 
Output: Vertica Analytic Database v8.1.1-4

→ Cluster Nodes: Getting the nodes related detail in the Vertica cluster.

• Cluster Size : In order to check the number of nodes present in the cluster.

  
  SELECT count(*) FROM nodes; 
  Output: 22 --this output will depends on the nodes in the cluster
  

• Current Node State : Node_state column in nodes table provides the current state of the Vertica node. Some of base possible values: UP, DOWN, REECOVERING & STANDBY.
  Refer link here from Vertica site for bigger picture of 'NODE' states which includes moving from 1 state to another.

  
  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.
  

• Node Recovery : 'Recovery_Status' table provide information about recovery status of nodes in the cluster.

  
  SELECT node_name, recover_epoch, recovery_phase, current_completed, is_running FROM recovery_status;
  

→ Sessions: Getting the current/past session detail & closing any active session if required.

• Active Sessions : Get the information about all the active sessions for the user and this session info includes the login time, current & last statement executed in the session.

  
  SELECT * FROM sessions; 
  

• Current Session : Get the information about current active session and this session detail includes the login time, current & last statement executed in the session.

  
  SELECT * FROM current_session; 
  

• Active/Past Sessions : Veritca has a metadata table where it capture detail of all the active & past sessions for reference. This table will session start & end time populated of all the terminated sessions and end time will be null for active ones.

  
  SELECT * FROM user_sessions; 
  

• Close a session : Vertica provide a way to kill/close a external session from other session submitting the statement.

  
  SELECT close_session('session_id');
  

• Close all session : Verica provides a way to closes all other sessions except the one submitting the 'CLOSE_ALL_SESSIONS' function.

  
  SELECT CLOSE_ALL_SESSIONS(); 
  

• Interupt Executing Statement : Verica provides a way interupt an executing statement for external session. It need two parameters, first will be session_id & 2nd will be statement_id to be interupted.
  Session_id & statement_id will be available in 'SESSIONS' table.

  
  Syntax: INTERRUPT_STATEMENT(session_id,statement_id)
  
  SELECT INTERRUPT_STATEMENT('session_id', 1); 
  

  Note : Closing sessions are not always immediate.

→ Locks: Getting information about the current/past locks requested on various objects. User can only see the records for tables that user have privileges to view.

• Active Locks : 'Locks' table provide information about all the current locks applied on various objects including lock requested time & grant time.

  
  SELECT * FROM locks; 
  

• Active/Past Locks : 'Lock_Usage' table provide aggregate information about waiting time to acquire lock on object and amount of time lock was held for each object along with number of times lock was requested in a particular session .

  
  SELECT * FROM locks; 
  

• All Lock Attempts : 'Dc_Lock_Attempts' table provide information about all the locks requested irrespective of whether lock was granted or request timed out.
  'Result' column in 'Dc_Lock_Attempts' table provides values like 'granted' & 'timeout' which can be used to check errors due to time out.

  
  SELECT * FROM dc_lock_attempts WHERE result='timeout';
  

→ Lock Modes: There are various locks /lock modes available in Vertica to support all transactions (DMLs, DDLs etc). Below table describes them and when it is required.

Lock Mode	Description
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.