Postgres now() vs 'now' in function

Question

Running into an issue where now() behaves different than 'now' when used in a function in Postgres.

drop table if exists test_date_bug;
CREATE TABLE test_date_bug
(
id serial NOT NULL,
date1 timestamp with time zone NOT NULL DEFAULT current_timestamp,
date2 timestamp with time zone NOT NULL DEFAULT 'infinity'
)
WITH (
OIDS=FALSE
);

drop function if exists test_date_bug_function(id_param bigint); 
CREATE OR REPLACE FUNCTION test_date_bug_function(id_param bigint)
RETURNS void AS
$$ 
BEGIN 
   UPDATE test_date_bug SET date2 = 'now' WHERE id = id_param;  
END;
$$
LANGUAGE 'plpgsql' VOLATILE
SECURITY DEFINER
SET search_path = public, pg_temp;  

insert into test_date_bug DEFAULT VALUES; 
insert into test_date_bug DEFAULT VALUES;  
insert into test_date_bug DEFAULT VALUES; 

select 1 from test_date_bug_function(1);

wait a couple seconds

select 1 from test_date_bug_function(2);

Results:

select * from test_date_bug; 
id |             date1             |             date2             
----+-------------------------------+-------------------------------
3 | 2015-12-10 12:42:01.931554-06 | infinity
1 | 2015-12-10 12:42:01.334465-06 | 2015-12-10 12:42:09.491183-06
2 | 2015-12-10 12:42:01.335665-06 | 2015-12-10 12:42:09.491183-06
(3 rows)

I would not expect the date2 on row 2 to be the same date2 as row 1.

Replacing

 UPDATE test_date_bug SET date2 = 'now' WHERE id = id_param;

With

 UPDATE test_date_bug SET date2 = now() WHERE id = id_param;

Sets new date as I would expect:

select * from test_date_bug; 
id |             date1             |             date2             
----+-------------------------------+-------------------------------
3 | 2015-12-10 12:43:29.480242-06 | infinity
1 | 2015-12-10 12:43:28.451195-06 | 2015-12-10 12:43:38.496625-06
2 | 2015-12-10 12:43:28.451786-06 | 2015-12-10 12:43:43.447715-06

Thoughts ?

Answer 1

It's not a bug, it's a feature... There are two points here.

1. Substitution of 'now'

   Let's have a look into the documentation (Date/Time Functions and Operators):

   All the date/time data types also accept the special literal value now to specify the current date and time (again, interpreted as the transaction start time). Thus, the following three all return the same result:

   SELECT CURRENT_TIMESTAMP;

   SELECT now();

   SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT

   Tip: You do not want to use the third form when specifying a DEFAULT clause while creating a table. 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! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion.

   So 'now' is converted to a timestamp at parse time.

2. Prepared statements

   Okay, but what does it mean in regard to functions? It's easy to demonstrate that a function is interpreted each time you call it:

   t=# create function test() returns timestamp as $$
   begin
    return 'now';
   end;
   $$ language plpgsql;
   CREATE FUNCTION
   
   t=# select test();
              test            
   ----------------------------
    2015-12-11 11:14:43.479809
   (1 row)
   
   t=# select test();
              test            
   ----------------------------
    2015-12-11 11:14:47.350266
   (1 row)
   

   In this example 'now' behaves as you expected.

   What is the difference? Your function uses SQL statements, and test() does not. Lets look into the documentation again (PL/pgSQL Plan Caching):

   As each expression and SQL command is first executed in the function, the PL/pgSQL interpreter parses and analyzes the command to create a prepared statement.

   And here (Prepare Statement):

   PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.

   Hence 'now' was converted to a timestamp when prepared statement was parsed. Let's demonstrate this by creating a prepared statement outside of a function:

   t=# prepare s(integer) as UPDATE test_date_bug SET date2 = 'now' WHERE id = $1;
   PREPARE
   
   t=# execute s(1);
   UPDATE 1
   t=# execute s(2);
   UPDATE 1
   
   t=# select * from test_date_bug;
    id |             date1             |             date2
   ----+-------------------------------+-------------------------------
     3 | 2015-12-11 11:01:38.491656+03 | infinity
     1 | 2015-12-11 11:01:37.91818+03  | 2015-12-11 11:40:44.339623+03
     2 | 2015-12-11 11:01:37.931056+03 | 2015-12-11 11:40:44.339623+03
   (3 rows)
   

That's what happend. 'now' was converted to a timestamp once (when prepared statement was parsed), and now() was called twice.

Answer 2

Egor has already pointed out the reason, but since you don't seem to be convinced I will discuss the issue further. The important part from the documentation is the following:

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!

While this does not exactly apply to your case, it can somehow explain the behavior of your procedure. As stated in the documentation, PostgreSQL will convert 'now' to a timestamp as soon as it parses it. Since it is a procedure, this will happen after the first call of the procedure. For each consecutive call the value will be the same, because it had already been converted after the first parse. So what basically happens is that you execute select 1 from test_date_bug_function(1); which converts 'now' to a timestamp that is now persistent in your procedure, and then select 1 from test_date_bug_function(2); simply uses the already converted value.

If you're not convinced yet, you can try executing(simply the statements outside of the procedure):

UPDATE test_date_bug SET date2 = 'now' WHERE id = 1; 
UPDATE test_date_bug SET date2 = 'now' WHERE id = 2; 

to see that you will achieve the desired behavior. This is because each UPDATE is newly compiled, while the procedure is persistent which makes the converted value persistent as well.