PostgreSQL 16 bug feedback
I encountered a problem in PostgreSQL 16:
In db1, there is a user table with fields id, name, phone, and createtime
db2 replicates the user table from db1 through logical replication, specifying the fields as id, name, and createtime
Then, in db1, perform the following operation: alter table user replica identity full;
Then, modifying or deleting a record in the user table will result in an error,
The error message for modification is as follows, and similar errors also occur when deleting.
update "public"."user" set name='aaa’where id = 20005
>ERROR: cannot update table "user"DETAIL: Column list used by the publication does not cover the replica identity.
On Thursday, July 17, 2025, yexiu-glory <yexiu-glory@qq.com> wrote:
I encountered a problem in PostgreSQL 16:
In db1, there is a user table with fields id, name, phone, and createtime
db2 replicates the user table from db1 through logical replication,
specifying the fields as id, name, and createtime
Then, in db1, perform the following operation: alter table user replica
identity full;
Then, modifying or deleting a record in the user table will result in an
error,
The error message for modification is as follows, and similar errors also
occur when deleting.
update "public"."user" set name='aaa’where id = 20005ERROR: cannot update table "user"DETAIL: Column list used by the
publication does not cover the replica identity.
What did you expect to happen?
David J.
On Thu, 2025-07-17 at 19:41 +0800, yexiu-glory wrote:
I encountered a problem in PostgreSQL 16:
In db1, there is a user table with fields id, name, phone, and createtime
db2 replicates the user table from db1 through logical replication, specifying the fields as id, name, and createtime
Then, in db1, perform the following operation: alter table user replica identity full;
Then, modifying or deleting a record in the user table will result in an error,
The error message for modification is as follows, and similar errors also occur when deleting.
update "public"."user" set name='aaa’where id = 20005ERROR: cannot update table "user"DETAIL: Column list used by the publication does not cover the replica identity.
That's not a bug.
To make that replication work, you must have excluded the column "phone" from
the list of published columns. So it cannot be a part of the replica identity,
the set of columns used in the WHERE condition on the subscriber.
Stick with REPLICA IDENTITY DEFAULT.
Yours,
Laurenz Albe
发件人：David G. Johnston <david.g.johnston@gmail.com>
发件时间：2025年7月18日 01:56
收件人：yexiu-glory <yexiu-glory@qq.com>
主题：Re: PostgreSQL 16 bug feedback
Keep replies on-list. You told the system “phone” is part of the row identifier and the subscriber doesn’t have the phone column, there is no way for the subscriber to succeed. You will get a failure with your intended setup, the only real question is when.
David J.
On Thursday, July 17, 2025, yexiu-glory <yexiu-glory@qq.com> wrote:
I hope there will be no errors and it can be updated and deleted normally.
---原始邮件---
发件人:"David G. Johnston"
发送时间:2025-07-17 23:54:17
收件人:"yexiu-glory";
主题:Re: PostgreSQL 16 bug feedback
On Thursday, July 17, 2025, yexiu-glory <yexiu-glory@qq.com> wrote:
I encountered a problem in PostgreSQL 16:
In db1, there is a user table with fields id, name, phone, and createtime
db2 replicates the user table from db1 through logical replication, specifying the fields as id, name, and createtime
Then, in db1, perform the following operation: alter table user replica identity full;
Then, modifying or deleting a record in the user table will result in an error,
The error message for modification is as follows, and similar errors also occur when deleting.
update "public"."user" set name='aaa’where id = 20005
>ERROR: cannot update table "user"DETAIL: Column list used by the publication does not cover the replica identity.
What did you expect to happen?
David J.
I think it would be better if, when using the command "alter table user replica identity full" and specifying columns, the full-state synchronization should also synchronize all the specified fields?
Import Notes
Reply to msg id not found: CAKFQuwY9E14byxUHTqKL_isn+um91ymnFBoNv8NPVs5L7MgNvg@mail.gmail.com
On Thu, 2025-07-18 at 00:21, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Thu, 2025-07-17 at 19:41 +0800, yexiu-glory wrote:
> > I encountered a problem in PostgreSQL 16:
> > In db1, there is a user table with fields id, name, phone, and createtime
> > db2 replicates the user table from db1 through logical replication, specifying the fields as id, name, and createtime
> > Then, in db1, perform the following operation: alter table user replica identity full;
> > Then, modifying or deleting a record in the user table will result in an error,
> > The error message for modification is as follows, and similar errors also occur when deleting.
> > update "public"."user" set name='aaa’where id = 20005
> > > ERROR: cannot update table "user"DETAIL: Column list used by the publication does not cover the replica identity.
>
> That's not a bug.
> To make that replication work, you must have excluded the column "phone" from
> the list of published columns. So it cannot be a part of the replica identity,
> the set of columns used in the WHERE condition on the subscriber.
>
> Stick with REPLICA IDENTITY DEFAULT.
>
I think it would be better if, when using the command "alter table user replica identity full" and specifying columns, the full-state synchronization should also synchronize all the specified fields?
On Thursday, July 17, 2025, yexiu-glory <yexiu-glory@qq.com> wrote:
On Thursday, July 17, 2025, yexiu-glory <*yexiu-glory@qq.com
<yexiu-glory@qq.com>*> wrote:I encountered a problem in PostgreSQL 16:
In db1, there is a user table with fields id, name, phone, and createtime
db2 replicates the user table from db1 through logical replication,
specifying the fields as id, name, and createtime
Then, in db1, perform the following operation: alter table user replica
identity full;
Then, modifying or deleting a record in the user table will result in an
error,
The error message for modification is as follows, and similar errors also
occur when deleting.
update "public"."user" set name='aaa’where id = 20005ERROR: cannot update table "user"DETAIL: Column list used by the
publication does not cover the replica identity.
What did you expect to happen?
I think it would be better if, when using the command "alter table user
replica identity full" and specifying columns, the full-state
synchronization should also synchronize all the specified fields?
Unless you are planning to write said patch this discussion seems quite
off-topic for -hackers at this time. I have my qualms about the mechanics
of some of this but am fine with the fact your sequence of actions have
left the system unable to publish update or deletes on the channel and that
it requires user intervention to change that.
If you wish to continue pondering this I’d suggest you post a message to
the -general mailing list and include why it would be worth the effort to
do something different here. Your toy example demonstrating behaviors
provides little insight as to why this is important. And it most
definitely does not demonstrate a bug - the docs do cover this, though I
admit not in a way I find particularly easy to learn from.
David J.