From: Chapman Flack <chap@anastigmatix.net>
To: Omar Kilani <omar.kilani@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
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, 6 Sep 2017 22:55:26 -0400
Message-ID: <59B0B51E.5020406@anastigmatix.net> (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>
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
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: chap@anastigmatix.net, omar.kilani@gmail.com, andres@anarazel.de, jd@commandprompt.com, tgl@sss.pgh.pa.us, Jim.Nasby@bluetreble.com, petr.jelinek@2ndquadrant.com, robertmhaas@gmail.com
Subject: Re: Replication vs. float timestamps is a disaster
In-Reply-To: <59B0B51E.5020406@anastigmatix.net>
* 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