On Wed, 31 Oct 2018 at 08:29, Erik Rijkers <er@xs4all.nl> wrote:
On 2018-10-31 09:15, Simon Riggs wrote: > On Wed, 31 Oct 2018 at 07:58, Erikjan Rijkers <er@xs4all.nl> wrote: > > >> I have also noticed that logical replication isn't possible on tables >> with a generated column. That's a shame but I suppsoe that is as >> expected. >> > > Couldn't see anything like that in the patch. Presumably unintended > consequence. The generated value needs to be in WAL, so decoding it > should > be trivial. >
These log messages occur on attempting at logical replication:
( table t1 has no generated columns; replicates fine. table t2 has one generated column; replication fails: see below )
LOG: database system is ready to accept connections LOG: logical replication apply worker for subscription "sub1" has started LOG: logical replication table synchronization worker for subscription "sub1", table "t1" has started LOG: logical replication table synchronization worker for subscription "sub1", table "t2" has started LOG: logical replication table synchronization worker for subscription "sub1", table "t1" has finished ERROR: column "i2" is a generated column DETAIL: Generated columns cannot be used in COPY. LOG: background worker "logical replication worker" (PID 22252) exited with exit code 1
OK, so the problem is COPY.
Which means we have an issue with restore. We need to be able to pg_dump a table with generated columns, then restore it afterwards. More generally, we need to be able to handle data that has already been generated - the "generate" idea should apply to new data not existing data.
Sounds like we need to do an ALTER TABLE ... GENERATE ALWAYS after the table has been re-created and re-loaded, so that both logical replication and dump/restore would work.
--
Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
