No-op updates with partitioning and logical replication started failing in version 13

From: Brad Nicholson <brad(dot)nicholson(at)instacart(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: No-op updates with partitioning and logical replication started failing in version 13
Date: 2022-08-03 20:53:22
Message-ID: CAMMnM=8i5DohH=YKzV0_wYuYSYvuOJoL9F5nzXTc+yzsG1f6rg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

We've hit an interesting change with table partitioning and logical
replication that was introduced in Postgres 13. I've tested this on PG
14.4, 13.7, 12.11, 11.16 and 10.18. These are brew installed binaries on
OSX, but we've also seen this on other platforms.

The error happens under the following conditions:
- parent table does not have a primary key
- parent table is part of a logical replication publication
- a no-op update that does not include the partition key is run against the
parent.

In Postgres versions < 13, the update succeeds with UPDATE 0.

In Postgres versions >= 13, it fails with:

ERROR: cannot update table "t1" because it does not have a replica
identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

Here is a self contained test case:

create table t1 (id int, created_at timestamp, dat varchar) partition by
range (created_at);
create table t1_child partition of t1 for values from ( '2022-01-01
00:00:00') to ('2022-01-31 00:00:00');
insert into t1 (id, dat,created_at) values (1, 'test', '2022-01-02
00:00:00');
create publication test_pub for all tables;
update t1 set dat = 'foo1' where id = 1 and 1=0;

Worth mentioning, the 1=0 is a Rails thing.

I would expect to see an UPDATE 0 in the newer versions instead of the
failure.

Thanks,
Brad

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Martijn van Oosterhout 2022-08-03 21:12:11 Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY
Previous Message Tom Lane 2022-08-03 19:50:20 Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY