From: Tom Lane <tgl@sss.pgh.pa.us>
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?)
Date: Fri, 01 Oct 2004 18:53:03 -0400
Message-ID: <15677.1096671183@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, bruno@wolff.to, pgsql-hackers@postgreSQL.org
Subject: Re: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?)
In-Reply-To: <15677.1096671183@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox