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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx > 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...
