Search Postgresql Archives

Re: Recording insert, updates, and deletes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux