Hi,
I was badly bitten by the fact that date/time special values are parsed as constants early by the query parser. This
waswith DATE 'today', which is according to some older web pages the right way to get today's date (it's listed as an
examplein documentation before 8.0).
In my case, I used this in a plpgsql function, called at set times from a long-running connection. In that case, the
valueis cached as a constant in the compiled function. Imagine how long it's taken to figure out why daily procedures
wouldn'trun correctly, while they performed just fine when called manually from a fresh connection, and in any kind of
testI could come up with.
The behavior is easily reproduced with a line like:
create function foo() returns timestamptz as $$ begin return 'now'::timestamptz; end; $$ language plpgsql;
which will return the same value over and over.
I'd consider this a bug, or at least a deficiency in the documentation. There is a hint that this might happen in the
documentationsection 9.9.4 (warning against using such string in a DEFAULT clause), but no mention in section 8.5.1.4
(date/timespecial values, which defines these literals) other than the rather vague remark that they are converted to
ordinaryvalues when read.
In my humble opinion, the whole system would be more robust if the query parser would emit function calls for these
values(now(), CURRENT_DATE, CURRENT_DATE +/- 1), which would solve this for all cases (plpgsql functions, default
clauses,views, cached query plans). If the sql standard says it should behave like it does, or we're worried about
backwardscomp, then perhaps an improvement could be made by using a different mode in certain contexts (I can remotely
seesome benefit in being able to denote table or view creation time, but none in the time a function is first used in a
certainprocess, or the time at which a query is first parsed).
I don't think the current behavior is consistent, either. The conversion to time value is done by the parser only if
thetype can be derived from the statement context (i.e. immediate cast), otherwise it's done later (e.g. when assigning
astring literal to a timestamp variable, or returning a string literal from a function with timestamp type), with a
differentresult.
Nota bene, this page: https://www.postgresql.org/docs/current/plpgsql-implementation.html section 42.11.2 -- points out
howearly conversion can mess up query plan caching, and then uses a workaround with a variable as an example that
*does*work correctly. But if someone would helpfully replace "curtime := 'now';" with "curtime := 'now'::timestamp;" in
theexample, things would go wrong again.
If no change is made to the parser, then I'd propose at least a big fat warning in section 8.5.1.4 that 'now',
'yesterday','today', and 'tomorrow' should only be used with the greatest caution, as these values will be converted to
constantsand then cached in unexpected places.
Regards, Tijs van Dam