Message-ID: <196cd52eacfb03ec788cadc4a8bf02c83207d9aa.camel@cybertec.at>
Subject: Re: Regarding logical replication issues with PostgreSQL versions
 16 and above
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: yexiu-glory <yexiu-glory@qq.com>, pgsql-general
	 <pgsql-general@lists.postgresql.org>
Date: Fri, 18 Jul 2025 15:40:06 +0200
In-Reply-To: <tencent_2EDF6F264A6F6C92BE540FC63078544EDF08@qq.com>
References: <tencent_2EDF6F264A6F6C92BE540FC63078544EDF08@qq.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/196cd52eacfb03ec788cadc4a8bf02c83207d9aa.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-07-18 at 19:08 +0800, yexiu-glory wrote:
> I'm facing a problem here: our business requires logical data replication=
 to other
> departments, but at the same time, sensitive fields need to be filtered o=
ut.
> Therefore, we used the column filtering function when creating logical re=
plication.
> If we use `alter table table1 replica identity default;`, there is no iss=
ue at all.
> However, when encountering new business scenarios, we need to use the CSI=
 columnar
> index function. Under the default setting, the synchronization delay of t=
he CSI
> columnar index is severe. Therefore, we need to use `alter table table1 r=
eplica
> identity full;` so that the CSI columnar index can perform data compariso=
n and only
> convert changed data when receiving data, thereby speeding up the data co=
nversion
> efficiency of the CSI columnar index. However, after using `alter table t=
able1
> replica identity full;`, updating the table will result in an error.
> ERROR: cannot update table "table1"
> DETAIL: The column list used by the publication does not cover the replic=
a identity.
> So, is there any solution to this problem, or is there a better approach =
to
> accelerate the conversion efficiency of the CSI columnar index.

I cannot help with that CSI thing.

The only workaround I can think of is to create an index on the publisher t=
hat
contains all the columns you need in the replica identity and then using
that with ALTER TABLE ... REPLICA IDENTITY USING INDEX ...

Yours,
Laurenz Albe