pgsql: Allow UPDATE to move rows between partitions.
pgsql: Allow UPDATE to move rows between partitions.
От:
Robert Haas <rhaas@postgresql.org>
Дата:
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 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/2f178441044be430f6b4d626e4dae68a9a6f6cec Modified Files -------------- 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(-)
Re: pgsql: Allow UPDATE to move rows between partitions.
От:
Amit Kapila <amit.kapila16@gmail.com>
Дата:
On Sat, Jan 20, 2018 at 2:03 AM, Robert Haas wrote: > Allow UPDATE to move rows between partitions. > + If an UPDATE on a partitioned table causes a row to move + to another partition, it will be performed as a DELETE + from the original partition followed by an INSERT into + the new partition. In this case, all row-level BEFORE + UPDATE triggers and all row-level + BEFORE DELETE triggers are fired on + the original partition. Do we need to maintain triggers related behavior for logical replication? In logical replication, we use ExecSimpleRelationDelete to perform Delete operation which is not aware of this special behavior (execute before update trigger for this case). -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com