From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: cast not IMMUTABLE?
Date: Thu, 06 May 2004 20:41:54 -0400
Message-ID: <26503.1083890514@sss.pgh.pa.us> (raw)
In-Reply-To: <c7ei84$j0e$1@floppy.pyrenet.fr>
References: <c7ei84$j0e$1@floppy.pyrenet.fr>
Gaetano Mendola <mendola@bigfoot.com> writes:
> consider that login_time is a TIMESTAMPTZ with an index on it.
> test# create index idx on user_logs ( (login_time::date) );
> ERROR: functions in index expression must be marked IMMUTABLE
> why that cast is not considered IMMUTABLE ?
Because it depends on your TimeZone setting. Observe:
regression=# begin;
BEGIN
regression=# select now(), now()::date;
now | now
-------------------------------+------------
2004-05-06 20:40:16.038307-04 | 2004-05-06
(1 row)
regression=# set timezone to 'GMT';
SET
regression=# select now(), now()::date;
now | now
-------------------------------+------------
2004-05-07 00:40:16.038307+00 | 2004-05-07
(1 row)
now() didn't change, but now()::date did.
> How can I define an index for the query (1) ?
What behavior are you really after here? You could change the column to
be timestamp without time zone (which would have an immutable conversion
to date), but if you want login_time to reflect some sort of absolute
reality then that's likely not the right answer.
Another possibility is to force the date conversion to occur with
respect to a particular time zone. I was going to suggest something
like
(login_time at time zone 'GMT')::date
but upon experimenting I see that that isn't considered immutable either
:-(. Offhand I think this may be an oversight --- I can't see any reason
for the various flavors of AT TIME ZONE (a/k/a timezone() function)
not to be considered immutable. (Hackers, any comments?)
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mendola@bigfoot.com, pgsql-hackers@postgresql.org
Subject: Re: cast not IMMUTABLE?
In-Reply-To: <26503.1083890514@sss.pgh.pa.us>
References: <c7ei84$j0e$1@floppy.pyrenet.fr> <26503.1083890514@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