Subject: Re: Replication vs. float timestamps is a disaster
To: Omar Kilani <omar.kilani@gmail.com>, Andres Freund <andres@anarazel.de>
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>
Cc: "Joshua D. Drake" <jd@commandprompt.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 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>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <59B0B51E.5020406@anastigmatix.net>
Date: Wed, 6 Sep 2017 22:55:26 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+8F9hh2eU45c=WxBSYxmGBGX8mM8QWGUTrsq0axfHcPz9F12Q@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 09/06/17 18:33, Omar Kilani wrote:

> Is there anything people using float datetimes can do that isn't a
> pg_dumpall | pg_restore to do a less painful update?
> 
> We have several TB of data still using float datetimes and I'm trying
> to figure out how we can move to 10 (currently on 9.6.x) without
> massive amounts of $ in duplicated hardware or downtime.

I ran into an analogous issue with endianness of PL/Java-defined datatypes,
and ended up devising a procedure [1] for treating the type one way on
output and another way on input, and then constructing an UPDATE query
that just assigns the column to itself using a function that's essentially
identity, but forces the output-input conversion (and doesn't look like
a simple identity function to the query optimizer, which otherwise might
optimize the whole update away).

While the details of that were specific to PL/Java and byte order,
something similar might work in your case if you can afford some period,
either just before or just after migration, when your normal workload
is blocked, long enough to run such updates on your several TB of data.

Or if that's too big a chunk of downtime, you might be able to add
a column in advance, of some user-defined type the same width as an
integer datetime, populate that in advance, migrate, then do a quick
catalog switcheroo of the column names and types. I've never done that,
so someone else might have comments on its feasibility or the best way
of doing it.

> 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

That does seem important to get the details on. If the integer datetime
column won't represent your stuff (and the too-large or too-small values
are necessary to represent), then some schema change might be necessary.
Or, if the outlying values were sentinel values of some kind (I wonder,
how else would you even have datetimes outside of the int64 range?),
maybe they can be replaced with others that fit.

-Chap

[1]: https://tada.github.io/pljava/use/byteordermigrate.html


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