Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5Pt6-00HXo6-FW for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 14:06:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u5Pt3-008ope-JS for pgsql-admin@arkaria.postgresql.org; Thu, 17 Apr 2025 14:06:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u5Pt2-008ocR-Ga for pgsql-admin@lists.postgresql.org; Thu, 17 Apr 2025 14:06:42 +0000 Received: from resdmta-h2p-547626.sys.comcast.net ([2001:558:fd02:2446::e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u5Psx-000a0V-1r for pgsql-admin@lists.postgresql.org; Thu, 17 Apr 2025 14:06:40 +0000 Received: from resomta-h2p-554996.sys.comcast.net ([96.102.179.208]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 256/256 bits) (Client did not present a certificate) by resdmta-h2p-547626.sys.comcast.net with ESMTPS id 5OvtuqD7qhRcK5Psrui9R2; Thu, 17 Apr 2025 14:06:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1744898789; bh=OKxu/n4tiYhDP8XR64l8WXrrEOozSVc3FtrXhNwp16s=; h=Received:Received:Content-Type:Message-ID:Date:MIME-Version:To: From:Subject:Xfinity-Spam-Result; b=DL9mtETAmhXVNI1xtgtslcJRLjcURmo43b5cD/SUAHBQ9Q1Ry379PdV6AhVU+BrUD V89P1S3QZnbB7GGB4vBL8pH40WqYY9PfAdeIKO3e40rPfMXct3zAA3YR/jSUX7HUB6 rnvxRoDXwZ9Ydxx5CO/iCqLBtxRnP5QupbW41HrhfHhWcH+NZXt7qhhptHxECpcTw3 wQN+oxtkTD1dw5YNq67nqafIMKJkQP/tDRfh8RGVMgSTR8/Jw33yCsVof9a2vFO/t0 FB1Rd/rAckogk3x1VA3NxbNaC1koiq6Efmyx7NBf4rcTjtLSEJYwkDeV4rO4b+VUu4 M6kiNC3+MK9hg== Received: from [172.16.200.7] ([45.144.113.103]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 256/256 bits) (Client did not present a certificate) by resomta-h2p-554996.sys.comcast.net with ESMTPSA id 5Psmuo2S4tkgz5PspuKI3Y; Thu, 17 Apr 2025 14:06:28 +0000 Content-Type: multipart/alternative; boundary="------------XPCXb6qiJ0W3yffnUVr5o2My" Message-ID: Date: Thu, 17 Apr 2025 10:06:24 -0400 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US To: Pgsql-admin , pgsql-admin From: Mike L Subject: logical replication issue X-CMAE-Envelope: MS4xfP+qFVm8KUt32eOU9mz+6HHs6W31QKzFfv45dNK6W2VRSbsEhVyTGAthT4prf+RAqI4KINOYPwvPPEm/O6g1SyJGW/hQNpExhX+bzUMlsaGpU6TsP913 O0ZvW9Mq2+5X5k+MkOSdeF3N6s5EAXJyw9hd4ugg5x9Cb9OSGh3HSO54E1GeVSn3iu+NRUkDUI9ogZqDN4cvj7bITvDuOWwdf9DiSia3/2sQ5583b56PJ1xw Gr4Wm/tkzeNMlo3GDt/7Pg== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------XPCXb6qiJ0W3yffnUVr5o2My Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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.  ... --------------XPCXb6qiJ0W3yffnUVr5o2My Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I am upgrading our existing 12.22 Aurora Postgresql clusters to 16.8 using:=C2=A0https://docs.aws= =2Eamazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.MajorVers= ionUpgrade.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.=C2=A0
Either way getting errors:=C2=A0
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=C2=A0
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=C2=A0
2025-04-16 18:47:12 UTC::@:[569]:LOG: background worker "logical replication worker" (PID 1633) exited with exit code 1=C2=A0

=C2=A0It 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.

=

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:
<= span style=3D"scrollbar-color: rgb(112, 112, 112) transparent; scrollbar-width= : thin; user-select: text !important; -webkit-tap-highlight-color: rgba(0= , 0, 0, 0); -webkit-user-drag: none; font-weight: initial;">8.13.3.=C2=A0= Accessing XML Values=C2=A0
The=C2=A0= = xml=C2=A0data 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.=C2=A0 ...



  


--------------XPCXb6qiJ0W3yffnUVr5o2My--