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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match