Postgres 10: do rows automatically move between partitions?

Question

Assuming I have a parent table with child partitions that are created based on the value of a field.

If the value of that field changes, is there a way to have Postgres automatically move the row into the appropriate partition?

For example:

create table my_table(name text)
partition by list (left(name, 1));

create table my_table_a
partition of my_table
for values in ('a');

create table my_table_b
partition of my_table
for values in ('b');

In this case, if I change the value of name in a row from aaa to bbb, how can I get it to automatically move that row into my_table_b.

When I tried to do that, (i.e. update my_table set name = 'bbb' where name = 'aaa';), I get the following error:

ERROR: new row for relation "my_table_a" violates partition constraint

if you have rules configured (or trigger) on update - surely, why not?.. — Vao Tsun, Dec 29 '17 at 21:30

Vao Tsun · Answer 1 · 2017-12-30T07:46:23.997

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f0e44751d7175fa3394da2c8f85e3ceb3cdbfe63

it doesn't handle updates that cross partition boundaries.

thus you need to create one yourself... here's your set:

t=# insert into my_table select 'abc';
INSERT 0 1
t=# insert into my_table select 'bcd';
INSERT 0 1
t=# select tableoid::regclass,* from my_table;
  tableoid  | name
------------+------
 my_table_a | abc
 my_table_b | bcd
(2 rows)

here's rule and fn():

t=# create or replace function puf(_j json,_o text) returns void as $$
begin
 raise info '%',': '||left(_j->>'name',1);
  execute format('insert into %I select * from json_populate_record(null::my_table, %L)','my_table_'||left(_j->>'name',1), _j);
  execute format('delete from %I where name = %L','my_table_'||left(_o,1), _o);
end;
$$language plpgsql;
CREATE FUNCTION
t=# create rule psr AS ON update to my_table do instead select puf(row_to_json(n),OLD.name) from (select NEW.*) n;
CREATE RULE

here's update:

t=# update my_table set name = 'bbb' where name = 'abc';
INFO:  : b
 puf
-----

(1 row)

UPDATE 0

checking result:

t=# select tableoid::regclass,* from my_table;
  tableoid  | name
------------+------
 my_table_b | bcd
 my_table_b | bbb
(2 rows)

once again:

t=# update my_table set name = 'a1' where name = 'bcd';
INFO:  : a
 puf
-----

(1 row)

UPDATE 0
t=# select tableoid::regclass,* from my_table;
  tableoid  | name
------------+------
 my_table_a | a1
 my_table_b | bbb
(2 rows)

Of course using json to pass NEW record looks ugly. And it is ugly indeed. But I did not have time to study the new PARTITION feature of 10, so don't know the elegant way to do this task. Hopefully I could give the generic idea of how you can possible solve the problem and you will produce a better neat code.

update

its probablygood idea to limit such rule to ON update to my_table where left(NEW.name,1) <> left(OLD.name,1) do instead, to release the heavy manipulations need

Postgres 10: do rows automatically move between partitions?

1 Answers1