Re: How to know a table has been modified?

you're right, changes in cascading tables are not logged.

Ing. Lennin Caro Pérez

Usuario:GNU/LINUX

PHP Developer

PostgreSQL DBA

Oracle DBA

Linux counter id 474393

--- On Mon, 2/27/12, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:

From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
Subject: Re: [HACKERS] How to know a table has been modified?
To: lennin(dot)caro(at)yahoo(dot)com
Cc: Kevin(dot)Grittner(at)wicourts(dot)gov, pgsql-hackers(at)postgresql(dot)org
Date: Monday, February 27, 2012, 4:05 PM

Are you suggesting log_statement? I don't think it's a solution by
following reasons:

1) it's slow to enable that on busy systems
2) tables affected by cascading delete/update/drop is not logged in
   PostgreSQL log
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> check the log of postgresql, there you can take the table name and the date of the modification
>
>
> Ing. Lennin Caro Pérez
>
> Usuario:GNU/LINUX
>
> PHP Developer
>
> PostgreSQL DBA
>
> Oracle DBA
>
> Linux  counter id 474393
>
> --- On Mon, 2/27/12, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>
> From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
> Subject: Re: [HACKERS] How to know a table has been modified?
> To: Kevin(dot)Grittner(at)wicourts(dot)gov
> Cc: pgsql-hackers(at)postgresql(dot)org
> Date: Monday, February 27, 2012, 12:04 PM
>
>>> For TRIGGER, I cannot thinking of any way. Any idea will be
>>> welcome.
>> 
>> It would require creating "cooperating" triggers in the database and
>> having a listener, but you might consider the
>> triggered_change_notifications() trigger function included in 9.2.
>> It works at least as far back as 9.0; I haven't tried it any further
>> back.
>
> Thanks for the info. It's a little bit overkill for my purpose though.
> (on busy systems, the notification would be too frequent).
>
> I would think that creating a small routine periodically consults
> pg_stat_all_tables view and records the last update datetime for each
> table (unfortunately the view does not have last modification date).
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers