2

I'm studying a little Postgres 9.3 stats, and I've observed that in my servers, this simple query:

select sum(xact_commit) as commits,sum(xact_rollback) as rollbacks from pg_stat_database;

I have curiosity to know the cause why it returns always more rollbacks than commits.

This is also true, for incremental values:

postgres=# select sum(xact_commit),sum(xact_rollback) from pg_stat_database;

     sum     |    sum    

------------+-----------

 1045889770 | 148206853

postgres=# select sum(xact_commit),sum(xact_rollback) from pg_stat_database;

    sum     |    sum    

------------+-----------

 1045890403 | 14820767

commits = 1045890403-1045889770 = 633
rollbacks = 148207673-148206853 = 820

I expect to have more commits than rollbacks, in normal conditions. Any idea?

  • Do you have any errors in logs in time? – Sahap Asci Nov 23 '17 at 18:24
  • I have some errors, but most SQL succeed (otherwise my customers would complain). Probably I misunderstanding the stats. – david.perez Nov 24 '17 at 8:24
  • 1
    a successful query does not mean the whole transaction will be committed. however a unsuccessful query means that the transaction is rolled back (if you don't handle that error in plpgsql) . Moreover you can rollback transactions in your application. I suggest you to test that numbers with a clean postgresql installation. run queries and check counts, rollback some transactions and check counts etc. – Sahap Asci Nov 28 '17 at 9:23

Your Answer

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

Browse other questions tagged or ask your own question.