I have one PostgreSQL 40TB size of database and every day I face the high rollback rate after every hour. I just want to see the rollback details about session which call rollback, rolled back table name etc. How can I fetch this details from database. Thanks in advance.
I didn't find any SQL or another way to fetch Rollback details
Look into the log file. If the rollbacks were caused by errors, these errors should show up in the log. If there was no error, but the application deliberately ran
ROLLBACK, the only way to log that is
log_statement = 'all', which can write so much log that it can impact database performance.
An alternative to
log_statement = 'all' (if that is too much) is to set
log_transaction_sample_rate = 0.01. Then one percent of your database transactions will be logged, which reduces the volume and still allows you to see enough
ROLLBACK statements to figure out what is going on.