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.
→ Sessions: Getting the current/past session detail & closing any active session if required.
→ 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.
→ 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.