Re: No-rewrite timestamp<->timestamptz conversions

On Thu, Feb 05, 2015 at 08:36:18PM -0500, Noah Misch wrote:
> On Tue, Nov 05, 2013 at 05:02:58PM -0800, Josh Berkus wrote:
> > I'd also love some way of doing a no-rewrite conversion between
> > timestamp and timestamptz, based on the assumption that the original
> > values are UTC time. That's one I encounter a lot.
>
> It was such a conversion that motivated me to add the no-rewrite ALTER TABLE
> ALTER TYPE support in the first place. Interesting. Support for it didn't
> end up in any submitted patch due to a formal problem: a protransform function
> shall only consult IMMUTABLE facts, but we posit that timezone==UTC is a
> STABLE observation. However, a protransform function can easily simplify the
> immutable expression "tscol AT TIME ZONE 'UTC'", avoiding a rewrite. See
> attached patch.

This (commit b8a18ad) ended up causing wrong EXPLAIN output and wrong
indxpath.c processing. Hence, commit c22ecc6 neutralized the optimization;
see that commit's threads for details. I pondered ways to solve those
problems, but I didn't come up with anything satisfying for EXPLAIN. (One
dead-end thought was to introduce an ExprShortcut node having "Node
*semantics" and "Node *shortcut" fields, where "semantics" is deparsed for
EXPLAIN and "shortcut" is actually evaluated. That would require teaching
piles of code about the new node type, which isn't appropriate for the benefit
in question.)

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?

Thanks,
nm