On Tue, 26 Feb 2019 at 06:14, Noah Misch <n...@leadboat.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
