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(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