Do any of you understand what "pg_stat_get_db_xact_commit" internally does? Even when the application is idle and no requests to the db are sent, the count keeps increasing continuously. Any idea what could be happening in the background?
A:
What version of PostgreSQL are you running? Testing on an idle 8.4.4 install, it doesn't change even after repeated calls. I'm guessing you're running a version prior to 8.3 where read-only commands generated transaction ids.
From the Postgres 8.3 release notes:
Using non-persistent transaction IDs for read-only transactions reduces overhead and VACUUM requirements (Florian Pflug)
Non-persistent transaction IDs do not increment the global transaction counter. Therefore, they reduce the load on pg_clog and increase the time between forced vacuums to prevent transaction ID wraparound. Other performance improvements were also made that should improve concurrency.
So prior to 8.3, simply running "SELECT pg_stat_get_db_xact_commit(oid);" would increment the counter, causing a different result on the next SELECT, even if nothing else was running.
Matthew Wood
2010-09-15 16:56:14
I see.. But the version which i am running is 8.4
Rekha
2010-09-15 18:19:03
The only other suggestions I have are to (1) look to see if autovacuum is running while you are seeing the db_xact_commit number increasing and (2) turn on logging with the debug level set to 5 and monitor the logs while checking db_xact_commit (basically log everything possible and then do forensics).
Matthew Wood
2010-09-15 22:16:10