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

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?.
Basically, the scenario is that two transactions are working in parallel.
Both of them do some SELECT queries and build their local list and work on
that list. Then both of them decide to add a new entry to the db. Due to
some legacy design problem, I cannot take advantage of the PostgreSQL
features and have to let the two transactions lock the whole table before
they add the entries. When one transation holds the lock, the other one
will be blocked waiting. But when the first transaction is done and
releases the lock, is there any way to let the second transaction check to
see if there is any change in the db table, and if not, go ahead,
otherwise it needs to do SELECT queries and refreshes its local cached
information and starts the process again to add a new entry? (Here the new
entries have some dependencies on each other, and there is no luxury in
the system to do rollback. :(( )
Thanks a lot!

--
Libby

The transactions should start by locking the table rather than by locking
it after they have done a select.

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