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 majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Hi,

I think that timeofday() should solve your issue.
Just take care that this function returns text instead of timestamp... You
will have to cast it.

Patrick


-----Original Message-----
From: pgsql-general-owner (AT) postgresql (DOT) org
[mailtogsql-general-owner (AT) postgresql (DOT) org]On Behalf Of Phil Endecott
Sent: mercredi 13 octobre 2004 14:11
To: pgsql-general (AT) postgresql (DOT) org
Subject: [GENERAL] Time at end of transaction


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 majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly




Protected by Polesoft Lockspam
http://www.polesoft.com/refer.html


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

Phil Endecott wrote:
You want timeofday() - see the "date/time functions" section of the
manuals. Note that it returns text not timestamptz.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thanks to Patrick and Richard for pointing out timeofday(); I was
imagining that I'd have to do some server-side-code magic to get that.

But I'm still hoping that someone will have a better solution - using
this I still need a near-global lock between setting the timestamps and
committing the transaction. Surely last-modified timestamps and
cache-refreshing are being used all over the place...

Regards,

--Phil.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings