Dear All,
Within a transaction, now() and current_timestamp are constant and give the time that the transaction started. This is normally what you want. But I have a case where I actually need the time that the transaction is committed, or something similar. Is there a way to get it? Here is the problem:
The timestamps that I am recording are "last modified" times. The client may have kept a local copy of something, and asks the server to "send a new copy if it has been modified since time X". This is actually HTTP's in-modified-since behaviour.
Consider three transactions W, R1 and R2. W writes to the tables and is long-running. R1 and R2 only read the tables and are short-running. They are interleaved as follows:
W starts R1 starts R1 finishes W finishes R2 starts R2 finishes
R1 and R2 come from the same client. In R2 the client asks "send me everything that has changed since (time of R1)". It needs to get the changes made by W, since R1 saw the state of the database before W started.
W currently finishes with a statement like this:
update ..... set last_modified_time = current_timestamp where ....;
This doesn't do what I want - I need to record the time when W will finish and its changes become visible to other transactions, not the time that it started.
Of course it is impossible to know when a transaction that is still in progress will finish so some sort of trick is needed. The best that I can think of is:
begin; ...main work of transaction, no or few locks held... LOCK some important lock that blocks reads update set last_modified_time = really_now end;
Any ideas anyone?
(What happens, or should happen, to current_timestamp inside nested transactions?)
Regards,
--Phil.
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
