The last example in my original email showed this happening where one "AT TIME ZONE" is in a subquery and the other is in the outer query. Imagine that scaled up to a large, complex query; and imagine that it eventually grows a redundant "AT TIME ZONE" because someone modifying an outer query doesn't realize that a deep subquery is already doing "AT TIME ZONE". Now the time is suddenly in the wrong zone, but the programmer explicitly asked for the timestamp to be in UTC.
We can certainly call that a mistake in the query (it's redundant if nothing else). But I would never expect the value to switch back to local time when I add "AT TIME ZONE 'utc'".
Well, as demonstrated its not redundant, its not doing what you believe it should: which is that timestamptz AT TIME ZONE 'UTC' be idompotent - but since the time zone provided can be any timezone that is not realistic.
Frankly, AT TIME ZONE is useful to taking user input and appending a known time zone, that doesn't match the session TimeZone GUC, to it in order to create a timestamptz value. timestamptz AT TIME ZONE's usefulness is marginal at best - and in any case should not be used in subqueries. Pass the original timestamptz typed value around until you are ready to send it to the user. The query that operates at the edge can use AT TIME ZONE if desired though I'd suggest that "to_char()" is less likely to trip people up even if it is a bit more verbose.
That said, as your example shows, as long as both the AT TIME ZONE targets are UTC the round-trip property holds.
