Re: [HACKERS] generated columns

On 2018-10-31 09:15, Simon Riggs wrote:
> On Wed, 31 Oct 2018 at 07:58, Erikjan Rijkers <er(at)xs4all(dot)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