Re: Patch: shouldn't timezone(text, timestamp[tz]) be STABLE?

Aleksander Alekseev <aleksander(at)timescale(dot)com> writes:
> There are several overloaded versions of timezone() function. One
> version accepts timezone name and timestamptz and returns timestamp:
> This function is marked as IMMUTABLE and it's possible to use it in
> functional indexes. I believe it's a bug.

That's a deliberate choice IIRC. I agree that the behavior could
change after a tzdata update, but if the standard is that "immutable"
means "no conceivable future code or configuration change could alter
the results", then there's not a lot of functions that will pass that
test :-(.

As a pretty relevant example, we're not going to stop marking text
comparison operators as immutable, even though we know all too well
that the OS' sort order might change underneath us. The loss of
functionality and performance that would result from downgrading
those to stable is just not acceptable. It's better to treat them
as immutable and accept the risk of sometimes having to rebuild
indexes.

I don't see a lot of argument for treating tzdata changes differently
from OS locale changes.

> Other versions of timezone() seem to be fine, except:
> Source code | timetz_zone
> Does anyone know the reason why, unlike other versions, it's marked
> VOLATILE?

Looking at the code, it decides whether to use DST or not based on
the current time ... which it gets using time(NULL). So the volatile
marking is correct for this implementation, because it could change
intra-query. This seems like a pretty dumb choice though: I'd think
it'd make more sense to use the value of now() as the referent.
Then it could be stable, and it'd also be faster because it wouldn't
need its own kernel call.

regards, tom lane