Re: Need help in transferring FP to Int64 DateTime

On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin <benson(dot)jin(at)troo(dot)com> wrote:

> I am sure this question has been asked before, however, I failed to find any
> related topics in the internet. We have a database about 100GB in size. It
> was started back in 7.x days and has been upgraded along the way to 9.0.
> Because of the historical reason, all timestamps are stored in
> FloatingPoint. To use pg9, we had to compile it with FP support instead of
> the default Int64.

I take it you had to compile 9.0 with --disable-integer-datetimes
because you wanted to use pg_upgrade to perform one of your database
upgrades, yes? Otherwise you would have been able to just
dump-and-restore into a 9.0 database with integer timestamps. At any
rate, you must have performed a dump-and-restore at some point since
your "7.x days", since pg_upgrade can handle databases only back to
8.3.

> Some recent research shows that PG team will eventually
> dump FP support in favor of int64 in future, we figured we need to make the
> move to Int64 before the database gets even larger.

FWIW, I don't think there's any urgent push to get rid of float
timestamps as a compile-time option, even though [1] claims the option
is "deprecated". Float timestamps were the default through 8.3, which
isn't quite ancient history yet, and it seems likely there are many
users in the same boat who would be upset about not being able to use
pg_upgrade if we removed that option.

Josh

[1] http://www.postgresql.org/docs/current/static/datatype-datetime.html