Re: No-rewrite timestamp<->timestamptz conversions

Noah Misch <noah(at)leadboat(dot)com> writes:
> Stepping back a bit, commit b8a18ad didn't provide a great UI. I doubt folks
> write queries this way spontaneously; to do so, they would have needed to
> learn that such syntax enables this optimization. If I'm going to do
> something more invasive, it should optimize the idiomatic "alter table t alter
> timestamptzcol type timestamp". One could do that with a facility like
> SupportRequestSimplify except permitted to consider STABLE facts. I suppose I
> could add a volatility field to SupportRequestSimplify. So far, I can't think
> of a second use case for such a facility, so instead I think
> ATColumnChangeRequiresRewrite() should have a hard-wired call for
> F_TIMESTAMPTZ_TIMESTAMP and F_TIMESTAMP_TIMESTAMPTZ. Patch attached. If we
> find more applications of this concept, it shouldn't be hard to migrate this
> logic into SupportRequestSimplify. Does anyone think that's better to do from
> the start?

It'd be nice to get the SupportRequestSimplify API correct from the first
release, so if there's even a slightly plausible reason for it to support
this, I'd be inclined to err in the direction of doing so. On the other
hand, if we really can't think of another use-case then a hard-wired fix
might be the best way.

One thing that we'd have to nail down a bit harder, if we're to add
something to the SupportRequestSimplify API, is exactly what the semantics
of the weaker check should be. The notion of "stable" has always been a
bit squishy, in that it's not totally clear what span of time stability
of the function result is being promised over. In the case at hand, for
instance, is it really impossible for the timezone GUC to change during
the execution of the ALTER TABLE command? You could probably break that
if you tried hard enough, though it seems unlikely that anyone would do so
accidentally.

I also kind of wonder whether this case arises often enough for us
to be expending so much effort optimizing it in the first place.
No doubt, where one lives colors one's opinion of how likely it is
that the timezone GUC is set to UTC ... but my guess is that that's
not true in very many installations.

regards, tom lane