Waiting for PostgreSQL 11 – Allow UPDATE to move rows between partitions.

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.