Allow UPDATE to move rows between partitions

Enterprise / PostgreSQL - Robert Haas [postgresql.org] - 19 January 2018 20:33 EST

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

2f17844104 Allow UPDATE to move rows between partitions.
contrib/file_fdw/input/file_fdw.source | 1 +
contrib/file_fdw/output/file_fdw.source | 2 +
doc/src/sgml/ddl.sgml | 24 +-
doc/src/sgml/ref/update.sgml | 13 +-
doc/src/sgml/trigger.sgml | 23 ++
src/backend/commands/copy.c | 40 +-
src/backend/commands/trigger.c | 52 ++-
src/backend/executor/execPartition.c | 241 ++++++++++-
src/backend/executor/nodeModifyTable.c | 583 +++++++++++++++++++++------
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 3 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/path/allpaths.c | 4 +-
src/backend/optimizer/plan/createplan.c | 4 +
src/backend/optimizer/plan/planner.c | 19 +-
src/backend/optimizer/prep/prepunion.c | 28 +-
src/backend/optimizer/util/pathnode.c | 4 +
src/include/executor/execPartition.h | 34 +-
src/include/nodes/execnodes.h | 4 +-
src/include/nodes/plannodes.h | 1 +
src/include/nodes/relation.h | 3 +
src/include/optimizer/pathnode.h | 1 +
src/include/optimizer/planner.h | 3 +-
src/test/regress/expected/update.out | 681 ++++++++++++++++++++++++++++++--
src/test/regress/sql/update.sql | 458 +++++++++++++++++++--
src/tools/pgindent/typedefs.list | 1 +
27 files changed, 1957 insertions(+), 274 deletions(-)

Upstream: git.postgresql.org


  • Share