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/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
