Data in table changed?

* Thomas Holmgren (thm@regnecentralen.dk) wrote:

I have a large number of clients synchronizing with a central database.
The clients update their local data by polling the database for changes
at fixed intervals. I need an efficient way of determining if data in a
table has been changed (either updated, deleted or inserted). Can this
be achieved without scanning the tables using expensive SQL? (my tables
are _big_!) Is there some way to get a "time for last update" for a
specific table? Or something similar I can use?

I would guess that you could create a trigger for the tables which
updates a seperate (small) table with the last-changed timestamp.

Stephen

On Tuesday 02 March 2004 19:34, Thomas Holmgren wrote:

Hi all,

I have a large number of clients synchronizing with a central database.
The clients update their local data by polling the database for changes
at fixed intervals. I need an efficient way of determining if data in a
table has been changed (either updated, deleted or inserted). Can this
be achieved without scanning the tables using expensive SQL? (my tables
are _big_!) Is there some way to get a "time for last update" for a
specific table? Or something similar I can use?

If you are syncing your entire database, why don't you look at replication
solutions which would transfer WAL files and perform any required updates..

Check http://gborg.postgresql.org/project/pgreplication/projdisplay.php

HTH

Shridhar

On 2004.03.02 08:04 Thomas Holmgren wrote:

I need an efficient way of determining if data in

a
table has been changed (either updated, deleted or inserted). Can this
be achieved without scanning the tables using expensive SQL? (my
tables
are _big_!) Is there some way to get a "time for last update" for a
specific table? Or something similar I can use?

You could always make table of 'last changed' timestamps with
columns of tablename and timestamp. Then a function which takes
the name of the table and updates the last_changed table with
a timestamp is called from a trigger for insert, update, and
delete of each table. The CREATE TRIGGER would pass the name
of the table to the function.

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

"Thomas Holmgren" <thm@regnecentralen.dk> writes:

I have a large number of clients synchronizing with a central database.
The clients update their local data by polling the database for changes
at fixed intervals. I need an efficient way of determining if data in a
table has been changed (either updated, deleted or inserted). Can this
be achieved without scanning the tables using expensive SQL?

Are the clients continuously connected to the database? If so you could
forget the whole polling concept and make it data-driven (the clients
listen for NOTIFY events sent out by updaters).

regards, tom lane