Re: relcache reference leak with pglogical replication to insert-only partitioned table?
On Sunday, 27 January 2019 11:20:03 UTC+8, Jeremy Finzel wrote: > > I understand it's not fully supported to replicate to a differently > partitioned setup on a subscriber with either pglogical or the native > logical replication, however I also know that INSERT triggers can be fired > in replication mode. I have an insert-only OLTP table that I want > partitioned only on the subscriber system. I have this setup using the > "old style" partitioning as it is a 9.6 system. > > Provider is 9.6.6 pglogical 2.1.1 > Subscriber is 9.6.10 pglogical 2.1.1 > > Everything appears good as far as the data. It is partitioning > correctly. Queries on the data are planning correctly. However, I am now > getting these WARNING messages constantly. How concerned should I be? Is > there a fix for this? Any insight is much appreciated! > > 2019-01-27 03:12:34.150 GMT,,,135600,,5c4d1f44.211b0,6794,,2019-01-27 > 03:02:28 GMT,54/0,1057372660,WARNING,01000,"relcache reference leak: > relation ""foo_pkey"" not closed","apply COMMIT in commit before > 14DB/34DB1B78, xid 1476598649 commited at 2019-01-26 21:12:34.071673-06 > (action #10) from node replorigin 22""pglogical apply 16420:2094659706" > It won't corrupt anything but you can expect resource leaks. I think there are changes to support this in pglogical3. I don't have any new information on when they might become public, but I do know work has been done to add a plugin mechanism etc as part of work toward opening pglogical3.
Re: Implicit timezone conversion replicating from timestamp to timestamptz?
On Friday, 25 January 2019 04:57:15 UTC+8, Jeremy Finzel wrote: > > We are working to migrate several large tables from the timestamp to the > timestamptz data type by using logical replication (so as to avoid long > downtime for type conversions). We are using pglogical but curious if what > I share below applies to native logical replication as well. > > Both source and destination dbs are at localtime, which is > 'America/Chicago' time zone. > > The source system has a timestamp stored "at time zone UTC", like this for > 6:00pm Chicago time: > 2019-01-24 20:00:00.00 > > I was *very surprised* to find that replicating above timestamp to > timestamptz actually does so correctly, showing this value in my psql > client on the subscriber: > 2019-01-24 14:00:00.00-06 > > How does it know/why does it assume it knows that the time zone of the > timestamp data type is UTC on the provider given that my clusters are at > America/Chicago? I would have actually expected an incorrect conversion of > the data unless I set the timezone to UTC on the way in on the subscriber > via a trigger. > > That is, I was expecting to see this: > 2019-01-24 20:00:00.00-06 > > Which is obviously wrong. So why does it do this and is there some > assumption being made somewhere in the code base that a timestamp is > actually saved "at time zone UTC"? > > pglogical is replicating the timestamp text, which is converted on both output and input.
