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