Detecting renamed columns via pgouput in logical replication ?

Hello,

re-posting question to the dev mailing list, since no reply received in the
general mailing list. This mailing list also seems a better place for this
question. See below for the original mail.

thx

---------- Forwarded message ---------
From: Andreas Andreakis <andreas.andreakis@gmail.com>
Date: Thu, Apr 16, 2020 at 9:22 PM
Subject: Detecting renamed columns via pgouput in logical replication ?
To: <pgsql-general@lists.postgresql.org>

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

On Sunday, April 26, 2020, Andreas Andreakis <andreas.andreakis@gmail.com>
wrote:

Hello,

re-posting question to the dev mailing list, since no reply received in
the general mailing list. This mailing list also seems a better place for
this question. See below for the original mail.

Actually this is probably the worse possible list to choose. It would be
better to simply ping the original post.

David J.

Hi Andreas,

On Sun, Apr 26, 2020 at 10:40:08PM -0700, Andreas Andreakis wrote:

re-posting question to the dev mailing list, since no reply received in the
general mailing list. This mailing list also seems a better place for this
question. See below for the original mail.

pgsql-committers is used as a mailing list to send automatically posts
when a commit to the central repository happens. What you may be
looking for here is pgsql-hackers:
https://www.postgresql.org/list/pgsql-hackers/
--
Michael

On Thursday, April 16, 2020, Andreas Andreakis <andreas.andreakis@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.

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.johnston@gmail.com> wrote:

Please don’t top-post.

On Monday, April 27, 2020, Andreas Andreakis <andreas.andreakis@gmail.com>
wrote:

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.

Correct. Maybe you want event triggers?

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

Correct

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 ?

You could search the mailing list archives for recent discussions but given
the general lack fo response to your email I’d say probably not. Decent
odds if someone was they would have replied to your email.

David J.

On Monday, April 27, 2020, David G. Johnston <david.g.johnston@gmail.com>
wrote:.

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 ?

You could search the mailing list archives for recent discussions but
given the general lack fo response to your email I’d say probably not.
Decent odds if someone was they would have replied to your email

I forgot, there is also a commitfest website for the project you could
peruse.

David J.

On 2020-Apr-27, Andreas Andreakis wrote:

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 ?

There aren't any plans currently that I am aware of. There was a
project to implement this a few years ago (part of which was mine) but
it's not complete and I haven't heard of anyone working on completing
it.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services