To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-hackers@postgreSQL.org
Subject: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd
	timestamp index possible?)
In-reply-to: <13492.1096656574@sss.pgh.pa.us> 
References: <web-1583353@davinci.ethosmedia.com>
	<Pine.GSO.4.10.10409271910480.22691-100000@solarwind>
	<20041001182830.GA20624@wolff.to> <13492.1096656574@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 01 Oct 2004 14:49:34 -0400"
Date: Fri, 01 Oct 2004 18:53:03 -0400
Message-ID: <15677.1096671183@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I wrote:
> Looking at this, I realize that date_trunc() is mismarked: the
> timestamptz variant is strongly dependent on the timezone setting
> and so should be STABLE not IMMUTABLE.  Ooops.

On looking more closely, I think that all of these functions are
mislabeled:

                                        oid                |   prorettype   |  prosrc                   | provolatile 

should be stable not immutable:
 date_trunc(text,timestamptz)                              | timestamptz    | timestamptz_trunc	        | i
 interval_pl_timestamptz(interval,timestamptz)             | timestamptz    | select $2 + $1	        | i
 timestamptz_pl_interval(timestamptz,interval)             | timestamptz    | timestamptz_pl_interval	| i
 timestamptz_mi_interval(timestamptz,interval)             | timestamptz    | timestamptz_mi_interval	| i
 "overlaps"(timestamptz,timestamptz,timestamptz,interval)  | boolean        | select ($1, $2) overlaps ($3, ($3 + $4))	| i
 "overlaps"(timestamptz,interval,timestamptz,timestamptz)  | boolean        | select ($1, ($1 + $2)) overlaps ($3, $4)	| i
 "overlaps"(timestamptz,interval,timestamptz,interval)     | boolean        | select ($1, ($1 + $2)) overlaps ($3, ($3 + $4))	| i

should be immutable not stable:
 to_char(timestamp,text)                                   | text           | timestamp_to_char         | s
 timestamptz(abstime)                                      | timestamptz    | abstime_timestamptz	| s
 abstime(timestamptz)                                      | abstime        | timestamptz_abstime	| s

It's easy to demonstrate that timestamptz+interval is dependent on the
timezone setting:

regression=# set timezone = 'EST5EDT';
SET
regression=# select '2004-03-31 00:00-05'::timestamptz + '1 month'::interval;
        ?column?
------------------------
 2004-04-30 00:00:00-04
(1 row)

regression=# set timezone = 'GMT';
SET
regression=# select '2004-03-31 00:00-05'::timestamptz + '1 month'::interval;
        ?column?
------------------------
 2004-04-30 05:00:00+00
(1 row)

and then the overlaps variants have to follow along.

On the other side of the coin, I don't think that to_char has any
dependency on timezone when it is dealing with a timestamp without time
zone.  (If you ask it for TZ you always get an empty string.)  Likewise
there's no such dependency in abstime/timestamptz conversions.

Do you see any other mislabelings?

What I'm inclined to do with these is change pg_proc.h but not force an
initdb.  Does anyone want to argue for an initdb to force it to be fixed
in 8.0?  We've lived with the wrong labelings for some time now without
noticing, so it doesn't seem like a serious enough bug to force a
post-beta initdb ... to me anyway.

			regards, tom lane