From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Tijs van Dam <tijs@thalex.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: date/time special values incorrectly cached as constant in plpgsql
Date: Sat, 17 Oct 2020 12:18:16 -0400
Message-ID: <119368.1602951496@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwYGbpZFHpRSbxsXouFDp5EQQJ6jHpN61dJCtoCjBHSKdQ@mail.gmail.com>
References: <c2LuRv9BiRT3bqIo5mMQiVraEXey_25B4vUn0kDqVqilwOEu_iVF1tbtvLnyQK7yDG3PFaz_GxLLPil2SDkj1MCObNRVaac-7j1dVdFERk8=@thalex.com>
<CAKFQuwYGbpZFHpRSbxsXouFDp5EQQJ6jHpN61dJCtoCjBHSKdQ@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, tijs@thalex.com, pgsql-bugs@lists.postgresql.org
Subject: Re: date/time special values incorrectly cached as constant in plpgsql
In-Reply-To: <119368.1602951496@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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