Detecting renamed columns via pgouput in logical replication ?

[Andreas Andreakis]

Hello,

when using Postgres 10 or higher, it seems that pgoutput can be used as an
output plugin for logical replication.

Does this allow to detect column renames ? Or is there a ticket for adding
support if the feature does not exist (if it is feasible to implement) ?

https://www.postgresql.org/docs/10/protocol-logicalrep-message-formats.html
should
be the format spec of pgoutput and it does not seem to contain sufficient
information for detecting renames. Also checked for Postgres version 11 and
12.

What I was hoping to find is the ordinal position of columns and use that
to infer column renames. As new columns always get a higher ordinal
position and renamed columns keep their position. Hence, a column rename
could be detected if different column names are received for the same
ordinal position, by tracking the column name per ordinal position at the
consumer. (Please let me know if any of that is incorrect)

cheers

Re: Detecting renamed columns via pgouput in logical replication ?

[Andreas Andreakis]

Thx for replying David.

According to
https://www.postgresql.org/docs/10/protocol-logicalrep-message-formats.html it
looks like certain schema information is embedded via pgoutput such as
column names and types. However, if a new column name appears and a
previous not, then it is currently not possible to differentiate if a
column was renamed VS if a new column was added and an old dropped.

There are additional schema changes that are currently not covered, such as
changes in default values.

Are there plans to add comprehensive schema change detection abilities via
logical replication ? either by embedding more information into pgoutput or
perhaps by embedding the schema DDLs ?

cheers





On Sun, Apr 26, 2020 at 11:07 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thursday, April 16, 2020, Andreas Andreakis <
> andreas.andrea...@gmail.com> wrote:
>>
>>
>> Does this allow to detect column renames ?
>>
>
> “The database schema and DDL commands are not replicated“
>
>
> https://www.postgresql.org/docs/current/logical-replication-restrictions.html
>
> David J.
>