Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?)
Date: 2004-10-01 22:53:03
Message-ID: 15677.1096671183@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers pgsql-novice pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruno Wolff III 2004-10-02 00:50:08 Re: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?)
Previous Message Tom Lane 2004-10-01 21:17:28 Re: date_trunc'd timestamp index possible?

Browse pgsql-novice by date

  From Date Subject
Next Message Bruno Wolff III 2004-10-02 00:50:08 Re: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?)
Previous Message Tom Lane 2004-10-01 21:17:28 Re: date_trunc'd timestamp index possible?

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruno Wolff III 2004-10-02 00:50:08 Re: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?)
Previous Message Chris Browne 2004-10-01 22:02:35 Re: AIX and V8 beta 3