public inbox for [email protected]help / color / mirror / Atom feed
logical replication issue 3+ messages / 2 participants [nested] [flat]
* logical replication issue @ 2025-04-17 14:06 Mike L <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Mike L @ 2025-04-17 14:06 UTC (permalink / raw) To: Pgsql-admin <[email protected]>; pgsql-admin I am upgrading our existing 12.22 Aurora Postgresql clusters to 16.8 using:https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.MajorVersionUpgrade.ht... 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.h... 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. ... ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: logical replication issue @ 2025-04-17 15:05 Tom Lane <[email protected]> parent: Mike L <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2025-04-17 15:05 UTC (permalink / raw) To: Mike L <[email protected]>; +Cc: Pgsql-admin <[email protected]>; pgsql-admin Mike L <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: logical replication issue @ 2025-04-17 15:52 Mike L <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Mike L @ 2025-04-17 15:52 UTC (permalink / raw) To: [email protected] 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. On April 17, 2025 11:05:25 AM EDT, Tom Lane <[email protected]> wrote: >Mike L <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-04-17 15:52 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-17 14:06 logical replication issue Mike L <[email protected]> 2025-04-17 15:05 ` Tom Lane <[email protected]> 2025-04-17 15:52 ` Mike L <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox