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



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

* Thomas Holmgren (thm (AT) regnecentralen (DOT) dk) wrote:
I would guess that you could create a trigger for the tables which
updates a seperate (small) table with the last-changed timestamp.

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFARJYlrzgMPqB3kigRAva3AJ9pajjF4f/dXLhazgoSn77G/roE/gCfTC5l
DiQZOEfOL4SEWFRwa9vCKRI=
=6OWO
-----END PGP SIGNATURE-----

On Tuesday 02 March 2004 19:34, Thomas Holmgren wrote:
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/...rojdisplay.php

HTH

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On 2004.03.02 08:04 Thomas Holmgren wrote:

I need an efficient way of determining if data in
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 (AT) meme (DOT) com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Thomas Holmgren" <thm (AT) regnecentralen (DOT) dk> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html