Re: [HACKERS] generated columns

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



> Virtual columns wouldn't need to be replicated.
> 
> I guess we might choose to replicate generated cols as a value, or 
> leave
> them out and let them be generated on the downstream side. The default
> should be to just treat them as a value.
> 
> --
> Simon Riggs                http://www.2ndQuadrant.com/
> <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services