How to track session and user for ten
schowdary
Vertica Customer
2021-01-29 18:27:30.730 DistCall Dispatch:7fd03ea2f700-b000000ccc83d9 [Txn] <INFO> Starting Commit: Txn: b000000ccc83d9 'DELETE FROM prd_xxx.xxxxx' 355101308 2021-01-29 18:27:30.731 DistCall Dispatch:7fd03ea2f700-b000000ccc83d9 [TM] <INFO> updateCPE: Updating projection xxxx CPE to 0x13ce62cd from 0xffffffffffffffff for wos 0xa00002a37ba2da 2021-01-29 18:27:30.736 DistCall Dispatch:7fd03ea2f700-b000000ccc83d9 [TM] <INFO> updateCPE: Updating projection xxxx CPE to 0x13ce62cd from 0xffffffffffffffff for wos 0xa00002a37ba2c0 2021-01-29 18:27:30.742 DistCall Dispatch:7fd03ea2f700 [Txn] <INFO> Commit Complete: Txn: b000000ccc83d9 at epoch 0x13ce62ce and new global catalog version 355101309
We had a accidental delete and trying to track down the transaction where it is coming from. We do not have backups to recover. Any Ideas how we can track and recover from this situation.
Answers
Depending on how far back your metadata goes, you should be able to track down the source.
Find the query that caused the delete in the query_profiles or query_requests table. Query_requests goes back further, I think. You can search for the query in the "request" column. Find the session_id of that transaction. And then, if you're lucky, you'll be able to find the corresponding session from the user_sessions table.
Incidentally, if you need to try to recover the row, you can try an "at time" or "at epoch" query. Basically, something like: "AT TIME 'time' SELECT * FROM bla" or "AT EPOCH ## SELECT * FROM bla" - each is a sort of historical snapshot of the deleted record prior to its delete vector getting removed. But by the default, this window is pretty small, so it might not be available anymore, depending on when the delete occurred, and how active the system is.