Re: Floating-point timestamps versus Range Types

On Mon, 2010-10-18 at 14:06 -0400, Robert Haas wrote:
> Right. I think your argument that we should "do nothing" upthread is
> exactly right.

OK.

> A more interesting question is whether and how we can ease the
> migration path from float timestamps to integer timestamps. Even
> without range types, if someone does have a UNIQUE index on a
> timestamp column, could they get an error if they dump from a
> float-timestamp version of PG and restore onto an integer-timestamp
> version?

Yes. They could also get an error if they dump from a float-timestamp
version and restore into a float-timestamp version. [ That's because for
float-timestamps typinput(typoutput(VALUE)) may not equal VALUE. ]

> How would we recommend that they recover from that
> situation?

COPY the data out in binary mode, or they have already lost data (if
using float-timestamps).

A reasonable conversion path might be to offer integer timestamps using
a different type name (e.g. inttimestamp) that always means integer
timestamps. Then, they could convert using ALTER TABLE, then do an
in-place upgrade. We could even make pg_upgrade optionally convert
inttimestamp to timestamp in O(1) on an integer-timestamps build.

Regards,
Jeff Davis