problem creating rtree index on timestamptz

Started by Hussein Patniabout 21 years ago2 messagesbugs
Jump to latest
#1Hussein Patni
security@cosbit.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

I was trying to create a functional index on a timestamptz column and
had the following problem. It seems subraction of an interval from a
timestamptz is not immutable. Should this be the case?

foo=> create table timestamp_tz_test ( start timestamptz primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"timestamp_tz_test_pkey" for table "timestamp_tz_test"
CREATE TABLE
 
foo=> create INDEX idx_timestamptz_rtree on timestamp_tz_test using
rtree ((start - '15 minutes'::INTERVAL));
ERROR: functions in index expression must be marked IMMUTABLE

I then tried a similar task with just at timestamp column and got this
error:

foo=> create table timestamp_test ( start timestamp primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"timestamp_test_pkey" for table "timestamp_test"
CREATE TABLE
 
foo=> create INDEX idx_timestamp_rtree on timestamp_test using rtree
((start - '15 minutes'::INTERVAL));
ERROR: data type timestamp without time zone has no default operator
class for access method "rtree"
HINT: You must specify an operator class for the index or define a
default operator class for the data type.

Should there be a default for this?

Thanks in advance,

- --hussein
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFCI44ZI2IgiXMhqe4RAnHvAKC9o2xHmfdQ0MuYJ8XWK3v9uxf5IgCgnCuk
tD2jJUIWaGlPtbiBcByE1HU=
=ayoM
-----END PGP SIGNATURE-----

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Hussein Patni (#1)
Re: problem creating rtree index on timestamptz

Hussein Patni <security@cosbit.com> writes:

It seems subraction of an interval from a
timestamptz is not immutable. Should this be the case?

Yes, because the results depend on your local timezone. For example:

regression=# set TimeZone TO 'EST5EDT';
SET
regression=# select '2005-02-28 17:00-05'::timestamptz - '6 months'::interval;
?column?
------------------------
2004-08-28 17:00:00-04
(1 row)

regression=# set TimeZone TO 'GMT';
SET
regression=# select '2005-02-28 17:00-05'::timestamptz - '6 months'::interval;
?column?
------------------------
2004-08-28 22:00:00+00
(1 row)

regression=# select '2004-08-28 17:00:00-04'::timestamptz - '2004-08-28 22:00:00+00';
?column?
-----------
-01:00:00
(1 row)

regression=#

It's probably true that subtracting an interval expressed in seconds (or
equivalent units) is an immutable operation, but we don't have a way to
capture that statement in data types, since there's no such restricted
interval datatype.

You might consider making a function defined like timestamptz - integer
(or float) in which the integer is considered as a number of seconds.
You could safely mark that as immutable, I think.

regards, tom lane