Thread: Possible bug in date(timestamptz)?

I can create a functional index on date(timestamp) but not date(timestamptz).
Version 7.3.2

=> CREATE TABLE foo (ts_with timestamp with time zone,
  ts_without timestamp without time zone);
CREATE TABLE
=> CREATE INDEX foo1_idx ON foo ( date(ts_with) );
ERROR:  DefineIndex: index function must be marked IMMUTABLE
=> CREATE INDEX foo2_idx ON foo ( date(ts_without) );
CREATE INDEX

I can't think why one would be immutable and the other not, so I updated
pg_proc setting provolatile='i' for the function in question. All seems OK.

I haven't got the source to hand - can anyone verify this isn't an RPM glitch
(seems unlikely)?

Can any developers confirm that switching the flag is safe (can't think why
not)?

--
  Richard Huxton

Richard Huxton <dev@archonet.com> writes:
> I can't think why one would be immutable and the other not,

You aren't thinking hard enough, then :-(

date(timestamptz) depends on the TimeZone setting, the other does not.

            regards, tom lane

> Richard Huxton <dev@archonet.com> writes:
>> I can't think why one would be immutable and the other not,
>
> You aren't thinking hard enough, then :-(
>
> date(timestamptz) depends on the TimeZone setting, the other does not.

I thought immutable was for the length of a statement. Reading it, I see
that "stable" covers that. Timestamp without time zone it is then.

Thanks Tom

- Richard Huxton