I need to be able to keep track of the number of transactions (the ones that are committed) that i am interested in, which in my case is all insert, update, and deletes performed on tables in the public schema. I have already tried to "select xact_commit from pg_stat_database" but xact_commit considers select to be a committed transaction which i do not want, also i don't consider pg_stat_database to be absolutely reliable as i have had a Postgres server installed on my local machine since last June, and it is only says i have 16,000 commited transactions (this is not correct, there have been far more).
I have been looking around the pg_catalog schema and i have found a few functions that are of interest to me, these are:
Each function takes an oid as the parameter, so i thought i could just pass a table's oid and it would return the results that i am interested in. Unfortunately the result returned from any of the above functions is always 0, this is wrong as i know data has at least been inserted (verified in pgAdmin). Having checked in postgresql.conf i found the following settings for statistics
# - Statistics Monitoring -
#log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off
# - Query/Index Statistics Collector -
#stats_start_collector = on stats_command_string = on #stats_block_level = off #stats_row_level = off #stats_reset_on_server_start = off
Do i have to enable one of these to get the function calls to work, and if so, which one(s).
I have also thought of obtaining this number by having a simple trigger that increments a number (value in a separate table) on insert, delete, update of a row in each table. I have been told that it might be possible to have a larger number than is correct because a trigger (BEFORE or AFTER) fires before the commit has taken place, so effectively it could be possible to increment the counter and then the DB server could crash and then the counter would be 1 larger than it should be, is this correct ?
If anyone can help or offer advice on how to achieve my objective it would be greatly appreciated.