pgsql: Add UPDATE/DELETE FOR PORTION OF

Add UPDATE/DELETE FOR PORTION OF

This is an extension of the UPDATE and DELETE commands to do a
"temporal update/delete" based on a range or multirange column. The
user can say UPDATE t FOR PORTION OF valid_at FROM '2001-01-01' TO
'2002-01-01' SET ... (or likewise with DELETE) where valid_at is a
range or multirange column.

The command is automatically limited to rows overlapping the targeted
portion, and only history within those bounds is changed. If a row
represents history partly inside and partly outside the bounds, then
the command truncates the row's application time to fit within the
targeted portion, then it inserts one or more "temporal leftovers":
new rows containing all the original values, except with the
application-time column changed to only represent the untouched part
of history.

To compute the temporal leftovers that are required, we use the *_minus_multi
set-returning functions defined in 5eed8ce50c.

- Added bison support for FOR PORTION OF syntax. The bounds must be
constant, so we forbid column references, subqueries, etc. We do
accept functions like NOW().
- Added logic to executor to insert new rows for the "temporal
leftover" part of a record touched by a FOR PORTION OF query.
- Documented FOR PORTION OF.
- Added tests.

Author: Paul A. Jungwirth <pj(at)illuminatedcomputing(dot)com>
Reviewed-by: Peter Eisentraut <peter(at)eisentraut(dot)org>
Discussion: https://www.postgresql.org/message-id/flat/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/8e72d914c52876525a90b28444453de8085c866f

Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 45 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 34 +
contrib/test_decoding/expected/ddl.out | 52 +
contrib/test_decoding/sql/ddl.sql | 30 +
doc/src/sgml/dml.sgml | 135 ++
doc/src/sgml/glossary.sgml | 15 +
doc/src/sgml/images/Makefile | 4 +-
doc/src/sgml/images/meson.build | 2 +
doc/src/sgml/images/temporal-delete.svg | 41 +
doc/src/sgml/images/temporal-delete.txt | 10 +
doc/src/sgml/images/temporal-update.svg | 45 +
doc/src/sgml/images/temporal-update.txt | 10 +
doc/src/sgml/ref/create_publication.sgml | 6 +
doc/src/sgml/ref/delete.sgml | 108 +-
doc/src/sgml/ref/update.sgml | 112 +-
doc/src/sgml/trigger.sgml | 12 +
src/backend/executor/execMain.c | 1 +
src/backend/executor/nodeModifyTable.c | 350 +++-
src/backend/nodes/nodeFuncs.c | 33 +
src/backend/optimizer/plan/createplan.c | 6 +-
src/backend/optimizer/plan/planner.c | 1 +
src/backend/optimizer/util/pathnode.c | 3 +-
src/backend/parser/analyze.c | 357 +++-
src/backend/parser/gram.y | 111 +-
src/backend/parser/parse_agg.c | 10 +
src/backend/parser/parse_collate.c | 1 +
src/backend/parser/parse_expr.c | 8 +
src/backend/parser/parse_func.c | 3 +
src/backend/parser/parse_merge.c | 2 +-
src/backend/rewrite/rewriteHandler.c | 75 +-
src/backend/utils/adt/ruleutils.c | 41 +
src/backend/utils/cache/lsyscache.c | 25 +
src/include/nodes/execnodes.h | 22 +
src/include/nodes/parsenodes.h | 21 +
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 2 +
src/include/nodes/primnodes.h | 35 +
src/include/optimizer/pathnode.h | 2 +-
src/include/parser/analyze.h | 3 +-
src/include/parser/kwlist.h | 1 +
src/include/parser/parse_node.h | 1 +
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/for_portion_of.out | 2100 ++++++++++++++++++++++++
src/test/regress/expected/privileges.out | 28 +
src/test/regress/expected/updatable_views.out | 32 +
src/test/regress/expected/without_overlaps.out | 245 ++-
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/for_portion_of.sql | 1368 +++++++++++++++
src/test/regress/sql/privileges.sql | 27 +
src/test/regress/sql/updatable_views.sql | 14 +
src/test/regress/sql/without_overlaps.sql | 120 +-
src/test/subscription/t/034_temporal.pl | 100 +-
src/tools/pgindent/typedefs.list | 3 +
53 files changed, 5728 insertions(+), 88 deletions(-)