Hi, everybody,

Our applications must work on various RDBMS'es, so in sql queries we
cannot use some built-in functions because they are not portable. Thus
we had to create own functions wrapping native ones. Fo example we have
created function returning current timestamp. And on PostgreSQL (tested
on 8.1.4, 8.3.5, 8.4.0) we noticed very strange behaviour. Let's create:

CREATE OR REPLACE FUNCTION MyNow1() RETURNS timestamp AS '
BEGIN
return timestamp(0) ''now'';
END;
' LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION MyNow2() RETURNS timestamp AS '
BEGIN
return current_timestamp(0);
END;
' LANGUAGE plpgsql VOLATILE;

And now it's time for select:

SELECT
MyNow1() as MyNow1,
MyNow2() as MyNow2;


Then I executed the query few times. The result is astonishing (I have
cut irrelevant lines for better clearity):

mynow1 | mynow2
---------------------+---------------------
2009-07-16 14:07:05 | 2009-07-16 14:36:06
2009-07-16 14:07:05 | 2009-07-16 14:36:10
2009-07-16 14:07:05 | 2009-07-16 14:36:19
2009-07-16 14:07:05 | 2009-07-16 14:36:53
2009-07-16 14:07:05 | 2009-07-16 14:36:56
2009-07-16 14:07:05 | 2009-07-16 14:37:01

It seems that MyNow1 is cached despite the fact the function was created
with 'volatile' modifier! Cached result stands until reconnect. May it
be a bug in PostgreSQL? In opposite direct call return better results:

SELECT
timestamp(0) 'now' as now1,
current_timestamp(0) as now2;

now1 | now2
---------------------+------------------------
2009-07-16 14:53:52 | 2009-07-16 14:53:52+02
2009-07-16 14:54:57 | 2009-07-16 14:54:57+02

Google has given me no explanation about this (imho strange) behaviour
so I'm asking for help.


--
Pozdrawiam
Jarek Lubczyñski
(eljot_007 at poczta dot pf dot pl)

There are 10 kinds of people:
Those who understand binary and those who don't

Jaros³aw Lubczyñski wrote:
Yes, this is confusing.

You'll find the explanation in
http://www.postgresql.org/docs/curre...TETIME-CURRENT
in the tip:

The system will convert now to a timestamp as soon as the constant is parsed,
so that when the default value is needed, the time of the table creation would be used!

You can also see this in 8.4.0 with:

test=> explain verbose select timestamp(0) 'now';
QUERY PLAN
-----------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: '2009-07-17 09:23:52'::timestamp(0) without time zone
(2 rows)

test=> explain verbose select current_timestamp;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: now()
(2 rows)

So your function "MyNow1" returns the timestamp of the creation of
the function and not the current time.

Yours,
Laurenz Albe

Laurenz Albe pisze:

I saw this doc but it seemed to me it didn't concern my case.

Well, it's not quite exactly I suppose. MyNow1() and MyNow2() return
always the same value when they're called first time after *every*
connection to database, see listing below (both functions were created
yesterday):

<listing>

[...]# psql -Umyuser mydb
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

mydb=# SELECT
mydb-# MyNow1() as MyNow1,
mydb-# MyNow2() as MyNow2;
mynow1 | mynow2
---------------------+---------------------
2009-07-17 09:35:18 | 2009-07-17 09:35:18 <= identical at first call
(1 row)

mydb=# SELECT
MyNow1() as MyNow1,
MyNow2() as MyNow2;
mynow1 | mynow2
---------------------+---------------------
2009-07-17 09:35:18 | 2009-07-17 09:36:22 <= different values
(1 row)

mydb=# \q
[...]# psql -Umyuser mydb
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

mydb=# SELECT
MyNow1() as MyNow1,
MyNow2() as MyNow2;
mynow1 | mynow2
---------------------+---------------------
2009-07-17 09:36:49 | 2009-07-17 09:36:49 <= identical at first call
(1 row)

mydb=# \q

</listing>

So I suppose this behaviour is not consistent with documentation.

Thanks a lot.

--
Greetings
Jarek Lubczyñski
(eljot_007 at poczta dot pf dot pl)

There are 10 kinds of people:
Those who understand binary and those who don't

Jaros³aw Lubczyñski wrote:
The mistake is mine, the documentation is correct.

"As soon as the constant is parsed" in this case is *not* when the
function is created, but when it is executed the first time in a connection.

Yours,
Laurenz Albe

Laurenz Albe pisze:
Thanks again.

--
Greetings
Jarek Lubczyñski
(eljot_007 at poczta dot pf dot pl)

There are 10 kinds of people:
Those who understand binary and those who don't