Re: No-rewrite timestamp<->timestamptz conversions

On Tue, 26 Feb 2019 at 06:14, Noah Misch <noah(at)leadboat(dot)com> wrote:

> 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?
>

Looks good, would need docs.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services