Re: Followup Timestamp to timestamp with TZ conversion

On Thu, Jul 22, 2021 at 11:29 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> As a thought experiment to prove that this is an issue, suppose that
> somebody invented an unsigned integer type, and made the cast from
> regular int4 follow the rules of a C cast, so that e.g. -1 becomes
> 2^32-1. Given that, an ALTER TYPE from int4 to the unsigned type
> could skip the heap rewrite. But we absolutely would have to rebuild
> any btree index on the column, because the sort ordering of the two
> types is different. OTOH, it's quite likely that a hash index would
> not really need to be rebuilt. So this is a real can of worms and
> we've not cared to open it.

I agree that it doesn't follow in general. I think it does in the case
of timestamp and timestamptz, because I don't think either the choice
of time zone or the fact that we're reckoning relative to a time zone
can change which of two timestamps is considered earlier. However, I
think the only infrastructure we have for proving that is to look to
see whether it's the same operator family in both cases. Because
timestamp_ops and timestamptz_ops are separate, that doesn't help
here.

--
Robert Haas
EDB: http://www.enterprisedb.com