By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,773 Members | 1,724 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,773 IT Pros & Developers. It's quick & easy.

Time at end of transaction

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
-------------------------------------------------------------------------- ----------------- Patrick Fiche
email : pa***********@aqsacom.com
tél : 01 69 29 36 18
-------------------------------------------------------------------------- -----------------

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Phil Endecott
Sent: mercredi 13 octobre 2004 14:11
To: pg***********@postgresql.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 ma*******@postgresql.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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
Phil Endecott wrote:
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:


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 ma*******@postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.