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.
Postgresql – Investigating rollbacks in Postgres
postgresqlpostgresql-9.3rollback
Related Question
- Postgresql – changed password for postgres login in pgAdmin, now can’t connect to server
- Postgresql – Is anyone else experiencing consistency issues with postgres-xl
- PostgreSQL – How to Restore Backup with WAL-E
- Postgres – Troubleshooting Slony-I Replication Stopped Working
- SQL Server Rollback – Tracking Down a Transaction Rollback
Best Answer
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.