On 19th of January 2018, Robert Haas committed patch:
Allow UPDATE to move rows between partitions. When an UPDATE causes a row to no longer match the partition constraint, try to move it to a different partition where it does match the partition constraint. In essence, the UPDATE is split into a DELETE from the old partition and an INSERT into the new one. This can lead to surprising behavior in concurrency scenarios because EvalPlanQual rechecks won't work as they normally did; the known problems are documented. (There is a pending patch to improve the situation further, but it needs more review.) Amit Khandekar, reviewed and tested by Amit Langote, David Rowley, Rajkumar Raghuwanshi, Dilip Kumar, Amul Sul, Thomas Munro, Álvaro Herrera, Amit Kapila, and me. A few final revisions by me. Discussion: http://postgr.es/m/CAJ3gD9do9o2ccQ7j7+tSgiE1REY65XRiMb=yJO3u3QhyP8EEPQ@mail.gmail.com
This is interesting, because it fixes certain case which didn't work previously.
Let's assume we have simple test table, partitioned by id range:
$ CREATE TABLE test ( id serial NOT NULL, text_column text NOT NULL ) PARTITION BY RANGE ( id ); CREATE TABLE $ CREATE TABLE test_0 partition OF test (PRIMARY KEY (id)) FOR VALUES FROM (0) TO (9); CREATE TABLE $ CREATE TABLE test_1 partition OF test (PRIMARY KEY (id)) FOR VALUES FROM (10) TO (19); CREATE TABLE $ CREATE TABLE test_2 partition OF test (PRIMARY KEY (id)) FOR VALUES FROM (20) TO (29); CREATE TABLE
Now, let's insert some sample records:
$ \copy test (text_column) FROM stdin; busily folds rooked pangs spools urging guilds permed billy mirth \. COPY 10
In here, we will have single row in test_1:
$ SELECT * FROM test_1; id | text_column ----+------------- 10 | mirth (1 ROW)
I can (and could):
$ UPDATE test_1 SET id = 11 WHERE id = 10; UPDATE 1 $ UPDATE test SET id = 12 WHERE id = 11; UPDATE 1
But, I couldn't, with update, change partition:
$ UPDATE test SET id = 20 WHERE id = 12; ERROR: NEW ROW FOR relation "test_1" violates partition CONSTRAINT DETAIL: Failing ROW contains (20, mirth). $ UPDATE test_1 SET id = 20 WHERE id = 12; ERROR: NEW ROW FOR relation "test_1" violates partition CONSTRAINT DETAIL: Failing ROW contains (20, mirth).
With this new change, though:
$ UPDATE test SET id = 20 WHERE id = 12; UPDATE 1
I still can't update within partition:
$ UPDATE test_2 SET id = 18 WHERE id = 20; ERROR: NEW ROW FOR relation "test_2" violates partition CONSTRAINT DETAIL: Failing ROW contains (18, mirth).
But this is (in my opinion) understandable.
That's great addition that will make partitioning even nicer to use, thanks to all involved.