Thread: Last modification time
Greetings,
For a big application, I want to generate reports from the database and
keep these on-line as long as they reflect the actual contents of the
database. I only want to regenerate the reports when needed, i.e.,
when the database contents have changed.
I'm sure PostgreSQL can tell me when the last update has been
COMMITted but until now I haven't been able to find out how. I must
have used the wrong search terms...
Can anyone tell me how to find the last update time of a database?
Thanks,
Johan
Johan Vromans <jvromans@squirrel.nl> writes: > Greetings, > > For a big application, I want to generate reports from the database and > keep these on-line as long as they reflect the actual contents of the > database. I only want to regenerate the reports when needed, i.e., > when the database contents have changed. > > I'm sure PostgreSQL can tell me when the last update has been > COMMITted but until now I haven't been able to find out how. I must > have used the wrong search terms... > > Can anyone tell me how to find the last update time of a database? There isn't any out-of-the-box way that I know of. I would put an AFTER trigger on all the tables concerned that inserts a row into an audit table. Your report generator can then run periodically, see if there are any new audit entries, generate reports, and clean out the audit table (if desired). Note that the audit table may grow very fast and need vacuuming a lot if you clean it out. Audit tables are useful for other things too, if you can afford them. -Doug
Use LISTEN/NOTIFY and a trigger. Karsten -- DSL-Aktion wegen gro�er Nachfrage bis 28.2.2006 verl�ngert: GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl
Doug McNaught <doug@mcnaught.org> writes: > I would put an AFTER trigger on all the tables concerned that > inserts a row into an audit table. [...] Audit tables are useful for > other things too, if you can afford them. I think auditing is much too heavy for something simple as finding the last modification timestamp. Yes, it may be the only alternative, but a heavy one... -- Johan
Johan Vromans <jvromans@squirrel.nl> writes: > Doug McNaught <doug@mcnaught.org> writes: > >> I would put an AFTER trigger on all the tables concerned that >> inserts a row into an audit table. [...] Audit tables are useful for >> other things too, if you can afford them. > > I think auditing is much too heavy for something simple as finding the > last modification timestamp. Yes, it may be the only alternative, but > a heavy one... Yeah, LISTEN/NOTIFY is definitely a lighter-weight solution--I didn't think of that before. -Doug
>Yeah, LISTEN/NOTIFY is definitely a lighter-weight solution--I didn't >think of that before. > > Hi, I'm interested too in using this method ! Can anyone give a simple example of how to use/implement it ? Or good links to clear/quick documentation about these functions ? Thank you, Philippe Ferreira.
Philippe Ferreira <phil.f@worldonline.fr> writes: >>Yeah, LISTEN/NOTIFY is definitely a lighter-weight solution--I didn't >>think of that before. >> >> > Hi, > > I'm interested too in using this method ! > > Can anyone give a simple example of how to use/implement it ? > Or good links to clear/quick documentation about these functions ? The online PostgreSQL docs at postgresql.org are quite good. -Doug
Doug McNaught <doug@mcnaught.org> writes: > Yeah, LISTEN/NOTIFY is definitely a lighter-weight solution--I didn't > think of that before. LISTEN/NOTIFY looks like a synchronisation mechanism. You can notify a subscriber that something happened. But in my case, the report generating program runs only occasionally and will not be permanently running subscribed. I'm not sure how to use LISTEN/NOTIFY for that. -- Johan
Johan Vromans <jvromans@squirrel.nl> writes: > Doug McNaught <doug@mcnaught.org> writes: >> Yeah, LISTEN/NOTIFY is definitely a lighter-weight solution--I didn't >> think of that before. > > LISTEN/NOTIFY looks like a synchronisation mechanism. You can notify a > subscriber that something happened. But in my case, the report > generating program runs only occasionally and will not be permanently > running subscribed. I'm not sure how to use LISTEN/NOTIFY for that. Well you need something trigger-based, because I don't think it would be useful to look at modification dates on the database files or anything like that (checkpoints, vacuum, rolled back transactions etc would change those even when the user-visible data didn't change). You could have the trigger update a single-row table and have the report generator (or a wrapper) poll for it, or you could have it send a NOTIFY to a little program sitting on a connection, which would then kick off the report generator. -Doug
On Sat, Feb 11, 2006 at 09:26:57PM +0100, Johan Vromans wrote: > Doug McNaught <doug@mcnaught.org> writes: > > Yeah, LISTEN/NOTIFY is definitely a lighter-weight solution--I didn't > > think of that before. > > LISTEN/NOTIFY looks like a synchronisation mechanism. You can notify a > subscriber that something happened. But in my case, the report > generating program runs only occasionally and will not be permanently > running subscribed. I'm not sure how to use LISTEN/NOTIFY for that. You add ON INSERT/UPDATE/NOTIFY triggers to the tables you are interested in which fire a given NOTIFY. The report generator would LISTEN for that while connected. However, given your above description why does it not suffice to regenerate the report whenever the report generator connects ? If you want to do so only when the table has actually changed you might add a last_modified timestamp column with a default of now(), remember that in the report generator and later do select exists(select 1 from <table> where last_modified > <old_last_modified>) to check whether you need to regenerate the report. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes: > However, given your above description why does it not > suffice to regenerate the report whenever the report > generator connects ? If you want to do so only when the > table has actually changed you might add a last_modified > timestamp column with a default of now(), remember that in > the report generator [...] Yes, I can add a table that records the last modification date and report date, and add triggers to all tables to update the modification date (timestamp) when something changes. But I expected (hoped) that the database itself could tell me straight away when the last modification (COMMIT) had been performed. Background is that the system the database runs on is also used for other processing. The database is not used frequently. As a result, the database is usually swapped out which makes the initial connection rather unresponsive. Database updates are not frequent, hence the idea to generate the reports on demand, and cache them until the database changes. Yes, I know this will not work with any of the discussed approaches since asking the database for the last modification will also suffer from the initial unresponsiveness... -- Johan