Error: timestamp with timezone + interval is not immutable while creating index

Phil Couling <couling@gmail.com> writes:

main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ;
ERROR: functions in index expression must be marked IMMUTABLE

Does anyone know why adding two fields like this results in anything
other than an immutable function? Under what circumstances could it
return a different result?

timestamptz + interval is not immutable because the results can vary
depending on timezone. For instance, in my zone (America/New_York):

regression=# select '2011-11-06 00:00'::timestamptz;
timestamptz
------------------------
2011-11-06 00:00:00-04
(1 row)

regression=# select '2011-11-06 00:00'::timestamptz + '1 day'::interval;
?column?
------------------------
2011-11-07 00:00:00-05
(1 row)

regression=# select '2011-11-06 00:00'::timestamptz + '24 hours'::interval;
?column?
------------------------
2011-11-06 23:00:00-05
(1 row)

In a zone where that day wasn't a DST transition day, you'd get
different results. IOW, adding '1 day' can mean 23, 24, or 25 hours
depending on your zone and the particular date.

If the interval value doesn't contain any days or months or larger
components, then yeah, the sum would be immutable ... but unfortunately
there's no way to express that concept given the particular datatypes
we've got.

regards, tom lane

On 10/13/2011 04:32 PM, Tom Lane wrote:

Phil Couling<couling@gmail.com> writes:

main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ;
ERROR: functions in index expression must be marked IMMUTABLE...

timestamptz + interval is not immutable because the results can vary
depending on timezone. For instance, in my zone (America/New_York):

So it seems like a potential workaround, depending on the nature of your
data and applications, would be to convert the timestamptz into a
timestamp at a reference TZ:

steve=# create table testfoo (a_timestamptz timestamptz, an_interval
interval);
CREATE TABLE
steve=# create index testfoo_index on testfoo ((a_timestamptz at time
zone 'UTC' + an_interval));
CREATE INDEX

You will have to be sure you are getting the results you want in the
vicinity of DST changes and if you are handling multiple timezones.

Cheers,
Steve

On 14 October 2011 00:49, Steve Crawford <scrawford@pinpointresearch.com> wrote:

On 10/13/2011 04:32 PM, Tom Lane wrote:

Phil Couling<couling@gmail.com>  writes:

main=>  create index foo_next_update on foo( (last_updated +
update_cycle) ) ;
ERROR:  functions in index expression must be marked IMMUTABLE...

timestamptz + interval is not immutable because the results can vary
depending on timezone.  For instance, in my zone (America/New_York):

So it seems like a potential workaround, depending on the nature of your
data and applications, would be to convert the timestamptz into a timestamp
at a reference TZ:

steve=# create table testfoo (a_timestamptz timestamptz, an_interval
interval);
CREATE TABLE
steve=# create index testfoo_index on testfoo ((a_timestamptz at time zone
'UTC' + an_interval));
CREATE INDEX

You will have to be sure you are getting the results you want in the
vicinity of DST changes and if you are handling multiple timezones.

Cheers,
Steve

Thanks all

That makes a lot of sense. For some reason I'd thought that having
the timezone would make it immutable (since it represents an absolute
point in time) whereas without it would not be (since the point in
time it *actually* represents is dependant on time zone...). Guess I
hadn't thought that through very well.

Kudos to Postgres for pointing out a flaw in my design! I'll be adding
in the timezone to the table (or at least a table it references).

The new index looks more like this:
create index foo_next_update on foo ( ((first_update + (update_cycle *
update_count)) at time zone update_region) )

I'm not sure timezone will ever be anything but 'GB' in this case, but
there's nothing like future proofing.

Regards All