On Wed, 31 Oct 2018 at 08:29, Erik Rijkers <e...@xs4all.nl> wrote: > On 2018-10-31 09:15, Simon Riggs wrote: > > On Wed, 31 Oct 2018 at 07:58, Erikjan Rijkers <e...@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/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
