Thread: logical replication issue
I am upgrading our existing 12.22 Aurora Postgresql clusters to 16.8 using: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.MajorVersionUpgrade.html
I have created a pub sub replication with a slot per database. I originally created 1 publication for each database for all tables. Due to this appeared issue, I have changed that method to creating 1 publication per schema. This allows limiting a smaller set of tables from the subscription.
Either way getting errors:
2025-04-16 18:47:11 UTC::@:[1633]:LOG: logical replication apply worker for subscription "clone_rep_slot_cdc_only_sub" has started
2025-04-16 18:47:12 UTC::@:[1633]:ERROR: could not identify an equality operator for type xml
2025-04-16 18:47:12 UTC::@:[1633]:CONTEXT: processing remote data for replication origin "pg_2141834" during message type "UPDATE" for replication target relation "sde.gdb_items" in transaction 151772192, finished at 0/61601498
2025-04-16 18:47:12 UTC::@:[569]:LOG: background worker "logical replication worker" (PID 1633) exited with exit code 1
It appears to be failing on the gdb_items table because there are columns of datatype xml. The only way I could get this to run was to exclude tables from the sde schema with datatype xml in the schema publication. Or remove the entire schema publication from the subscription. Once I remove the table(s) in question the slot becomes active.
Is this an undocumented limitation? I cannot find this limitation anywhere, in aws docs: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/blue-green-deployments-considerations.html#blue-green-deployments-limitations-postgres-logical
Nor in Postgresql version 16: https://www.postgresql.org/docs/16/logical-replication-restrictions.html
Should logical replication fail due to a column of type 'xml' in a table in a publication?
I assume this issue in replication is related to this excerpt from docs:
8.13.3. Accessing XML Values
The
xml data type is unusual in that it does not provide any comparison operators. This is because there is no well-defined and universally useful comparison algorithm for XML data. ...Mike L <giantmetfan@comcast.net> writes:
> Should logical replication fail due to a column of type 'xml' in a table
> in a publication?
Well, as you note, there's no equality operator for type xml.
Looking at the replication code, it looks like you might be able
to dodge the error if the replicated-to table has a primary key
(which'd necessarily not involve the xml column). Without a PK,
it will do full-row equality checks to determine tuple matches,
and that's where it falls down trying to compare the xml column.
regards, tom lane
Thanks Tom. These tables with this issue are related to Arcgis. I cannot modify existing tables by adding primary keys if it doesn't already have one.
Will have to exclude these tables from replication it appears.
Will have to exclude these tables from replication it appears.
On April 17, 2025 11:05:25 AM EDT, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mike L <giantmetfan@comcast.net> writes:Should logical replication fail due to a column of type 'xml' in a table
in a publication?
Well, as you note, there's no equality operator for type xml.
Looking at the replication code, it looks like you might be able
to dodge the error if the replicated-to table has a primary key
(which'd necessarily not involve the xml column). Without a PK,
it will do full-row equality checks to determine tuple matches,
and that's where it falls down trying to compare the xml column.
regards, tom lane
Mike L