pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feeddate/time special values incorrectly cached as constant in plpgsql
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2020-10-17 540849814c 3753e2720a 133d06f7bb c1a3188bae d74ed6fe6e 3f898c986e d317fd7570 Doc: caution against misuse of 'now' and related datetime literals.
* date/time special values incorrectly cached as constant in plpgsql
@ 2020-10-17 10:09 Tijs van Dam <tijs@thalex.com>
2020-10-17 15:35 ` Re: date/time special values incorrectly cached as constant in plpgsql David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tijs van Dam @ 2020-10-17 10:09 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
I was badly bitten by the fact that date/time special values are parsed as constants early by the query parser. This was with DATE 'today', which is according to some older web pages the right way to get today's date (it's listed as an example in 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 value is cached as a constant in the compiled function. Imagine how long it's taken to figure out why daily procedures wouldn't run correctly, while they performed just fine when called manually from a fresh connection, and in any kind of test I 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 documentation section 9.9.4 (warning against using such string in a DEFAULT clause), but no mention in section 8.5.1.4 (date/time special values, which defines these literals) other than the rather vague remark that they are converted to ordinary values 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 backwards comp, then perhaps an improvement could be made by using a different mode in certain contexts (I can remotely see some benefit in being able to denote table or view creation time, but none in the time a function is first used in a certain process, 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 the type can be derived from the statement context (i.e. immediate cast), otherwise it's done later (e.g. when assigning a string literal to a timestamp variable, or returning a string literal from a function with timestamp type), with a different result.
Nota bene, this page: https://www.postgresql.org/docs/current/plpgsql-implementation.html section 42.11.2 -- points out how early 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 the example, 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 constants and then cached in unexpected places.
Regards, Tijs van Dam
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date/time special values incorrectly cached as constant in plpgsql
2020-10-17 10:09 date/time special values incorrectly cached as constant in plpgsql Tijs van Dam <tijs@thalex.com>
@ 2020-10-17 15:35 ` David G. Johnston <david.g.johnston@gmail.com>
2020-10-17 16:18 ` Re: date/time special values incorrectly cached as constant in plpgsql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2020-10-17 15:35 UTC (permalink / raw)
To: Tijs van Dam <tijs@thalex.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sat, Oct 17, 2020 at 4:05 AM Tijs van Dam <tijs@thalex.com> wrote:
> 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 constants and then cached in unexpected places.
>
IMO, there really isn't anything surprising that these literal inputs end
up converted to constants, which are indeed cached in parts of the system
that utilize a cache, or are stored as the resultant literal instead of an
expression. That's how literal input values work. If I need something to
be dynamic I have to use a volatile function.
I've flagged this one for later consideration and may decide to write a
documentation patch at some point - but as the existing docs aren't wrong
and do cover this dynamic, if maybe not explicitly and thoroughly enough
for some readers, the effort/benefit calculation isn't that high for me.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date/time special values incorrectly cached as constant in plpgsql
2020-10-17 10:09 date/time special values incorrectly cached as constant in plpgsql Tijs van Dam <tijs@thalex.com>
2020-10-17 15:35 ` Re: date/time special values incorrectly cached as constant in plpgsql David G. Johnston <david.g.johnston@gmail.com>
@ 2020-10-17 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-17 20:05 ` Re: date/time special values incorrectly cached as constant in plpgsql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2020-10-17 16:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tijs van Dam <tijs@thalex.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Oct 17, 2020 at 4:05 AM Tijs van Dam <tijs@thalex.com> wrote:
>> 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 constants and then cached in unexpected places.
> IMO, there really isn't anything surprising that these literal inputs end
> up converted to constants, which are indeed cached in parts of the system
> that utilize a cache, or are stored as the resultant literal instead of an
> expression. That's how literal input values work. If I need something to
> be dynamic I have to use a volatile function.
Indeed, but I concur with the OP that 8.5.1.4 doesn't really expend enough
words on this point. Perhaps append something like
<caution>
While the values now, today, tomorrow, yesterday are fine to use in
interactive SQL commands, they can have surprising behavior when used
in prepared statements, views, or function definitions. In such cases,
plan caching can result in a converted specific time value continuing
to be used long after it becomes stale. Use one of the SQL functions
instead in such contexts.
</caution>
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date/time special values incorrectly cached as constant in plpgsql
2020-10-17 10:09 date/time special values incorrectly cached as constant in plpgsql Tijs van Dam <tijs@thalex.com>
2020-10-17 15:35 ` Re: date/time special values incorrectly cached as constant in plpgsql David G. Johnston <david.g.johnston@gmail.com>
2020-10-17 16:18 ` Re: date/time special values incorrectly cached as constant in plpgsql Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-10-17 20:05 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2020-10-17 20:05 UTC (permalink / raw)
To: Tijs van Dam <tijs@thalex.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
I wrote:
> Indeed, but I concur with the OP that 8.5.1.4 doesn't really expend enough
> words on this point.
After a bit more thought, I committed
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=540849814cdc22ea025777d374ff6705b...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-10-17 20:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-17 10:09 date/time special values incorrectly cached as constant in plpgsql Tijs van Dam <tijs@thalex.com>
2020-10-17 15:35 ` David G. Johnston <david.g.johnston@gmail.com>
2020-10-17 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-17 20:05 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox