473,287 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

last update time of a table

pg
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
6 8517
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

Nov 12 '05 #2
pg
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" <de*@archonet.com>
To: "pg" <pg@newhonest.com>; <pg***********@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

---------------------------(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

Nov 12 '05 #3
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/curre...ql-listen.html
-Jason


Mike Mascari
ma*****@mascari.com

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

Nov 12 '05 #4
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #5
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #6
pg
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" <cl******@hotmail.com>
To: "pg" <pg@newhonest.com>; <pg***********@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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: gsb | last post by:
I'm not very good in mySQL but am trying to modify a script. It has to do with a log table. I want to retrieve the last 2 log records (if any) and if the last log entries are the same as my...
8
by: Shawn McKenzie | last post by:
Can someone help me with a query to get the date/time that a database was last modified? I looked at the SHOW command but there was no mention of this. TIA, Shawn
4
by: Ike | last post by:
Is there a way in mysql to discern when the last date/time a field, row, or table was been updated or inserted? Thanks, ike
12
by: francisds | last post by:
Hi, Can you guys see if there's a solution to this problem? I have a database from which I have to read each record and process that record. New records are being added all the time, so I...
3
by: Ray | last post by:
I am having my first experience using BLOB as a row in a table. I am using it to insert graphics for labels we print. I have no problem inserting into and select from the table. The graphic is...
2
by: Mark Reed | last post by:
Hi all, Scenario: I have about 10 people using a front end database which only 2 have the privilege to update the information. This is done by pasting the contents of a notepad report into a...
0
by: Bob | last post by:
Background 1. I create a data table that has one primary key and one unique column with other columns as well 2. I then Bind it to my list countrol setting the DataSource to my table name and...
7
by: Hieronimus | last post by:
Hi all I inherited a server running MySQL 4.* from a previous developer. Now, the records on this database might be what I want, or they might just be test data. I think my only chance of...
0
by: marlberg | last post by:
Platform: Windows2000, WindowsXP, Windows Vista, etc Language: C#, ASP.NET Pre-compiled Libraries: Enterprise Library 3.0 full I have a requirement to implement in and display in C# and...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.