Thread: How to know a table has been modified?

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.

create table t1(i int, j int);
create table t2(i int references t1.i);
drop table t1 cascade;

In this example, if t1 is dropped, t2 is dropped as well. So query
cache corresponding to t1 and t2 should be invalidated. The only way I
could thinking of is, looking into pg_depend. I would like to know if
there's any better/convenient way to know it.

For TRIGGER, I cannot thinking of any way. Any idea will be welcome.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top"><span class=""
id="result_box"lang="en"><span class="hps">check</span> <span class="hps">the log of</span> <span
class="hps">postgresql,</span><span class="hps">there</span> <span class="hps">you can take the</span> <span
class="hps">tablename</span> <span class="hps">and the date of</span> <span class="hps">the
modification</span></span><br/><br /><hr style="width: 100%; height: 2px;" /><br />Ing. Lennin Caro Pérez<br />
Usuario:GNU/LINUX<br/> PHP Developer<br /> PostgreSQL DBA<br /> Oracle DBA<br /> Linux counter id 474393<br /><br />---
On<b>Mon, 2/27/12, Tatsuo Ishii <i><ishii@postgresql.org></i></b> wrote:<br /><blockquote style="border-left: 2px
solidrgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />From: Tatsuo Ishii <ishii@postgresql.org><br
/>Subject:Re: [HACKERS] How to know a table has been modified?<br />To: Kevin.Grittner@wicourts.gov<br />Cc:
pgsql-hackers@postgresql.org<br/>Date: Monday, February 27, 2012, 12:04 PM<br /><br /><div class="plainMail">>>
ForTRIGGER, I cannot thinking of any way. Any idea will be<br />>> welcome.<br />>  <br />> It would
requirecreating "cooperating" triggers in the database and<br />> having a listener, but you might consider the<br
/>>triggered_change_notifications() trigger function included in 9.2. <br />> It works at least as far back as
9.0;I haven't tried it any further<br />> back.<br /><br />Thanks for the info. It's a little bit overkill for my
purposethough.<br />(on busy systems, the notification would be too frequent).<br /><br />I would think that creating a
smallroutine periodically consults<br />pg_stat_all_tables view and records the last update datetime for each<br
/>table(unfortunately the view does not have last modification date).<br />--<br />Tatsuo Ishii<br />SRA OSS, Inc.
Japan<br/>English: <a href="http://www.sraoss.co.jp/index_en.php"
target="_blank">http://www.sraoss.co.jp/index_en.php</a><br/>Japanese: <a href="http://www.sraoss.co.jp"
target="_blank">http://www.sraoss.co.jp</a><br/><br />-- <br />Sent via pgsql-hackers mailing list (<a
href="/mc/compose?to=pgsql-hackers@postgresql.org"
ymailto="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/>To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></div></blockquote></td></tr></table> 

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@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

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top"><div
class="almost_half_cell"id="gt-res-content"><div dir="ltr" style="zoom:1"><span class="" id="result_box"
lang="en"><spanclass="hps">you're right</span><span class="">, changes</span> <span class="hps">in cascading</span>
<spanclass="hps">tables</span> <span class="hps">are not logged.<br /><br /><br /></span></span></div></div><br /><hr
style="width:100%; height: 2px;" /><br />Ing. Lennin Caro Pérez<br /> Usuario:GNU/LINUX<br /> PHP Developer<br />
PostgreSQLDBA<br /> Oracle DBA<br /> Linux counter id 474393<br /><br />--- On <b>Mon, 2/27/12, Tatsuo Ishii
<i><ishii@postgresql.org></i></b>wrote:<br /><blockquote style="border-left: 2px solid rgb(16, 16, 255);
margin-left:5px; padding-left: 5px;"><br />From: Tatsuo Ishii <ishii@postgresql.org><br />Subject: Re: [HACKERS]
Howto know a table has been modified?<br />To: lennin.caro@yahoo.com<br />Cc: Kevin.Grittner@wicourts.gov,
pgsql-hackers@postgresql.org<br/>Date: Monday, February 27, 2012, 4:05 PM<br /><br /><div class="plainMail">Are you
suggestinglog_statement? I don't think it's a solution by<br />following reasons:<br /><br />1) it's slow to enable
thaton busy systems<br />2) tables affected by cascading delete/update/drop is not logged in<br />   PostgreSQL log<br
/>--<br/>Tatsuo Ishii<br />SRA OSS, Inc. Japan<br />English: <a href="http://www.sraoss.co.jp/index_en.php"
target="_blank">http://www.sraoss.co.jp/index_en.php</a><br/>Japanese: <a href="http://www.sraoss.co.jp"
target="_blank">http://www.sraoss.co.jp</a><br/><br />> check the log of postgresql, there you can take the table
nameand the date of the modification<br />> <br />> <br />> Ing. Lennin Caro Pérez<br />> <br />>
Usuario:GNU/LINUX<br/>> <br />> PHP Developer<br />> <br />> PostgreSQL DBA<br />> <br />> Oracle
DBA<br/>> <br />> Linux  counter id 474393<br />> <br />> --- On Mon, 2/27/12, Tatsuo Ishii <<a
href="/mc/compose?to=ishii@postgresql.org"ymailto="mailto:ishii@postgresql.org">ishii@postgresql.org</a>> wrote:<br
/>><br />> From: Tatsuo Ishii <<a href="/mc/compose?to=ishii@postgresql.org"
ymailto="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br/>> Subject: Re: [HACKERS] How to know a table
hasbeen modified?<br />> To: <a href="/mc/compose?to=Kevin.Grittner@wicourts.gov"
ymailto="mailto:Kevin.Grittner@wicourts.gov">Kevin.Grittner@wicourts.gov</a><br/>> Cc: <a
href="/mc/compose?to=pgsql-hackers@postgresql.org"
ymailto="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a><br/>> Date: Monday, February 27,
2012,12:04 PM<br />> <br />>>> For TRIGGER, I cannot thinking of any way. Any idea will be<br
/>>>>welcome.<br />>>  <br />>> It would require creating "cooperating" triggers in the database
and<br/>>> having a listener, but you might consider the<br />>> triggered_change_notifications() trigger
functionincluded in 9.2. <br />>> It works at least as far back as 9.0; I haven't tried it any further<br
/>>>back.<br />> <br />> Thanks for the info. It's a little bit overkill for my purpose though.<br />>
(onbusy systems, the notification would be too frequent).<br />> <br />> I would think that creating a small
routineperiodically consults<br />> pg_stat_all_tables view and records the last update datetime for each<br />>
table(unfortunately the view does not have last modification date).<br />> --<br />> Tatsuo Ishii<br />> SRA
OSS,Inc. Japan<br />> English: <a href="http://www.sraoss.co.jp/index_en.php"
target="_blank">http://www.sraoss.co.jp/index_en.php</a><br/>> Japanese: <a href="http://www.sraoss.co.jp"
target="_blank">http://www.sraoss.co.jp</a><br/>> <br />> -- <br />> Sent via pgsql-hackers mailing list (<a
href="/mc/compose?to=pgsql-hackers@postgresql.org"
ymailto="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/>> To make changes to your
subscription:<br/>> <a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></div></blockquote></td></tr></table> 

> 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