To: Denis Zaitsev <zzz@anda.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: Very strange 'now' behaviour in nested triggers. 
In-reply-to: <20030726193947.B1636@natasha.ward.six> 
References: <20030726193947.B1636@natasha.ward.six>
Comments: In-reply-to Denis Zaitsev <zzz@anda.ru>
	message dated "Sat, 26 Jul 2003 19:39:47 +0600"
Date: Sat, 26 Jul 2003 10:31:44 -0400
Message-ID: <9412.1059229904@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Denis Zaitsev <zzz@anda.ru> writes:
> create table xxx (
>     s text,
>     t timestamp
>         default 'now'
> );

That's a dangerous way to define the default --- 'now' is taken as a
literal of type timestamp, which means it will be reduced to a timestamp
constant as soon as a statement that requires the default is planned.
You lose in plpgsql because of plan caching, but you'd also lose if you
tried to PREPARE the insert command.  Example:

regression=# insert into xxx values('a');
INSERT 154541 1
regression=# insert into xxx values('b');
INSERT 154542 1
regression=# prepare s(text) as insert into xxx values($1);
PREPARE
regression=# execute s('q1');
EXECUTE
regression=# execute s('q2');
EXECUTE
regression=# select * from xxx;
 s  |             t
----+----------------------------
 a  | 2003-07-26 10:18:51.364913
 b  | 2003-07-26 10:18:53.519648
 q1 | 2003-07-26 10:19:21.795415
 q2 | 2003-07-26 10:19:21.795415
(4 rows)

The default would work the way you want with almost any other way of
doing it.  For instance

	default now()
	default current_timestamp
	default localtimestamp
	default 'now'::text

Given that you want timestamp without time zone, I'd probably use
"default localtimestamp".

			regards, tom lane