BUG #15304: Problem when replicating Floating point timestamps to int64 timestamps

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: v(dot)ventirozos(at)gmail(dot)com
Subject: BUG #15304: Problem when replicating Floating point timestamps to int64 timestamps
Date: 2018-07-27 17:31:02
Message-ID: 153271266275.1396.4515851245483780977@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15304
Logged by: Vasilis Ventirozos
Email address: v(dot)ventirozos(at)gmail(dot)com
PostgreSQL version: 9.6.3
Operating system: ubuntu xenial
Description:

Hey all,
While testing a migration procedure on a database (v9.6.9) from floating
point datetimes to int64 (v9.6.9) using pglogical i saw that after the
initial data sync all the subsequent replicated rows containing dates are
getting nonsensical datetime values in the subscriber.
I've read:
https://www.postgresql.org/message-id/flat/26788(dot)1487455319%40sss(dot)pgh(dot)pa(dot)us#26788(dot)1487455319(at)sss(dot)pgh(dot)pa(dot)us
So I know that this is not something new, but i was wondering if there is
any way around this, other than just use slony (that works fine). For
example if there was a pattern, i could possibly translate the "wrong" dates
on the subscriber using a trigger. An interesting observation is that date
'2000-01-01 00:00:00' was actually transferred fine, while '1999-01-01
00:00:00' made the sync worker to error out with "date out of range".

Some examples bellow :
id | mydate | should_be |
----+------------------------------+-------------------------------+
72 | 2018-01-01 00:00:00 | Initial row before replication |
73 | 2000-01-01 00:00:00 | 2000-01-01 00:00:00 |
74 | 151547-03-14 20:46:33.353216 | 2001-01-01 00:00:00 |
75 | 151689-07-17 15:26:49.024512 | 2002-01-01 00:00:00 |
76 | 151765-01-03 19:04:55.441408 | 2003-01-01 00:00:00 |
77 | 151832-01-28 12:43:50.545408 | 2004-01-01 00:00:00 |
78 | 151874-03-11 10:09:15.259904 | 2005-01-01 00:00:00 |
79 | 151907-09-22 18:58:42.811904 | 2006-01-01 00:00:00 |

Best Regards,
Vasilis Ventirozos
OmniTI Computer Consulting Inc.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2018-07-27 17:40:21 Re: BUG #15304: Problem when replicating Floating point timestamps to int64 timestamps
Previous Message John Daniel 2018-07-27 15:48:02 Re: BUG #15300: "do you want the application "postgres" to accept incoming network connections" dialog box madness