Recording insert, updates, and deletes

From: "Andy Dale" <andy(dot)dale(at)gmail(dot)com>
To: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Subject: Recording insert, updates, and deletes
Date: 2007-01-10 15:51:57
Message-ID: faa313130701100751x49e1059fg2225f19f3d3e3729@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

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:

pg_stat_get_tuples_inserted()
pg_stat_get_tuples_updated()
pg_stat_get_tuples_deleted()

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.

Thanks,

Andy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-01-10 15:58:28 Re: SP in PostgreSQL
Previous Message Martijn van Oosterhout 2007-01-10 15:41:19 Re: join problem...