PostgreSQL JDBC - 'now' is sometimes not now

Author

'now' is sometimes not now - but current_timestamp is

Guillaume Cottenceau

2006-10-25, 8:24 am

Hi,

I update a row's TIMESTAMP WITH TIME ZONE column value with:

... SET <colname> = 'now'::TIMESTAMP WITH TIME ZONE ...

Initially, I was hesitating with CURRENT_TIMESTAMP, but in a
table description, a column with a default value of 'now' or
CURRENT_TIMESTAMP will both be printed "not null default
('now'::text)::times
tamp(6) with time zone" so I assumed the
correct way of writing it was 'now' and then to cast to a
timestamp with/without time zone.

However, I have seen that some entries in the table get wrongly
updated to the same timestamp in the past. Just like if the
updates after one of them were then using always the same
timestamp as this one instead of 'now'. I use prepared
statements, so I suppose there might be something wrong there
with optimization or the fact that the query is sent to the
server. I use prepared statements with
ResultSet. TYPE_SCROLL_INSENSIT
IVE, ResultSet.CONCUR_READ_ONLY and
a prepare threshold of 5.

I have experimented with CURRENT_TIMESTAMP instead of 'now', and
it "fixes" the problem. Also, and now it's getting kinda funny,
using ('now'::text)::TIMES
TAMP WITH TIME ZONE also "fixes" the
problem. So only the 'now'::TIMESTAMP WITH TIME ZONE form
exhausts the problem. So I was curious about it, a feature, a
bug, a misunderstanding from my side?

In the documentation, I could find that "String literals
specifying time-varying date/time values, such as 'now' or
'today' will no longer work as expected in column default
expressions" in the 7.4 release notes, but that's only talking
about column default expressions right? [1] seems to confirm (in
the form "TIMESTAMP 'now'") that the problem is limited to column
default values.

I am unsure if "not null default ('now'::text)::times
tamp(6) with
time zone" in the table description is not a bit misleading then,
since CURRENT_TIMESTAMP or now() should be rather used - or maybe
'now' should be deprecated completely?

Using postgresql-8.1-407.jdbc3.jar over java 1.4.2 connecting to
a 7.4 database.

Ref:
[1] http://www.postgresql.org/docs/{7.4,8.1}/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

--
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2006-10-25, 8:24 am

Guillaume Cottenceau <gc@mnc.ch> writes:
> I update a row's TIMESTAMP WITH TIME ZONE column value with:
> ... SET <colname> = 'now'::TIMESTAMP WITH TIME ZONE ...

What you will have there is a timestamp-type constant with the value
that now() had at the time the statement was parsed. If you re-use a
prepared statement then the value will be out of date.

> Initially, I was hesitating with CURRENT_TIMESTAMP, but in a
> table description, a column with a default value of 'now' or
> CURRENT_TIMESTAMP will both be printed "not null default
> ('now'::text)::times
tamp(6) with time zone" so I assumed the
> correct way of writing it was 'now' and then to cast to a
> timestamp with/without time zone.

If you are going to copy the implementation of CURRENT_TIMESTAMP,
you should at least copy it accurately. (Hint: the cast from
text to timestamp is not immutable.) But why are you not just
using CURRENT_TIMESTAMP, rather than trying to outsmart the system?

regards, tom lane

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

Guillaume Cottenceau

2006-10-25, 8:24 am

Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> If you are going to copy the implementation of CURRENT_TIMESTAMP,
> you should at least copy it accurately. (Hint: the cast from
> text to timestamp is not immutable.) But why are you not just
> using CURRENT_TIMESTAMP, rather than trying to outsmart the system?

I was just trying to use what is best and wrongly picked up
'now'::TIMESTAMP WITH TIME ZONE. I know now this was wrong :), I
only claimed that maybe the table description was a little
misleading (I know the difference was the ::text cast but for a
non postgresql expert, it didn't look crucial).

--
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links