Re: [HACKERS] How to know a table has been modified?
Tatsuo Ishii 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/2/28 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 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?
> 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?
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 wrote: From: Tatsuo Ishii 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 wrote: > > From: Tatsuo Ishii > 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?
On Mon, Feb 27, 2012 at 9:35 PM, Tatsuo Ishii 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?
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 wrote: > > From: Tatsuo Ishii > 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?
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 wrote: From: Tatsuo Ishii 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?
>> 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?
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
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
