I was recently trying to find long running queries in Vertica, as we had something that was killing our performance of our cluster, and I got bit by this little bit of behavior around getting the “current” time in Vertica.
For some context, I had an open tmux session on a development server with one of the windows setup to be an open connection to our Vertica database1.
The part about having that database session open in a tmux session is important, because the database session had been open for at least a week at the point in time I was trying to do some checks to find the queries that had been running the longest.
What bit me was that current_timestamp, and now() functions are tied ‘to the start of the current transaction […] to allow a single transaction to have a consistent notion of the “current” time […]’.
The way this bit me, was I was seeing queries that were running for -7 days, because I was doing math on the timestamp against the current_timestamp.
1 2 3 4 5 6 7 8 9 10 11 | someuser => select current_timestamp; ?column? ------------------------------- 2014-07-11 10:45:17.135025-05(1 row)someuser=> select now(); now ------------------------------- 2014-07-11 10:45:17.135025-05(1 row) |
What I needed to be using was statement_timestamp(), which ‘returns the start time of the current statement’
1 2 3 4 5 | someuser=> select statement_timestamp(); now ------------------------------- 2014-07-18 14:23:36.492831-05(1 row) |
And that is how you get a more up to date timestamp in your Vertica queries if you are in a long running database session.
–Proctor
-
Since I have to help fill the role of a database administrator as part of being on the data team at work, and want quick access to be able to run some queries when things like the above happen, instead of hoping I can establish a connection.
Pingback: Swift Introduction Part Two - The Daily Six Pack
Pingback: Currently Executing Queries in VerticaProctor It | Proctor It