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

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

From: bert(at)brothom(dot)nl
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6761: unexpected behaviour of 'now'::timestamp
Date: 2012-07-25 12:53:01
Message-ID: E1Su15J-0001Oj-Ja@wrigleys.postgresql.org (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: Fujii MasaoDate: 2012-07-25 16:00:05
Subject: Re: BUG #6756: primary_conninfo is ignored if restore_command is set..
Previous:From: Alexander LawDate: 2012-07-25 11:54:23
Subject: Re: BUG #6742: pg_dump doesn't convert encoding of DB object names to OS encoding

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