From: Tom Lane <tgl@sss.pgh.pa.us>
To: Omar Kilani <omar.kilani@gmail.com>
cc: Andres Freund <andres@anarazel.de>,
        "Joshua D. Drake" <jd@commandprompt.com>,
        Jim Nasby <Jim.Nasby@bluetreble.com>,
        Petr Jelinek <petr.jelinek@2ndquadrant.com>,
        Robert Haas <robertmhaas@gmail.com>,
        "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Replication vs. float timestamps is a disaster
In-reply-to: 
 <CA+8F9hh2eU45c=WxBSYxmGBGX8mM8QWGUTrsq0axfHcPz9F12Q@mail.gmail.com>
References: <1691.1487519369@sss.pgh.pa.us>
 <20170220070311.gscn7ppqbwlgdpjq@alap3.anarazel.de>
 <3bd3c551-8b23-45c8-27a2-97b5a922b130@2ndquadrant.com>
 <20170220110456.2jupmfhxlizykmmc@alap3.anarazel.de>
 <fece0003-6ede-ca8b-8f26-3c6fa899583d@BlueTreble.com>
 <20170222075345.mbzrmdaetdqfgitk@alap3.anarazel.de>
 <21041.1487771008@sss.pgh.pa.us>
 <20170222135634.gnlqu6by2xbpvwii@alap3.anarazel.de>
 <21225.1487803552@sss.pgh.pa.us>
 <8f819ff5-0162-f382-72d1-da565ceb4125@commandprompt.com>
 <20170228011332.afd5x35pftvwkxbd@alap3.anarazel.de>
 <CA+8F9hh2eU45c=WxBSYxmGBGX8mM8QWGUTrsq0axfHcPz9F12Q@mail.gmail.com>
Comments: In-reply-to Omar Kilani <omar.kilani@gmail.com>
	message dated "Wed, 06 Sep 2017 15:33:56 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3191.1504738508.1@sss.pgh.pa.us>
Date: Wed, 06 Sep 2017 18:55:08 -0400
Message-ID: <3192.1504738508@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

Omar Kilani <omar.kilani@gmail.com> writes:
> Is there anything people using float datetimes can do that isn't a
> pg_dumpall | pg_restore to do a less painful update?

Um, not really.  You may be stuck on 9.6 until you can spare the effort
to convert.  The physical representations of timestamps are totally
different in the two cases.

> I did attempt a pg_dumpall | pg_restore at one point but for whatever
> reason we had data in tables that integer datetimes fails on (I forget
> the exact crash, but the datetime values were either too small or too
> large to fit into the integer datetimes field -- I can retry this if
> it would be helpful).

I'm pretty sure the minimum values are the same in both cases, to wit
Julian day zero.  As for the max, according to the old code comments

 * The upper limit for dates is 5874897-12-31, which is a bit less than what
 * the Julian-date code can allow.  We use that same limit for timestamps when
 * using floating-point timestamps ... For integer timestamps, the upper
 * limit is 294276-12-31.

I would hope that any timestamps you've got beyond 294276AD are erroneous
data that you need to clean up anyway.

			regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers