Thread: last update time of a table
Is there any simple way to query the most recent time of "changes" made to a table? I'm accessing my database with ODBC to a remote site thru internet. I want to eliminate some DUPLICATE long queries by evaluating whether the data has been changed since last query. What should I do? -Jason
On Friday 05 December 2003 01:21, pg wrote: > Is there any simple way to query the most recent time of "changes" made to > a table? > > I'm accessing my database with ODBC to a remote site thru internet. I want > to eliminate some DUPLICATE long queries by evaluating whether the data has > been > changed since last query. What should I do? The canonical way is to add a last_changed column and a trigger to make sure it gets updated whenever the rest of the row is. Go over to http://techdocs.postgresql.org/ and check in the plpgsql cookbook or my Postgresql notes, or the archives come to think of it. -- Richard Huxton Archonet Ltd
I read thru your info, thanks a lot. In fact, I only need to decide whether a table (the whole) has been updated since last query. I have some pulldown menus in a VB app which extract data from a remote site with slow connection. And the data in those tables for pulldowns changes rarely. So if the pulldown has to extract the data and transmit it thru slow connection, the pulldown will take a few seconds to be in action, which is a little bit annoying, especially if the data is the same as in the array of client. So if I can query the table, knowing that no data changed in the table since my last query, I can use the client side array as pulldown data without waiting for long transmition time. I wonder if there is some more direct method, or thru the pg system tables to get this info. If there's not out there, I would use a trigger which will update a seperate table containing the last update time of all tables (not records) for pulldowns. -Jason ----- Original Message ----- From: "Richard Huxton" <dev@archonet.com> To: "pg" <pg@newhonest.com>; <pgsql-general@postgresql.org> Sent: Friday, December 05, 2003 5:53 PM Subject: Re: [GENERAL] last update time of a table > On Friday 05 December 2003 01:21, pg wrote: > > Is there any simple way to query the most recent time of "changes" made to > > a table? > > > > I'm accessing my database with ODBC to a remote site thru internet. I want > > to eliminate some DUPLICATE long queries by evaluating whether the data has > > been > > changed since last query. What should I do? > > The canonical way is to add a last_changed column and a trigger to make sure > it gets updated whenever the rest of the row is. > > Go over to http://techdocs.postgresql.org/ and check in the plpgsql cookbook > or my Postgresql notes, or the archives come to think of it. > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
pg wrote: > In fact, I only need to decide whether a table (the whole) has been updated > since last query. I have some pulldown menus in a VB app which extract data > from a remote site with slow connection. And the data in those tables for > pulldowns changes rarely. So if the pulldown has to extract the data and > transmit it thru slow connection, the pulldown will take a few seconds to be > in action, which is a little bit annoying, especially if the data is the > same as in the array of client. So if I can query the table, knowing that no > data changed in the table since my last query, I can use the client side > array as pulldown data without waiting for long transmition time. > > I wonder if there is some more direct method, or thru the pg system tables > to get this info. If there's not out there, I would use a trigger which will > update a seperate table containing the last update time of all tables (not > records) for pulldowns. You can use LISTEN/NOTIFY to do what you want: http://www.postgresql.org/docs/current/static/sql-listen.html > -Jason Mike Mascari mascarm@mascari.com
> In fact, I only need to decide whether a table (the whole) has been updated > since last query. I have some pulldown menus in a VB app which extract data > from a remote site with slow connection. And the data in those tables for > pulldowns changes rarely. So if the pulldown has to extract the data and > transmit it thru slow connection, the pulldown will take a few seconds to be > in action, which is a little bit annoying, especially if the data is the > same as in the array of client. So if I can query the table, knowing that no > data changed in the table since my last query, I can use the client side > array as pulldown data without waiting for long transmition time. > > I wonder if there is some more direct method, or thru the pg system tables > to get this info. If there's not out there, I would use a trigger which will > update a seperate table containing the last update time of all tables (not > records) for pulldowns. You can use NOTIFY/LISTEN with triggers on update/delete/insert. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
pg wrote: > I have some pulldown menus in a VB app which extract data > from a remote site with slow connection. And the data in those tables for > pulldowns changes rarely. So if the pulldown has to extract the data and > transmit it thru slow connection, the pulldown will take a few seconds to be > in action, which is a little bit annoying, especially if the data is the > same as in the array of client. Probably you'll do it better storing a local cached copy of the pulldown data (in the VB side) and having a background process to refresh it only when the server notifies that it has changed. hth cl.
Thanks for your suggestion. I think this is exactly Karsten and Mike said : create triggers to notify the client for any kind of updates. I'm not sure whether VB can handle such events, so I have to further study on this. -Jason ----- Original Message ----- From: "Claudio Lapidus" <clapidus@hotmail.com> To: "pg" <pg@newhonest.com>; <pgsql-general@postgresql.org> Sent: Sunday, December 07, 2003 11:09 PM Subject: Re: [GENERAL] last update time of a table > pg wrote: > > I have some pulldown menus in a VB app which extract data > > from a remote site with slow connection. And the data in those tables for > > pulldowns changes rarely. So if the pulldown has to extract the data and > > transmit it thru slow connection, the pulldown will take a few seconds to > be > > in action, which is a little bit annoying, especially if the data is the > > same as in the array of client. > > Probably you'll do it better storing a local cached copy of the pulldown > data (in the VB side) and having a background process to refresh it only > when the server notifies that it has changed. > > hth > cl. >