Re: 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(at)squirrel(dot)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 groer Nachfrage bis 28.2.2006 verlngert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Doug McNaught <doug(at)mcnaught(dot)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(at)squirrel(dot)nl> writes:

> Doug McNaught <doug(at)mcnaught(dot)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(dot)f(at)worldonline(dot)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(at)mcnaught(dot)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(at)squirrel(dot)nl> writes:

> Doug McNaught <doug(at)mcnaught(dot)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(at)mcnaught(dot)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(dot)Hilbert(at)gmx(dot)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