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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
