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

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

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

2012-02-27 Thread Lennin Caro
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

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

2012-02-27 Thread 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

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

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

2012-02-27 Thread Lennin Caro
: [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

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

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

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