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
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 |