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

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