pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Omar Kilani <omar.kilani@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Jim Nasby <Jim.Nasby@bluetreble.com>
Cc: Petr Jelinek <petr.jelinek@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Replication vs. float timestamps is a disaster
Date: Wed, 06 Sep 2017 18:55:08 -0400
Message-ID: <3192.1504738508@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

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



view thread (33+ messages)  latest in thread

Message-ID: <3192.1504738508@sss.pgh.pa.us>
Permalink:  ../3192.1504738508@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3192.1504738508@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, omar.kilani@gmail.com, andres@anarazel.de, jd@commandprompt.com, Jim.Nasby@bluetreble.com, petr.jelinek@2ndquadrant.com, robertmhaas@gmail.com
  Subject: Re: Replication vs. float timestamps is a disaster
  In-Reply-To: <3192.1504738508@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox