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

2012-03-06 Thread Dimitri Fontaine
Tatsuo Ishii is...@postgresql.org writes:
 I'm working on implementing query cache in pgpool-II. I want to know
 if a table has been modified because pgpool-II has to invalidate cache
 if corresponding table is modified. For DDL/DML it would be doable
 since pgpool-II knows all SQLs sent from clients. Problem is, implicit
 table modifications done by CASCADE, TRIGGERS and so on.

Some of that (triggers) is provided in the command triggers patch. The
CASCADE not so much but your command trigger will get called on the
top-level object.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
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 Tatsuo Ishii
 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
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 Tatsuo Ishii
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

-- 
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 Pavan Deolasee
On Mon, Feb 27, 2012 at 9:35 PM, Tatsuo Ishii is...@postgresql.org wrote:

 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


Would looking into currently held locks help ? You might get some false
positive because the transaction may have acquired a lock, but did not do
any modification. But if you can live with that, it might be worth
considering.

Thanks,
Pavan

-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com


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


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

2012-02-27 Thread Tatsuo Ishii
 Would looking into currently held locks help ? You might get some false
 positive because the transaction may have acquired a lock, but did not do
 any modification. But if you can live with that, it might be worth
 considering.

The locks disappear after corresponding sessions ends or the
transaction ends so I don't think I can use them for my particular
purpose.
--
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 Shigeru Hanada
2012/2/28 Tatsuo Ishii is...@postgresql.org:
 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

What about reading archived WAL files?  They would contain every
delete/update/drop including cascading ones, though it might be too late
because WAL files are not available until archived.

xlogdump would help reading WAL files.

https://github.com/snaga/xlogdump

-- 
Shigeru Hanada


-- 
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-26 Thread Kevin Grittner
Tatsuo Ishii  wrote:
 
 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.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers