Thread: BUG #6761: unexpected behaviour of 'now'::timestamp
The following bug has been logged on the website: Bug reference: 6761 Logged by: Bert Thomas Email address: bert@brothom.nl PostgreSQL version: 9.1.3 Operating system: Linux Description:=20=20=20=20=20=20=20=20 Hi, To reproduce what I mean, consider this function: CREATE FUNCTION testbug() RETURNS character varying LANGUAGE plpgsql AS $$declare l_ts timestamp(0); begin l_ts :=3D 'now'::timestamp(0); return l_ts::varchar; end $$; If a program invokes this function multiple times on a single connection, only the first time the correct date and time is produced. All other invocations return the exact same value as the first invocation. Changing the function to this fixes the problem: CREATE FUNCTION testbug() RETURNS character varying LANGUAGE plpgsql AS $$declare l_ts timestamp(0); l_nu varchar; begin l_nu :=3D 'now'; l_ts :=3D l_nu::timestamp(0); return l_ts::varchar; end $$; Appearently the expression is re-evaluated every time in this case, whilst in the first case it is only evaluated once as the constant 'now' could not change obviously. I'm not sure if this is a bug or not, but at least it is suprising behaviour. To me it looks like a bad form of optimization. Kind regards, Bert Thomas BroThom
Hello this is not bug - it is consequence of plan cache http://postgres.cz/wiki/Automatic_execution_plan_caching_in_PL/pgSQL please, use CURRENT_TIMESTAMP instead - using 'now'::timestamp is deprecated due this issue Regards Pavel 2012/7/25 <bert@brothom.nl>: > The following bug has been logged on the website: > > Bug reference: 6761 > Logged by: Bert Thomas > Email address: bert@brothom.nl > PostgreSQL version: 9.1.3 > Operating system: Linux > Description: > > Hi, > > To reproduce what I mean, consider this function: > > CREATE FUNCTION testbug() RETURNS character varying > LANGUAGE plpgsql > AS $$declare > l_ts timestamp(0); > > begin > l_ts := 'now'::timestamp(0); > return l_ts::varchar; > end > $$; > > If a program invokes this function multiple times on a single connection, > only the first time the correct date and time is produced. All other > invocations return the exact same value as the first invocation. > > Changing the function to this fixes the problem: > > CREATE FUNCTION testbug() RETURNS character varying > LANGUAGE plpgsql > AS $$declare > l_ts timestamp(0); > l_nu varchar; > > begin > l_nu := 'now'; > l_ts := l_nu::timestamp(0); > return l_ts::varchar; > end > $$; > > Appearently the expression is re-evaluated every time in this case, whilst > in the first case it is only evaluated once as the constant 'now' could not > change obviously. I'm not sure if this is a bug or not, but at least it is > suprising behaviour. To me it looks like a bad form of optimization. > > Kind regards, > Bert Thomas > BroThom > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
Pavel Stehule <pavel.stehule@gmail.com> writes: > this is not bug - it is consequence of plan cache FWIW, there is documentation of this issue near the end of http://www.postgresql.org/docs/9.1/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING This exact case isn't covered in the examples, but the point is that the expression 'now'::timestamp will get folded to a timestamp constant during planning, and then not replanned later. As Pavel says, it's a lot safer to use one of the variants of the now() function. regards, tom lane
Thanks Tom and Pavel!