Re: [HACKERS] generated columns

On Wed, 31 Oct 2018 at 08:29, Erik Rijkers <er(at)xs4all(dot)nl> wrote:

> 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
>

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