Thread: BUG #15304: Problem when replicating Floating point timestamps toint64 timestamps
BUG #15304: Problem when replicating Floating point timestamps toint64 timestamps
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15304 Logged by: Vasilis Ventirozos Email address: v.ventirozos@gmail.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.1487455319%40sss.pgh.pa.us#26788.1487455319@sss.pgh.pa.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.
Re: BUG #15304: Problem when replicating Floating point timestampsto int64 timestamps
From
Alvaro Herrera
Date:
On 2018-Jul-27, PG Bug reporting form wrote: > 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.1487455319%40sss.pgh.pa.us#26788.1487455319@sss.pgh.pa.us > So I know that this is not something new, but i was wondering if there is > any way around this, As I recall, pglogical has a way to indicate transmission of data using the text representation rather than binary, which should fix this problem. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #15304: Problem when replicating Floating point timestamps toint64 timestamps
From
Vasilis Ventirozos
Date:
> On 27 Jul 2018, at 20:40, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > > On 2018-Jul-27, PG Bug reporting form wrote: > >> 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.1487455319%40sss.pgh.pa.us#26788.1487455319@sss.pgh.pa.us >> So I know that this is not something new, but i was wondering if there is >> any way around this, > > As I recall, pglogical has a way to indicate transmission of data using > the text representation rather than binary, which should fix this > problem. > > -- > Álvaro Herrera https://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services That worked. Thanks ! Vasilis Ventirozos OmniTI Computer Consulting Inc.