Re: Is there a way to query whether a table has been changed or not?

On Fri, Sep 06, 2002 at 06:12:19PM -0400, Xueying (Libby) SHEN wrote:
> Hi,
> I am doing a project to switch from a home-grown plain-text based database
> to PostgreSQL. Before, we can always check the timestamp of the file to tell
> whether the local info cached is staler than the file or not. So I am
> wondering if there is something similar in PostgreSQL?.

Libby,

the general approach to implement optimistic locking is

1. Read the "last update" timestamp from the database (t1),
together with the data to be edited.
2. Work on data locally.

(some time later:)

3. Obtain a write lock for the "last update" timestamp.
4. Read the "last update" timestamp from the database (t2).
5. If t2 > t1, abort transaction or re-read data, release lock,
report problem, done.
6. Otherwise, go on with the updating.
7. Finally, set the "last update" timestamp to "now".
8. Release the lock.

I don't think there is any built-in support in PostgreSQL for that,
but since you are doing your own locking anyway, you can arrange
an extra table to hold the update timestamp. Or maybe you can add
a timestamp column to the table being updated itself.

Take care -
JPL