Re: Recording insert, updates, and deletes

Hi,

I turned on the stats_row_level in the postgresql.conf file and now the the
calls to the stats functions work. I want to get the inserted, updated, and
deleted numbers on a given database, so i have written a query to do so:

SELECT
sum(pg_stat_get_tuples_inserted(c.oid)) AS inserted,
sum(pg_stat_get_tuples_updated(c.oid)) AS updated,
sum(pg_stat_get_tuples_deleted(c.oid)) AS deleted
FROM
pg_class c, information_schema.tables i
WHERE
i.table_catalog = 'testdb' AND
i.table_schema= 'public' AND
i.table_name = c.relname

I had to use the information schema as i could not figure out a way to fetch
the tables of a particular database using only pg_* tables. What i am
really now concerned is reliability, is it possible that the stats can be
incorrect ? and are they never reset ?. Also does using row level stats
have a serious effect on the performance ?

Cheers,

Andy

On 10/01/07, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
>
> On Wed, 2007-01-10 at 12:37, Andy Dale wrote:
> > Hi Brad,
> >
> > If i have to create a separate slony replication set, then i cannot do
> > it this way (i cannot and do not want to have a master-slave(s)
> > architecture)
> >
> > Andy
> >
> > On 10/01/07, Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info> wrote:
> > On Wed, 2007-01-10 at 16:51 +0100, Andy Dale wrote:
> >
> > > If anyone can help or offer advice on how to achieve my
> > objective it
> > > would be greatly appreciated.
> >
> > Slony log shipping will do this
>
> I've lost the OP, but look in the contrib/spi directory for something
> designed to do auditing of inserts / deletes etc...
>