Re: Recording insert, updates, and deletes

Sorry for being stupid, you can select the table info from the pg_class
table, so i can ignore the information schema.

If the stats collector is 'lossy ' i will not be able to use it, can anyone
confirm that it is ? So maybe my best option is to write a simple trigger
that just increments a counter (value in a separate table) after an
insert/update/delete and then add this trigger to each table i want to
record the stats for. Would this new approach work (i.e. be "lossless") ?

Cheers,

Andy

On 11/01/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> Andy Dale wrote:
> > 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.
>
> It's the pg_class table you'll want to start with. If you start psql
> with -E and then do \dt you'll see the queries it uses.
>
> > 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 ?
>
> Well, I'm not sure about incorrect. AFAIK the stats gatherer is "lossy",
> so there's not a 100% guarantee that every read/write is measured.
> Performance shouldn't be an issue unless you're already pushing the
> limits of your hardware.
>
> --
> Richard Huxton
> Archonet Ltd
>