Re: [HACKERS] How to know a table has been modified?

2012-02-27 Thread Lennin Caro
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 is...@postgresql.org wrote:

From: Tatsuo Ishii is...@postgresql.org
Subject: Re: [HACKERS] How to know a table has been modified?
To: kevin.gritt...@wicourts.gov
Cc: pgsql-hackers@postgresql.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] How to know a table has been modified?

2012-02-27 Thread Lennin Caro
   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 is...@postgresql.org wrote:

From: Tatsuo Ishii is...@postgresql.org
Subject: Re: [HACKERS] How to know a table has been modified?
To: lennin.c...@yahoo.com
Cc: kevin.gritt...@wicourts.gov, pgsql-hackers@postgresql.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 is...@postgresql.org wrote:
 
 From: Tatsuo Ishii is...@postgresql.org
 Subject: Re: [HACKERS] How to know a table has been modified?
 To: kevin.gritt...@wicourts.gov
 Cc: pgsql-hackers@postgresql.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@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers