Date: Mon, 4 Aug 2025 14:20:48 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Adding per backend commit and rollback counters
Message-ID: <aJDBwNlyiFuJOoPx@ip-10-97-1-34.eu-west-3.compute.internal>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ZnbSmJtA6EgjuGCe"
Content-Disposition: inline
Archived-At: 
 <https://www.postgresql.org/message-id/aJDBwNlyiFuJOoPx%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk


--ZnbSmJtA6EgjuGCe
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hi hackers,

PFA a patch for $SUBJECT.

Currently we can find xact_commit and xact_rollback in pg_stat_database but we
don't have this information per backend.

This patch adds 2 functions: pg_stat_get_backend_xact_commit() and
pg_stat_get_backend_xact_rollback() to report the number of transactions that
have been committed/rolled back for a given backend PID.

I think having this information per-backend could be useful, for example, to:

- check which application is producing the highest number of commit / rollback
- check if the application's hosts have "uniform" commit/rollback pattern
- check if some application's hosts are doing a lot of rollback (as compared
to the other hosts): that could mean those hosts are not using an up-to-date
application version

This patch is pretty straightforward as it relies on the existing per backend
statistics machinery that has been added in 9aea73fc61d (so that there is not
that much design to discuss).

On a side note, I noticed that when a transaction fails, say this way:

postgres=# insert into bdt2 values(1);
ERROR:  relation "bdt2" does not exist

Then the existing pg_stat_get_db_xact_rollback() does not return the rollback
increment (so does pg_stat_database.xact_rollback). Indeed, the flush is done
during the next commit or explicit rollback.

Maybe we could add an extra counter, that tracks the transactions that have not
been explicitly rolled back (xact_error or such) and flush it at the right time.

Looking forward to your feedback,

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

--ZnbSmJtA6EgjuGCe
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="v1-0001-Adding-per-backend-commit-and-rollback-counters.patch"