Skip site navigation (1) Skip section navigation (2)

Re: BUG #6761: unexpected behaviour of 'now'::timestamp

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: bert(at)brothom(dot)nl
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6761: unexpected behaviour of 'now'::timestamp
Date: 2012-07-25 16:27:29
Message-ID: CAFj8pRD6mmx3e7tSHn_W-vsu8L68H+k3N9d7OLVFT=jNfviSFg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)brothom(dot)nl>:
> The following bug has been logged on the website:
>
> Bug reference:      6761
> Logged by:          Bert Thomas
> Email address:      bert(at)brothom(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-07-25 16:53:03
Subject: Re: BUG #6761: unexpected behaviour of 'now'::timestamp
Previous:From: Alvaro HerreraDate: 2012-07-25 16:17:00
Subject: Re: BUG #6758: ./configure script sets HAVE_WCSTOMBS_L 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group