Re: No-rewrite timestamp<->timestamptz conversions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Noah Misch <noah(at)leadboat(dot)com>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: No-rewrite timestamp<->timestamptz conversions
Date: 2019-02-26 15:46:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2019-02-26 15:46:31 Re: Protect syscache from bloating with negative cache entries
Previous Message Michael Banck 2019-02-26 15:35:22 Re: Offline enabling/disabling of data checksums