Display the Current Statement Number Within the Current Transaction

Jim_Knicely

A Transaction in Vertica is one or more operations that are executed as a unit of work. At the user level, transactions occur in the current session by a user or script running one or more SQL statements. A transaction has a unique Transaction ID and the “units of work” within a transaction are each identified by a sequential Statement number.

You can use the CURRENT_TRANS_ID function to display the current Transaction ID and the CURRENT_STATEMENT function to display the current Statement number.

Example:

dbadmin=> SELECT current_trans_id(), current_statement();
current_trans_id  | current_statement
-------------------+-------------------
45035996273868005 |                 1
(1 row)

dbadmin=> SELECT current_trans_id(), current_statement();
current_trans_id  | current_statement
-------------------+-------------------
45035996273868005 |                 2
(1 row)

dbadmin=> SELECT current_trans_id(), current_statement();
current_trans_id  | current_statement
-------------------+-------------------
45035996273868005 |                 3
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT current_trans_id(), current_statement();
current_trans_id  | current_statement
-------------------+-------------------
45035996273868009 |                 1
(1 row)

dbadmin=> SELECT current_trans_id(), current_statement();
current_trans_id  | current_statement
-------------------+-------------------
45035996273868009 |                 2
(1 row)

Helpful links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/Glossary/Transaction.htm

Have fun!