5

A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have tried searching the Postgres log file but it doesn't contain any instances of the word "rollback" and I can't see anything in the Postgres manual about enabling rollback logging.

  • 3
    Probably have to set log_statement = 'all' and a suitable log_line_prefix. But lots of rollbacks aren't necessarily a problem. Some frameworks ROLLBACK read-only transactions by default. It's generally harmless. – Craig Ringer Jul 7 '14 at 11:24
2

I wouldn't expect the mere presence of a rollback to be a problem. There are all kinds of reasons this might happen and nearly all are harmless. A good rule of thumb is that if the application requested a roll back, it is harmless at least for the DBA...

The key is to log statements in errors (which would force rollbacks) and investigate those. The other possibility which is possibly concerning is in serialization failures (if using serializable transactions) but here the application has the responsibility of handling the rollback.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.