By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

Data in table changed?

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
* Thomas Holmgren (th*@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

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

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

Nov 23 '05 #2

P: n/a
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/...rojdisplay.php

HTH

Shridhar

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

Nov 23 '05 #3

P: n/a

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 <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

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

Nov 23 '05 #4

P: n/a
"Thomas Holmgren" <th*@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

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

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

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.