How to know a table has been modified?

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

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

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@postgresql.org> wrote:

From: Tatsuo Ishii <ishii@postgresql.org>
Subject: Re: [HACKERS] How to know a table has been modified?
To: Kevin.Grittner@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

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

On Mon, Feb 27, 2012 at 9:35 PM, Tatsuo Ishii <ishii@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

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@postgresql.org> wrote:

From: Tatsuo Ishii <ishii@postgresql.org>
Subject: Re: [HACKERS] How to know a table has been modified?
To: lennin.caro@yahoo.com
Cc: Kevin.Grittner@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

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

2012/2/28 Tatsuo Ishii <ishii@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

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