Обсуждение: Data in table changed?

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?

Thank you :)

Kind regards,
Thomas Holmgren
Denmark


--
Mvh.
Thomas Holmgren

* 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