Обсуждение: Detecting renamed columns via pgouput in logical replication ?
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 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“
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:
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“David J.
Please don’t top-post.
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