c3 text,
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
+CREATE TABLE "S 1"."T 5" (
+ c1 int4range NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 daterange NOT NULL,
+ CONSTRAINT t5_pkey PRIMARY KEY (c1, c4 WITHOUT OVERLAPS)
+);
-- Disable autovacuum for these tables to avoid unexpected effects of that
ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false');
+ALTER TABLE "S 1"."T 5" SET (autovacuum_enabled = 'false');
INSERT INTO "S 1"."T 1"
SELECT id,
id % 10,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 100) id;
DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests
+INSERT INTO "S 1"."T 5"
+ SELECT int4range(id, id + 1),
+ id + 1,
+ 'AAA' || to_char(id, 'FM000'),
+ '[2000-01-01,2020-01-01)'
+ FROM generate_series(1, 100) id;
ANALYZE "S 1"."T 1";
ANALYZE "S 1"."T 2";
ANALYZE "S 1"."T 3";
ANALYZE "S 1"."T 4";
+ANALYZE "S 1"."T 5";
-- ===================================================================
-- create foreign tables
-- ===================================================================
c2 int NOT NULL,
c3 text
) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4');
+CREATE FOREIGN TABLE ft8 (
+ c1 int4range NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 daterange NOT NULL
+) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 5');
-- ===================================================================
-- tests for validator
-- ===================================================================
public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') |
public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') |
public | ft7 | loopback3 | (schema_name 'S 1', table_name 'T 4') |
-(6 rows)
+ public | ft8 | loopback | (schema_name 'S 1', table_name 'T 5') |
+(7 rows)
-- Test that alteration of server options causes reconnection
-- Remote's errors might be non-English, so hide them to ensure stable results
ft2
(1 row)
+-- Test UPDATE FOR PORTION OF
+UPDATE ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01'
+SET c2 = c2 + 1
+WHERE c1 = '[1,2)';
+ERROR: foreign tables don't support FOR PORTION OF
+SELECT * FROM ft8 WHERE c1 = '[1,2)' ORDER BY c1, c4;
+ c1 | c2 | c3 | c4
+-------+----+--------+-------------------------
+ [1,2) | 2 | AAA001 | [01-01-2000,01-01-2020)
+(1 row)
+
+-- Test DELETE FOR PORTION OF
+DELETE FROM ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01'
+WHERE c1 = '[2,3)';
+ERROR: foreign tables don't support FOR PORTION OF
+SELECT * FROM ft8 WHERE c1 = '[2,3)' ORDER BY c1, c4;
+ c1 | c2 | c3 | c4
+-------+----+--------+-------------------------
+ [2,3) | 3 | AAA002 | [01-01-2000,01-01-2020)
+(1 row)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
c3 text,
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
+CREATE TABLE "S 1"."T 5" (
+ c1 int4range NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 daterange NOT NULL,
+ CONSTRAINT t5_pkey PRIMARY KEY (c1, c4 WITHOUT OVERLAPS)
+);
-- Disable autovacuum for these tables to avoid unexpected effects of that
ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false');
+ALTER TABLE "S 1"."T 5" SET (autovacuum_enabled = 'false');
INSERT INTO "S 1"."T 1"
SELECT id,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 100) id;
DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests
+INSERT INTO "S 1"."T 5"
+ SELECT int4range(id, id + 1),
+ id + 1,
+ 'AAA' || to_char(id, 'FM000'),
+ '[2000-01-01,2020-01-01)'
+ FROM generate_series(1, 100) id;
ANALYZE "S 1"."T 1";
ANALYZE "S 1"."T 2";
ANALYZE "S 1"."T 3";
ANALYZE "S 1"."T 4";
+ANALYZE "S 1"."T 5";
-- ===================================================================
-- create foreign tables
c3 text
) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4');
+CREATE FOREIGN TABLE ft8 (
+ c1 int4range NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 daterange NOT NULL
+) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 5');
+
+
-- ===================================================================
-- tests for validator
-- ===================================================================
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
+-- Test UPDATE FOR PORTION OF
+UPDATE ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01'
+SET c2 = c2 + 1
+WHERE c1 = '[1,2)';
+SELECT * FROM ft8 WHERE c1 = '[1,2)' ORDER BY c1, c4;
+
+-- Test DELETE FOR PORTION OF
+DELETE FROM ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01'
+WHERE c1 = '[2,3)';
+SELECT * FROM ft8 WHERE c1 = '[2,3)' ORDER BY c1, c4;
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
COMMIT
(33 rows)
+-- FOR PORTION OF setup
+CREATE TABLE replication_example_temporal(id int4range, valid_at daterange, somedata int, text varchar(120), PRIMARY KEY (id, valid_at WITHOUT OVERLAPS));
+INSERT INTO replication_example_temporal VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 1, 'aaa');
+INSERT INTO replication_example_temporal VALUES ('[2,3)', '[2000-01-01,2020-01-01)', 1, 'aaa');
+/* display results */
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+ data
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ BEGIN
+ table public.replication_example_temporal: INSERT: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2000,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa'
+ COMMIT
+ BEGIN
+ table public.replication_example_temporal: INSERT: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2000,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa'
+ COMMIT
+(6 rows)
+
+-- UPDATE FOR PORTION OF support
+BEGIN;
+ UPDATE replication_example_temporal
+ FOR PORTION OF valid_at FROM '2010-01-01' TO '2011-01-01'
+ SET somedata = 2,
+ text = 'bbb'
+ WHERE id = '[1,2)';
+COMMIT;
+/* display results */
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+ data
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ BEGIN
+ table public.replication_example_temporal: UPDATE: old-key: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2000,01-01-2020)' new-tuple: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2010,01-01-2011)' somedata[integer]:2 text[character varying]:'bbb'
+ table public.replication_example_temporal: INSERT: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2000,01-01-2010)' somedata[integer]:1 text[character varying]:'aaa'
+ table public.replication_example_temporal: INSERT: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2011,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa'
+ COMMIT
+(5 rows)
+
+-- DELETE FOR PORTION OF support
+BEGIN;
+ DELETE FROM replication_example_temporal
+ FOR PORTION OF valid_at FROM '2012-01-01' TO '2013-01-01'
+ WHERE id = '[2,3)';
+COMMIT;
+/* display results */
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+ data
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ BEGIN
+ table public.replication_example_temporal: DELETE: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2000,01-01-2020)'
+ table public.replication_example_temporal: INSERT: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2000,01-01-2012)' somedata[integer]:1 text[character varying]:'aaa'
+ table public.replication_example_temporal: INSERT: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2013,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa'
+ COMMIT
+(5 rows)
+
-- MERGE support
BEGIN;
MERGE INTO replication_example t
/* display results */
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+-- FOR PORTION OF setup
+CREATE TABLE replication_example_temporal(id int4range, valid_at daterange, somedata int, text varchar(120), PRIMARY KEY (id, valid_at WITHOUT OVERLAPS));
+INSERT INTO replication_example_temporal VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 1, 'aaa');
+INSERT INTO replication_example_temporal VALUES ('[2,3)', '[2000-01-01,2020-01-01)', 1, 'aaa');
+
+/* display results */
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+
+-- UPDATE FOR PORTION OF support
+BEGIN;
+ UPDATE replication_example_temporal
+ FOR PORTION OF valid_at FROM '2010-01-01' TO '2011-01-01'
+ SET somedata = 2,
+ text = 'bbb'
+ WHERE id = '[1,2)';
+COMMIT;
+
+/* display results */
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+
+-- DELETE FOR PORTION OF support
+BEGIN;
+ DELETE FROM replication_example_temporal
+ FOR PORTION OF valid_at FROM '2012-01-01' TO '2013-01-01'
+ WHERE id = '[2,3)';
+COMMIT;
+
+/* display results */
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+
-- MERGE support
BEGIN;
MERGE INTO replication_example t
</para>
</sect1>
+ <sect1 id="dml-application-time-update-delete">
+ <title>Updating and Deleting Temporal Data</title>
+
+ <para>
+ Special syntax is available to update and delete from application-time
+ temporal tables (see <xref linkend="ddl-application-time" />). (No extra
+ syntax is required to insert into them: the user just provides the
+ application time values like any other column.) When updating or deleting,
+ the user can target a specific portion of history. Only rows overlapping
+ that history are affected, and within those rows only the targeted history
+ is changed. If a row contains more history beyond what is targeted, its
+ application time is reduced to fit within the targeted portion, and new
+ rows are inserted to preserve the history that was not targeted.
+ </para>
+
+ <para>
+ Recall the example table from <xref linkend="temporal-entities-figure" />,
+ containing this data:
+
+<programlisting>
+ product_no | price | valid_at
+------------+-------+-------------------------
+ 5 | 5.00 | [2020-01-01,2022-01-01)
+ 5 | 8.00 | [2022-01-01,)
+ 6 | 9.00 | [2021-01-01,2024-01-01)
+</programlisting>
+
+ A temporal update might look like this:
+
+<programlisting>
+UPDATE products
+ <emphasis>FOR PORTION OF valid_at FROM '2023-09-01' TO '2025-03-01'</emphasis>
+ SET price = 12.00
+ WHERE product_no = 5;
+</programlisting>
+
+ That command will update the second record for product 5. It will set the
+ price to 12.00 and the application time to
+ <literal>[2023-09-01,2025-03-01)</literal>. Then, since the row's
+ application time was originally <literal>[2022-01-01,)</literal>, the
+ command must insert two <glossterm
+ linkend="glossary-temporal-leftovers">temporal leftovers</glossterm>: one
+ for history before September 1, 2023, and another for history since March
+ 1, 2025. After the update, the table has four rows for product 5:
+
+<programlisting>
+ product_no | price | valid_at
+------------+-------+-------------------------
+ 5 | 5.00 | [2020-01-01,2022-01-01)
+ 5 | 8.00 | [2022-01-01,2023-09-01)
+ 5 | 12.00 | [2023-09-01,2025-03-01)
+ 5 | 8.00 | [2025-03-01,)
+ 6 | 9.00 | [2021-01-01,2024-01-01)
+</programlisting>
+
+ The new history could be plotted as in <xref linkend="temporal-update-figure"/>.
+ </para>
+
+ <figure id="temporal-update-figure">
+ <title>Temporal Update Example</title>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/temporal-update.svg" format="SVG" width="100%"/>
+ </imageobject>
+ </mediaobject>
+ </figure>
+
+ <para>
+ Similarly, a specific portion of history may be targeted when deleting rows
+ from a table. In that case, the original rows are removed, but new
+ <glossterm linkend="glossary-temporal-leftovers">temporal
+ leftovers</glossterm> are inserted to preserve the untouched history. The
+ syntax for a temporal delete is:
+
+<programlisting>
+DELETE FROM products
+ <emphasis>FOR PORTION OF valid_at FROM '2021-08-01' TO '2023-09-01'</emphasis>
+ WHERE product_no = 5;
+</programlisting>
+
+ Continuing the example, this command would delete two records. The
+ first record would yield a single temporal leftover, and the second
+ would be deleted entirely. The rows in the table would now be:
+
+<programlisting>
+ product_no | price | valid_at
+------------+-------+-------------------------
+ 5 | 5.00 | [2020-01-01,2021-08-01)
+ 5 | 12.00 | [2023-09-01,2025-03-01)
+ 5 | 8.00 | [2025-03-01,)
+ 6 | 9.00 | [2021-01-01,2024-01-01)
+</programlisting>
+
+ The new history could be plotted as in <xref linkend="temporal-delete-figure"/>.
+ </para>
+
+ <figure id="temporal-delete-figure">
+ <title>Temporal Delete Example</title>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/temporal-delete.svg" format="SVG" width="100%"/>
+ </imageobject>
+ </mediaobject>
+ </figure>
+
+ <para>
+ Instead of using the <literal>FROM ... TO ...</literal> syntax, temporal
+ update/delete commands can also give the targeted range/multirange
+ directly, inside parentheses. For example: <literal>DELETE FROM products
+ FOR PORTION OF valid_at ('[2028-01-01,)') ...</literal>. This syntax is
+ required when application time is stored in a multirange column.
+ </para>
+
+ <para>
+ When application time is stored in a range type column, zero, one or two
+ temporal leftovers are produced by each row that is updated/deleted. With
+ a multirange column, only zero or one temporal leftover is produced. The
+ leftover bounds are computed using <literal>range_minus_multi</literal> and
+ <literal>multirange_minus_multi</literal> (see <xref
+ linkend="functions-range"/>).
+ </para>
+
+ <para>
+ The bounds given to <literal>FOR PORTION OF</literal> must be constant.
+ Functions like <literal>now()</literal> are allowed, but column references
+ are not.
+ </para>
+
+ <para>
+ When temporal leftovers are inserted, all <literal>INSERT</literal>
+ triggers are fired, but permission checks for inserting rows are
+ skipped.
+ </para>
+ </sect1>
+
<sect1 id="dml-returning">
<title>Returning Data from Modified Rows</title>
</glossdef>
</glossentry>
+ <glossentry id="glossary-temporal-leftovers">
+ <glossterm>Temporal leftovers</glossterm>
+ <glossdef>
+ <para>
+ After a temporal update or delete, the portion of history that was not
+ updated/deleted. When using ranges to track application time, there may be
+ zero, one, or two stretches of history that were not updated/deleted
+ (before and/or after the portion that was updated/deleted). New rows are
+ automatically inserted into the table to preserve that history. A single
+ multirange can accommodate the untouched history before and after the
+ update/delete, so there will be only zero or one leftover.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-temporal-table">
<glossterm>Temporal table</glossterm>
<glossdef>
gin.svg \
pagelayout.svg \
temporal-entities.svg \
- temporal-references.svg
+ temporal-references.svg \
+ temporal-update.svg \
+ temporal-delete.svg
DITAA = ditaa
DOT = dot
'pagelayout.txt',
'temporal-entities.txt',
'temporal-references.txt',
+ 'temporal-update.txt',
+ 'temporal-delete.txt',
]
foreach file : all_files
--- /dev/null
+<?xml version="1.0"?>
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 1330 224" width="1330" height="224" shape-rendering="geometricPrecision" version="1.0">
+ <defs>
+ <filter id="f2" x="0" y="0" width="200%" height="200%">
+ <feOffset result="offOut" in="SourceGraphic" dx="5" dy="5"/>
+ <feGaussianBlur result="blurOut" in="offOut" stdDeviation="3"/>
+ <feBlend in="SourceGraphic" in2="blurOut" mode="normal"/>
+ </filter>
+ </defs>
+ <g stroke-width="1" stroke-linecap="square" stroke-linejoin="round">
+ <rect x="0" y="0" width="1330" height="224" style="fill: #ffffff"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M685.0 63.0 L685.0 147.0 L1005.0 147.0 L1005.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M315.0 63.0 L315.0 147.0 L25.0 147.0 L25.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M1005.0 63.0 L1005.0 147.0 L1275.0 147.0 L1275.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M205.0 168.0 L205.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M25.0 168.0 L25.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M385.0 168.0 L385.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M565.0 168.0 L565.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M745.0 168.0 L745.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M925.0 168.0 L925.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1105.0 168.0 L1105.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1285.0 168.0 L1285.0 181.0 "/>
+ <text x="46" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="40" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 5.00,</text>
+ <text x="83" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Jan 2020,1 Aug 2021))</text>
+ <text x="20" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2020</text>
+ <text x="200" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2021</text>
+ <text x="380" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2022</text>
+ <text x="560" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2023</text>
+ <text x="706" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="700" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 12.00,</text>
+ <text x="743" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Sep 2023,1 Mar 2025))</text>
+ <text x="740" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2024</text>
+ <text x="920" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2025</text>
+ <text x="1026" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="1020" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 8.00,</text>
+ <text x="1056" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Mar 2025,))</text>
+ <text x="1100" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2026</text>
+ <text x="1289" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">...</text>
+ </g>
+</svg>
--- /dev/null
++----------------------------+ +-------------------------------+--------------------------+
+| cGRE | | cGRE | cGRE |
+| products | | products | products |
+| (5, 5.00, | | (5, 12.00, | (5, 8.00, |
+| [1 Jan 2020,1 Aug 2021)) | | [1 Sep 2023,1 Mar 2025)) | [1 Mar 2025,)) |
+| | | | |
++----------------------------+ +-------------------------------+--------------------------+
+
+| | | | | | | |
+2020 2021 2022 2023 2024 2025 2026 ...
--- /dev/null
+<?xml version="1.0"?>
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 1330 224" width="1330" height="224" shape-rendering="geometricPrecision" version="1.0">
+ <defs>
+ <filter id="f2" x="0" y="0" width="200%" height="200%">
+ <feOffset result="offOut" in="SourceGraphic" dx="5" dy="5"/>
+ <feGaussianBlur result="blurOut" in="offOut" stdDeviation="3"/>
+ <feBlend in="SourceGraphic" in2="blurOut" mode="normal"/>
+ </filter>
+ </defs>
+ <g stroke-width="1" stroke-linecap="square" stroke-linejoin="round">
+ <rect x="0" y="0" width="1330" height="224" style="fill: #ffffff"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M385.0 63.0 L385.0 147.0 L25.0 147.0 L25.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M1285.0 63.0 L1285.0 147.0 L975.0 147.0 L975.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M385.0 147.0 L685.0 147.0 L685.0 63.0 L385.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M685.0 63.0 L685.0 147.0 L975.0 147.0 L975.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M205.0 168.0 L205.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M25.0 168.0 L25.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M385.0 168.0 L385.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M565.0 168.0 L565.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M745.0 168.0 L745.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M925.0 168.0 L925.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1105.0 168.0 L1105.0 181.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1285.0 168.0 L1285.0 181.0 "/>
+ <text x="46" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="40" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 5.00,</text>
+ <text x="86" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Jan 2020,1 Jan 2022))</text>
+ <text x="20" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2020</text>
+ <text x="200" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2021</text>
+ <text x="406" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="400" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 8.00,</text>
+ <text x="445" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Jan 2022,1 Sep 2023))</text>
+ <text x="380" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2022</text>
+ <text x="560" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2023</text>
+ <text x="706" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="700" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 12.00,</text>
+ <text x="743" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Sep 2023,1 Mar 2025))</text>
+ <text x="740" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2024</text>
+ <text x="920" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2025</text>
+ <text x="996" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="990" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 8.00,</text>
+ <text x="1026" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Mar 2025,))</text>
+ <text x="1100" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2026</text>
+ <text x="1289" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">...</text>
+ </g>
+</svg>
--- /dev/null
++-----------------------------------+-----------------------------+----------------------------+------------------------------+
+| cGRE | cGRE | cGRE | cGRE |
+| products | products | products | products |
+| (5, 5.00, | (5, 8.00, | (5, 12.00, | (5, 8.00, |
+| [1 Jan 2020,1 Jan 2022)) | [1 Jan 2022,1 Sep 2023)) | [1 Sep 2023,1 Mar 2025)) | [1 Mar 2025,)) |
+| | | | |
++-----------------------------------+-----------------------------+----------------------------+------------------------------+
+
+| | | | | | | |
+2020 2021 2022 2023 2024 2025 2026 ...
for each row inserted, updated, or deleted.
</para>
+ <para>
+ For an <command>UPDATE/DELETE ... FOR PORTION OF</command> command, the
+ publication will publish an <command>UPDATE</command> or <command>DELETE</command>,
+ followed by one <command>INSERT</command> for each temporal leftover row inserted.
+ </para>
+
<para>
<command>ATTACH</command>ing a table into a partition tree whose root is
published using a publication with <literal>publish_via_partition_root</literal>
<refsynopsisdiv>
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
-DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
+DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
+ [ FOR PORTION OF <replaceable class="parameter">range_column_name</replaceable> <replaceable class="parameter">for_portion_of_target</replaceable> ]
+ [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
[ USING <replaceable class="parameter">from_item</replaceable> [, ...] ]
[ WHERE <replaceable class="parameter">condition</replaceable> | WHERE CURRENT OF <replaceable class="parameter">cursor_name</replaceable> ]
[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
{ * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
+
+<phrase>where <replaceable class="parameter">for_portion_of_target</replaceable> is:</phrase>
+
+{ FROM <replaceable class="parameter">start_time</replaceable> TO <replaceable class="parameter">end_time</replaceable> |
+ ( <replaceable class="parameter">portion</replaceable> ) }
</synopsis>
</refsynopsisdiv>
output list of <command>SELECT</command>.
</para>
+ <para>
+ If the <literal>FOR PORTION OF</literal> clause is used, the delete will
+ only affect rows that overlap the given portion. Furthermore, if a row's
+ application time extends outside the <literal>FOR PORTION OF</literal>
+ bounds, then the delete will only change the application time within those
+ bounds. In effect, only the history targeted by <literal>FOR PORTION
+ OF</literal> is deleted, and no moments outside. Furthermore, after a row
+ is deleted, new <glossterm linkend="glossary-temporal-leftovers">temporal
+ leftovers</glossterm> might be inserted: rows whose range or multirange
+ receives the remaining application time outside the targeted bounds, with
+ the original values in their other columns. For range columns, there will
+ be zero to two inserted records, depending on whether the original
+ application time was completely deleted, extended before/after the change,
+ or both. Multiranges never require two temporal leftovers, because one
+ value can always contain whatever application time remains.
+ </para>
+
<para>
You must have the <literal>DELETE</literal> privilege on the table
to delete from it, as well as the <literal>SELECT</literal>
privilege for any table in the <literal>USING</literal> clause or
whose values are read in the <replaceable
class="parameter">condition</replaceable>.
+ When <literal>FOR PORTION OF</literal> is used, the secondary inserts do
+ not require <literal>INSERT</literal> privilege on the table. (This is
+ because conceptually no new information is being added; the inserted rows
+ only preserve existing data about the untargeted time period.)
</para>
</refsect1>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">range_column_name</replaceable></term>
+ <listitem>
+ <para>
+ The range or multirange column to use when performing a temporal delete.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">for_portion_of_target</replaceable></term>
+ <listitem>
+ <para>
+ The portion to delete. If targeting a range column, this can be in the
+ form <literal>FROM</literal> <replaceable
+ class="parameter">start_time</replaceable> <literal>TO</literal>
+ <replaceable class="parameter">end_time</replaceable>. Otherwise, it
+ must be in the form <literal>(</literal><replaceable
+ class="parameter">portion</replaceable><literal>)</literal>, where
+ <replaceable class="parameter">portion</replaceable> is an expression
+ that yields a value of the same type as <replaceable
+ class="parameter">range_column_name</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">start_time</replaceable></term>
+ <listitem>
+ <para>
+ The earliest time (inclusive) to change in a temporal delete. This must
+ be a value matching the base type of the range from <replaceable
+ class="parameter">range_column_name</replaceable>. A null value here
+ indicates a delete whose beginning is unbounded (as with range types).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">end_time</replaceable></term>
+ <listitem>
+ <para>
+ The latest time (exclusive) to change in a temporal delete. This must
+ be a value matching the base type of the range from <replaceable
+ class="parameter">range_column_name</replaceable>. A null value here
+ indicates a delete whose end is unbounded (as with range types).
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">from_item</replaceable></term>
<listitem>
suppressed by a <literal>BEFORE DELETE</literal> trigger. If <replaceable
class="parameter">count</replaceable> is 0, no rows were deleted by
the query (this is not considered an error).
+ If <literal>FOR PORTION OF</literal> was used, the
+ <replaceable class="parameter">count</replaceable> does not include
+ <glossterm linkend="glossary-temporal-leftovers">temporal leftovers</glossterm>
+ that were inserted.
</para>
<para>
clause, the result will be similar to that of a <command>SELECT</command>
statement containing the columns and values defined in the
<literal>RETURNING</literal> list, computed over the row(s) deleted by the
- command.
+ command. If <literal>FOR PORTION OF</literal> was used, the
+ <literal>RETURNING</literal> clause gives one result for each deleted row,
+ but does not include inserted
+ <glossterm linkend="glossary-temporal-leftovers">temporal leftovers</glossterm>.
+ The value of the application-time column matches the old value of the deleted
+ row(s). Note this will represent more application time than was actually erased,
+ if temporal leftovers were inserted.
</para>
</refsect1>
In some cases the join style is easier to write or faster to
execute than the sub-select style.
</para>
+
+ <para>
+ When <literal>FOR PORTION OF</literal> is used, this can result in users
+ who don't have <literal>INSERT</literal> privileges firing
+ <literal>INSERT</literal> triggers. This should be considered when
+ using <literal>SECURITY DEFINER</literal> trigger functions.
+ </para>
</refsect1>
<refsect1>
</programlisting>
</para>
+ <para>
+ An example of a temporal delete:
+<programlisting>
+DELETE FROM products
+ FOR PORTION OF valid_at FROM '2021-08-01' TO '2023-09-01'
+ WHERE product_no = 5;
+</programlisting>
+ </para>
+
<para>
While there is no <literal>LIMIT</literal> clause
for <command>DELETE</command>, it is possible to get a similar effect
<refsynopsisdiv>
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
-UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
+UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
+ [ FOR PORTION OF <replaceable class="parameter">range_column_name</replaceable> <replaceable class="parameter">for_portion_of_target</replaceable> ]
+ [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> )
[ WHERE <replaceable class="parameter">condition</replaceable> | WHERE CURRENT OF <replaceable class="parameter">cursor_name</replaceable> ]
[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
{ * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
+
+<phrase>where <replaceable class="parameter">for_portion_of_target</replaceable> is:</phrase>
+
+{ FROM <replaceable class="parameter">start_time</replaceable> TO <replaceable class="parameter">end_time</replaceable> |
+ ( <replaceable class="parameter">portion</replaceable> ) }
</synopsis>
</refsynopsisdiv>
output list of <command>SELECT</command>.
</para>
+ <para>
+ If the <literal>FOR PORTION OF</literal> clause is used, the update will
+ only affect rows that overlap the given portion. Furthermore, if a row's
+ application time extends outside the <literal>FOR PORTION OF</literal>
+ bounds, then the update will only change the application time within those
+ bounds. In effect, only the history targeted by <literal>FOR PORTION
+ OF</literal> is updated, and no moments outside. Furthermore, after a row
+ is updated, the range or multirange is first shrunk so that its application
+ time no longer extends beyond the targeted <literal>FOR PORTION
+ OF</literal> bounds. Then, new <glossterm
+ linkend="glossary-temporal-leftovers">temporal leftovers</glossterm> might
+ be inserted: rows whose range or multirange receives the remaining
+ application time outside the targeted
+ <literal>FROM</literal>/<literal>TO</literal> bounds, with the original
+ values in their other columns. For range columns, there will be zero to
+ two inserted records, depending on whether the original application time
+ was completely updated, extended before/after the change, or both.
+ Multiranges never require two temporal leftovers, because one value can
+ always contain whatever application time remains.
+ </para>
+
<para>
You must have the <literal>UPDATE</literal> privilege on the table,
or at least on the column(s) that are listed to be updated.
privilege on any column whose values are read in the
<replaceable class="parameter">expressions</replaceable> or
<replaceable class="parameter">condition</replaceable>.
+ When <literal>FOR PORTION OF</literal> is used, the secondary inserts do
+ not require <literal>INSERT</literal> privilege on the table. (This is
+ because conceptually no new information is being added; the inserted rows
+ only preserve existing data about the untargeted time period.)
</para>
</refsect1>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">range_column_name</replaceable></term>
+ <listitem>
+ <para>
+ The range or multirange column to use when performing a temporal update.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">for_portion_of_target</replaceable></term>
+ <listitem>
+ <para>
+ The portion to update. If targeting a range column, this can be in the
+ form <literal>FROM</literal> <replaceable
+ class="parameter">start_time</replaceable> <literal>TO</literal>
+ <replaceable class="parameter">end_time</replaceable>. Otherwise, it
+ must be in the form <literal>(</literal><replaceable
+ class="parameter">portion</replaceable><literal>)</literal> where
+ <replaceable class="parameter">portion</replaceable> is an expression
+ that yields a value of the same type as <replaceable
+ class="parameter">range_column_name</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">start_time</replaceable></term>
+ <listitem>
+ <para>
+ The earliest time (inclusive) to change in a temporal update. This must
+ be a value matching the base type of the range from <replaceable
+ class="parameter">range_column_name</replaceable>. A null value here
+ indicates an update whose beginning is unbounded (as with range types).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">end_time</replaceable></term>
+ <listitem>
+ <para>
+ The latest time (exclusive) to change in a temporal update. This must
+ be a value matching the base type of the range from <replaceable
+ class="parameter">range_column_name</replaceable>. A null value here
+ indicates an update whose end is unbounded (as with range types).
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">column_name</replaceable></term>
<listitem>
updates were suppressed by a <literal>BEFORE UPDATE</literal> trigger. If
<replaceable class="parameter">count</replaceable> is 0, no rows were
updated by the query (this is not considered an error).
+ If <literal>FOR PORTION OF</literal> was used, the
+ <replaceable class="parameter">count</replaceable> does not include
+ <glossterm linkend="glossary-temporal-leftovers">temporal leftovers</glossterm>
+ that were inserted.
</para>
<para>
clause, the result will be similar to that of a <command>SELECT</command>
statement containing the columns and values defined in the
<literal>RETURNING</literal> list, computed over the row(s) updated by the
- command.
+ command. If <literal>FOR PORTION OF</literal> was used, the
+ <literal>RETURNING</literal> clause gives one result for each updated row,
+ but does not include inserted
+ <glossterm linkend="glossary-temporal-leftovers">temporal leftovers</glossterm>.
+ The value of the application-time column matches the new value of the updated
+ row(s).
</para>
</refsect1>
partition that is not the same as the ancestor that's mentioned in the
<command>UPDATE</command> query.
</para>
+
+ <para>
+ When <literal>FOR PORTION OF</literal> is used, this can result in users
+ who don't have <literal>INSERT</literal> privileges firing
+ <literal>INSERT</literal> triggers. This should be considered when
+ using <literal>SECURITY DEFINER</literal> trigger functions.
+ </para>
</refsect1>
<refsect1>
</programlisting>
</para>
+ <para>
+ An example of a temporal update:
+<programlisting>
+UPDATE products
+ FOR PORTION OF valid_at FROM '2023-09-01' TO '2025-03-01'
+ SET price = 12.00
+ WHERE product_no = 5;
+</programlisting>
+ </para>
+
<para id="update-limit">
Updates affecting many rows can have negative effects on system
performance, such as table bloat, increased replica lag, and increased
are fired for the same kind of action.
</para>
+ <para>
+ If an <command>UPDATE</command> or <command>DELETE</command> uses
+ <literal>FOR PORTION OF</literal>, causing new rows to be inserted
+ to preserve the leftover untargeted part of modified records, then
+ <command>INSERT</command> triggers are fired for each inserted
+ row. Each row is inserted separately, so they fire their own
+ statement triggers, and they have their own transition tables.
+ (The <literal>BEFORE DELETE/UPDATE</literal> triggers are fired first,
+ then <literal>BEFORE INSERT</literal>, then <literal>AFTER
+ INSERT</literal>, then <literal>AFTER DELETE/UPDATE</literal>.)
+ </para>
+
<para>
Trigger functions invoked by per-statement triggers should always
return <symbol>NULL</symbol>. Trigger functions invoked by per-row
resultRelInfo->ri_projectReturning = NULL;
resultRelInfo->ri_onConflictArbiterIndexes = NIL;
resultRelInfo->ri_onConflict = NULL;
+ resultRelInfo->ri_forPortionOf = NULL;
resultRelInfo->ri_ReturningSlot = NULL;
resultRelInfo->ri_TrigOldSlot = NULL;
resultRelInfo->ri_TrigNewSlot = NULL;
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/injection_point.h"
+#include "utils/rangetypes.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
TupleTableSlot *excludedSlot,
bool canSetTag,
TupleTableSlot **returning);
+static void ExecForPortionOfLeftovers(ModifyTableContext *context,
+ EState *estate,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid);
static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate,
EState *estate,
PartitionTupleRouting *proute,
static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
bool canSetTag);
+static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate);
+static void fireBSTriggers(ModifyTableState *node);
+static void fireASTriggers(ModifyTableState *node);
/*
return result;
}
+/* ----------------------------------------------------------------
+ * ExecForPortionOfLeftovers
+ *
+ * Insert tuples for the untouched portion of a row in a FOR
+ * PORTION OF UPDATE/DELETE
+ * ----------------------------------------------------------------
+ */
+static void
+ExecForPortionOfLeftovers(ModifyTableContext *context,
+ EState *estate,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid)
+{
+ ModifyTableState *mtstate = context->mtstate;
+ ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
+ ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
+ AttrNumber rangeAttno;
+ Datum oldRange;
+ TypeCacheEntry *typcache;
+ ForPortionOfState *fpoState;
+ TupleTableSlot *oldtupleSlot;
+ TupleTableSlot *leftoverSlot;
+ TupleConversionMap *map = NULL;
+ HeapTuple oldtuple = NULL;
+ CmdType oldOperation;
+ TransitionCaptureState *oldTcs;
+ FmgrInfo flinfo;
+ ReturnSetInfo rsi;
+ bool didInit = false;
+ bool shouldFree = false;
+
+ LOCAL_FCINFO(fcinfo, 2);
+
+ if (!resultRelInfo->ri_forPortionOf)
+ {
+ /*
+ * If we don't have a ForPortionOfState yet, we must be a partition
+ * child being hit for the first time. Make a copy from the root, with
+ * our own tupleTableSlot. We do this lazily so that we don't pay the
+ * price of unused partitions.
+ */
+ ForPortionOfState *leafState = makeNode(ForPortionOfState);
+
+ if (!mtstate->rootResultRelInfo)
+ elog(ERROR, "no root relation but ri_forPortionOf is uninitialized");
+
+ fpoState = mtstate->rootResultRelInfo->ri_forPortionOf;
+ Assert(fpoState);
+
+ leafState->fp_rangeName = fpoState->fp_rangeName;
+ leafState->fp_rangeType = fpoState->fp_rangeType;
+ leafState->fp_rangeAttno = fpoState->fp_rangeAttno;
+ leafState->fp_targetRange = fpoState->fp_targetRange;
+ leafState->fp_Leftover = fpoState->fp_Leftover;
+ /* Each partition needs a slot matching its tuple descriptor */
+ leafState->fp_Existing =
+ table_slot_create(resultRelInfo->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ resultRelInfo->ri_forPortionOf = leafState;
+ }
+ fpoState = resultRelInfo->ri_forPortionOf;
+ oldtupleSlot = fpoState->fp_Existing;
+ leftoverSlot = fpoState->fp_Leftover;
+
+ /*
+ * Get the old pre-UPDATE/DELETE tuple. We will use its range to compute
+ * untouched parts of history, and if necessary we will insert copies with
+ * truncated start/end times.
+ *
+ * We have already locked the tuple in ExecUpdate/ExecDelete, and it has
+ * passed EvalPlanQual. This ensures that concurrent updates in READ
+ * COMMITTED can't insert conflicting temporal leftovers.
+ */
+ if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot))
+ elog(ERROR, "failed to fetch tuple for FOR PORTION OF");
+
+ /*
+ * Get the old range of the record being updated/deleted. Must read with
+ * the attno of the leaf partition being updated.
+ */
+
+ rangeAttno = forPortionOf->rangeVar->varattno;
+ if (resultRelInfo->ri_RootResultRelInfo)
+ map = ExecGetChildToRootMap(resultRelInfo);
+ if (map != NULL)
+ rangeAttno = map->attrMap->attnums[rangeAttno - 1];
+ slot_getallattrs(oldtupleSlot);
+
+ if (oldtupleSlot->tts_isnull[rangeAttno - 1])
+ elog(ERROR, "found a NULL range in a temporal table");
+ oldRange = oldtupleSlot->tts_values[rangeAttno - 1];
+
+ /*
+ * Get the range's type cache entry. This is worth caching for the whole
+ * UPDATE/DELETE as range functions do.
+ */
+
+ typcache = fpoState->fp_leftoverstypcache;
+ if (typcache == NULL)
+ {
+ typcache = lookup_type_cache(forPortionOf->rangeType, 0);
+ fpoState->fp_leftoverstypcache = typcache;
+ }
+
+ /*
+ * Get the ranges to the left/right of the targeted range. We call a SETOF
+ * support function and insert as many temporal leftovers as it gives us.
+ * Although rangetypes have 0/1/2 leftovers, multiranges have 0/1, and
+ * other types may have more.
+ */
+
+ fmgr_info(forPortionOf->withoutPortionProc, &flinfo);
+ rsi.type = T_ReturnSetInfo;
+ rsi.econtext = mtstate->ps.ps_ExprContext;
+ rsi.expectedDesc = NULL;
+ rsi.allowedModes = (int) (SFRM_ValuePerCall);
+ rsi.returnMode = SFRM_ValuePerCall;
+ rsi.setResult = NULL;
+ rsi.setDesc = NULL;
+
+ InitFunctionCallInfoData(*fcinfo, &flinfo, 2, InvalidOid, NULL, (Node *) &rsi);
+ fcinfo->args[0].value = oldRange;
+ fcinfo->args[0].isnull = false;
+ fcinfo->args[1].value = fpoState->fp_targetRange;
+ fcinfo->args[1].isnull = false;
+
+ /*
+ * If there are partitions, we must insert into the root table, so we get
+ * tuple routing. We already set up leftoverSlot with the root tuple
+ * descriptor.
+ */
+ if (resultRelInfo->ri_RootResultRelInfo)
+ resultRelInfo = resultRelInfo->ri_RootResultRelInfo;
+
+ /*
+ * Insert a leftover for each value returned by the without_portion helper
+ * function
+ */
+ while (true)
+ {
+ Datum leftover = FunctionCallInvoke(fcinfo);
+
+ /* Are we done? */
+ if (rsi.isDone == ExprEndResult)
+ break;
+
+ if (fcinfo->isnull)
+ elog(ERROR, "Got a null from without_portion function");
+
+ /*
+ * Does the new Datum violate domain checks? Row-level CHECK
+ * constraints are validated by ExecInsert, so we don't need to do
+ * anything here for those.
+ */
+ if (forPortionOf->isDomain)
+ domain_check(leftover, false, forPortionOf->rangeVar->vartype, NULL, NULL);
+
+ if (!didInit)
+ {
+ /*
+ * Make a copy of the pre-UPDATE row. Then we'll overwrite the
+ * range column below. Convert oldtuple to the base table's format
+ * if necessary. We need to insert temporal leftovers through the
+ * root partition so they get routed correctly.
+ */
+ if (map != NULL)
+ {
+ leftoverSlot = execute_attr_map_slot(map->attrMap,
+ oldtupleSlot,
+ leftoverSlot);
+ }
+ else
+ {
+ oldtuple = ExecFetchSlotHeapTuple(oldtupleSlot, false, &shouldFree);
+ ExecForceStoreHeapTuple(oldtuple, leftoverSlot, false);
+ }
+
+ /*
+ * Save some mtstate things so we can restore them below. XXX:
+ * Should we create our own ModifyTableState instead?
+ */
+ oldOperation = mtstate->operation;
+ mtstate->operation = CMD_INSERT;
+ oldTcs = mtstate->mt_transition_capture;
+
+ didInit = true;
+ }
+
+ leftoverSlot->tts_values[forPortionOf->rangeVar->varattno - 1] = leftover;
+ leftoverSlot->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false;
+ ExecMaterializeSlot(leftoverSlot);
+
+ /*
+ * The standard says that each temporal leftover should execute its
+ * own INSERT statement, firing all statement and row triggers, but
+ * skipping insert permission checks. Therefore we give each insert
+ * its own transition table. If we just push & pop a new trigger level
+ * for each insert, we get exactly what we need.
+ *
+ * We have to make sure that the inserts don't add to the ROW_COUNT
+ * diagnostic or the command tag, so we pass false for canSetTag.
+ */
+ AfterTriggerBeginQuery();
+ ExecSetupTransitionCaptureState(mtstate, estate);
+ fireBSTriggers(mtstate);
+ ExecInsert(context, resultRelInfo, leftoverSlot, false, NULL, NULL);
+ fireASTriggers(mtstate);
+ AfterTriggerEndQuery(estate);
+ }
+
+ if (didInit)
+ {
+ mtstate->operation = oldOperation;
+ mtstate->mt_transition_capture = oldTcs;
+
+ if (shouldFree)
+ heap_freetuple(oldtuple);
+ }
+}
+
/* ----------------------------------------------------------------
* ExecBatchInsert
*
*
* Closing steps of tuple deletion; this invokes AFTER FOR EACH ROW triggers,
* including the UPDATE triggers if the deletion is being done as part of a
- * cross-partition tuple move.
+ * cross-partition tuple move. It also inserts temporal leftovers from a
+ * DELETE FOR PORTION OF.
*/
static void
ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ar_delete_trig_tcs = NULL;
}
+ /* Compute temporal leftovers in FOR PORTION OF */
+ if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf)
+ ExecForPortionOfLeftovers(context, estate, resultRelInfo, tupleid);
+
/* AFTER ROW DELETE Triggers */
ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple,
ar_delete_trig_tcs, changingPart);
if (resultRelInfo == mtstate->rootResultRelInfo)
ExecPartitionCheckEmitError(resultRelInfo, slot, estate);
- /* Initialize tuple routing info if not already done. */
+ /*
+ * Initialize tuple routing info if not already done. Note whatever we do
+ * here must be done in ExecInitModifyTable for FOR PORTION OF as well.
+ */
if (mtstate->mt_partition_tuple_routing == NULL)
{
Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc;
* ExecUpdateEpilogue -- subroutine for ExecUpdate
*
* Closing steps of updating a tuple. Must be called if ExecUpdateAct
- * returns indicating that the tuple was updated.
+ * returns indicating that the tuple was updated. It also inserts temporal
+ * leftovers from an UPDATE FOR PORTION OF.
*/
static void
ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt,
NULL);
}
+ /* Compute temporal leftovers in FOR PORTION OF */
+ if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf)
+ ExecForPortionOfLeftovers(context, context->estate, resultRelInfo, tupleid);
+
/* AFTER ROW UPDATE Triggers */
ExecARUpdateTriggers(context->estate, resultRelInfo,
NULL, NULL,
}
}
+ /*
+ * If needed, initialize the target range for FOR PORTION OF.
+ */
+ if (node->forPortionOf)
+ {
+ ResultRelInfo *rootRelInfo;
+ TupleDesc tupDesc;
+ ForPortionOfExpr *forPortionOf;
+ Datum targetRange;
+ bool isNull;
+ ExprContext *econtext;
+ ExprState *exprState;
+ ForPortionOfState *fpoState;
+
+ rootRelInfo = mtstate->resultRelInfo;
+ if (rootRelInfo->ri_RootResultRelInfo)
+ rootRelInfo = rootRelInfo->ri_RootResultRelInfo;
+
+ tupDesc = rootRelInfo->ri_RelationDesc->rd_att;
+ forPortionOf = (ForPortionOfExpr *) node->forPortionOf;
+
+ /* Eval the FOR PORTION OF target */
+ if (mtstate->ps.ps_ExprContext == NULL)
+ ExecAssignExprContext(estate, &mtstate->ps);
+ econtext = mtstate->ps.ps_ExprContext;
+
+ exprState = ExecPrepareExpr((Expr *) forPortionOf->targetRange, estate);
+ targetRange = ExecEvalExpr(exprState, econtext, &isNull);
+
+ /*
+ * FOR PORTION OF ... TO ... FROM should never give us a NULL target,
+ * but FOR PORTION OF (...) could.
+ */
+ if (isNull)
+ ereport(ERROR,
+ (errmsg("FOR PORTION OF target was null")),
+ executor_errposition(estate, forPortionOf->targetLocation));
+
+ /* Create state for FOR PORTION OF operation */
+
+ fpoState = makeNode(ForPortionOfState);
+ fpoState->fp_rangeName = forPortionOf->range_name;
+ fpoState->fp_rangeType = forPortionOf->rangeType;
+ fpoState->fp_rangeAttno = forPortionOf->rangeVar->varattno;
+ fpoState->fp_targetRange = targetRange;
+
+ /* Initialize slot for the existing tuple */
+
+ fpoState->fp_Existing =
+ table_slot_create(rootRelInfo->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ /* Create the tuple slot for INSERTing the temporal leftovers */
+
+ fpoState->fp_Leftover =
+ ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual);
+
+ rootRelInfo->ri_forPortionOf = fpoState;
+
+ /*
+ * Make sure the root relation has the FOR PORTION OF clause too. Each
+ * partition needs its own TupleTableSlot, since they can have
+ * different descriptors, so they'll use the root fpoState to
+ * initialize one if necessary.
+ */
+ if (node->rootRelation > 0)
+ mtstate->rootResultRelInfo->ri_forPortionOf = fpoState;
+
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
+ mtstate->mt_partition_tuple_routing == NULL)
+ {
+ /*
+ * We will need tuple routing to insert temporal leftovers. Since
+ * we are initializing things before ExecCrossPartitionUpdate
+ * runs, we must do everything it needs as well.
+ */
+ Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc;
+ MemoryContext oldcxt;
+
+ /* Things built here have to last for the query duration. */
+ oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ mtstate->mt_partition_tuple_routing =
+ ExecSetupPartitionTupleRouting(estate, rootRel);
+
+ /*
+ * Before a partition's tuple can be re-routed, it must first be
+ * converted to the root's format, so we'll need a slot for
+ * storing such tuples.
+ */
+ Assert(mtstate->mt_root_tuple_slot == NULL);
+ mtstate->mt_root_tuple_slot = table_slot_create(rootRel, NULL);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /*
+ * Don't free the ExprContext here because the result must last for
+ * the whole query.
+ */
+ }
+
/*
* If we have any secondary relations in an UPDATE or DELETE, they need to
* be treated like non-locked relations in SELECT FOR UPDATE, i.e., the
return true;
}
break;
+ case T_ForPortionOfExpr:
+ {
+ ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node;
+
+ if (WALK(forPortionOf->targetFrom))
+ return true;
+ if (WALK(forPortionOf->targetTo))
+ return true;
+ if (WALK(forPortionOf->targetRange))
+ return true;
+ if (WALK(forPortionOf->overlapsExpr))
+ return true;
+ }
+ break;
case T_PartitionPruneStepOp:
{
PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node;
return true;
if (WALK(query->mergeJoinCondition))
return true;
+ if (WALK(query->forPortionOf))
+ return true;
if (WALK(query->returningList))
return true;
if (WALK(query->jointree))
return (Node *) newnode;
}
break;
+ case T_ForPortionOfExpr:
+ {
+ ForPortionOfExpr *fpo = (ForPortionOfExpr *) node;
+ ForPortionOfExpr *newnode;
+
+ FLATCOPY(newnode, fpo, ForPortionOfExpr);
+ MUTATE(newnode->rangeVar, fpo->rangeVar, Var *);
+ MUTATE(newnode->targetFrom, fpo->targetFrom, Node *);
+ MUTATE(newnode->targetTo, fpo->targetTo, Node *);
+ MUTATE(newnode->targetRange, fpo->targetRange, Node *);
+ MUTATE(newnode->overlapsExpr, fpo->overlapsExpr, Node *);
+ MUTATE(newnode->rangeTargetList, fpo->rangeTargetList, List *);
+
+ return (Node *) newnode;
+ }
+ break;
case T_PartitionPruneStepOp:
{
PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node;
MUTATE(query->onConflict, query->onConflict, OnConflictExpr *);
MUTATE(query->mergeActionList, query->mergeActionList, List *);
MUTATE(query->mergeJoinCondition, query->mergeJoinCondition, Node *);
+ MUTATE(query->forPortionOf, query->forPortionOf, ForPortionOfExpr *);
MUTATE(query->returningList, query->returningList, List *);
MUTATE(query->jointree, query->jointree, FromExpr *);
MUTATE(query->setOperations, query->setOperations, Node *);
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
List *mergeActionLists, List *mergeJoinConditions,
- int epqParam);
+ ForPortionOfExpr *forPortionOf, int epqParam);
static GatherMerge *create_gather_merge_plan(PlannerInfo *root,
GatherMergePath *best_path);
best_path->onconflict,
best_path->mergeActionLists,
best_path->mergeJoinConditions,
+ best_path->forPortionOf,
best_path->epqParam);
copy_generic_path_info(&plan->plan, &best_path->path);
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
List *mergeActionLists, List *mergeJoinConditions,
- int epqParam)
+ ForPortionOfExpr *forPortionOf, int epqParam)
{
ModifyTable *node = makeNode(ModifyTable);
bool returning_old_or_new = false;
node->exclRelTlist = onconflict->exclRelTlist;
}
node->updateColnosLists = updateColnosLists;
+ node->forPortionOf = (Node *) forPortionOf;
node->withCheckOptionLists = withCheckOptionLists;
node->returningOldAlias = root->parse->returningOldAlias;
node->returningNewAlias = root->parse->returningNewAlias;
parse->onConflict,
mergeActionLists,
mergeJoinConditions,
+ parse->forPortionOf,
assign_special_exec_param(root));
}
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
List *mergeActionLists, List *mergeJoinConditions,
- int epqParam)
+ ForPortionOfExpr *forPortionOf, int epqParam)
{
ModifyTablePath *pathnode = makeNode(ModifyTablePath);
pathnode->returningLists = returningLists;
pathnode->rowMarks = rowMarks;
pathnode->onconflict = onconflict;
+ pathnode->forPortionOf = forPortionOf;
pathnode->epqParam = epqParam;
pathnode->mergeActionLists = mergeActionLists;
pathnode->mergeJoinConditions = mergeJoinConditions;
#include "postgres.h"
+#include "access/stratnum.h"
#include "access/sysattr.h"
#include "catalog/dependency.h"
+#include "catalog/pg_am.h"
+#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "parser/parsetree.h"
#include "utils/backend_status.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/guc.h"
+#include "utils/lsyscache.h"
+#include "utils/rangetypes.h"
#include "utils/rel.h"
#include "utils/syscache.h"
static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static OnConflictExpr *transformOnConflictClause(ParseState *pstate,
OnConflictClause *onConflictClause);
+static ForPortionOfExpr *transformForPortionOfClause(ParseState *pstate,
+ int rtindex,
+ const ForPortionOfClause *forPortionOfClause,
+ bool isUpdate);
static int count_rowexpr_columns(ParseState *pstate, Node *expr);
static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt,
SelectStmtPassthrough *passthru);
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
+ if (stmt->forPortionOf)
+ qry->forPortionOf = transformForPortionOfClause(pstate,
+ qry->resultRelation,
+ stmt->forPortionOf,
+ false);
+
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
/* Process the UPDATE SET clause */
if (onConflictClause->action == ONCONFLICT_UPDATE)
onConflictSet =
- transformUpdateTargetList(pstate, onConflictClause->targetList);
+ transformUpdateTargetList(pstate, onConflictClause->targetList, NULL);
/* Process the SELECT/UPDATE WHERE clause */
onConflictWhere = transformWhereClause(pstate,
return result;
}
+/*
+ * transformForPortionOfClause
+ *
+ * Transforms a ForPortionOfClause in an UPDATE/DELETE statement.
+ *
+ * - Look up the range/period requested.
+ * - Build a compatible range value from the FROM and TO expressions.
+ * - Build an "overlaps" expression for filtering, used later by the
+ * rewriter.
+ * - For UPDATEs, build an "intersects" expression the rewriter can add
+ * to the targetList to change the temporal bounds.
+ */
+static ForPortionOfExpr *
+transformForPortionOfClause(ParseState *pstate,
+ int rtindex,
+ const ForPortionOfClause *forPortionOf,
+ bool isUpdate)
+{
+ Relation targetrel = pstate->p_target_relation;
+ int range_attno = InvalidAttrNumber;
+ Form_pg_attribute attr;
+ Oid attbasetype;
+ Oid opclass;
+ Oid opfamily;
+ Oid opcintype;
+ Oid funcid = InvalidOid;
+ StrategyNumber strat;
+ Oid opid;
+ OpExpr *op;
+ ForPortionOfExpr *result;
+ Var *rangeVar;
+
+ /* We don't support FOR PORTION OF FDW queries. */
+ if (targetrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign tables don't support FOR PORTION OF")));
+
+ result = makeNode(ForPortionOfExpr);
+
+ /* Look up the FOR PORTION OF name requested. */
+ range_attno = attnameAttNum(targetrel, forPortionOf->range_name, false);
+ if (range_attno == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ forPortionOf->range_name,
+ RelationGetRelationName(targetrel)),
+ parser_errposition(pstate, forPortionOf->location)));
+ attr = TupleDescAttr(targetrel->rd_att, range_attno - 1);
+
+ attbasetype = getBaseType(attr->atttypid);
+
+ rangeVar = makeVar(rtindex,
+ range_attno,
+ attr->atttypid,
+ attr->atttypmod,
+ attr->attcollation,
+ 0);
+ rangeVar->location = forPortionOf->location;
+ result->rangeVar = rangeVar;
+
+ /* Require SELECT privilege on the application-time column. */
+ markVarForSelectPriv(pstate, rangeVar);
+
+ /*
+ * Use the basetype for the target, which shouldn't be required to follow
+ * domain rules. The table's column type is in the Var if we need it.
+ */
+ result->rangeType = attbasetype;
+ result->isDomain = attbasetype != attr->atttypid;
+
+ if (forPortionOf->target)
+ {
+ Oid declared_target_type = attbasetype;
+ Oid actual_target_type;
+
+ /*
+ * We were already given an expression for the target, so we don't
+ * have to build anything. We still have to make sure we got the right
+ * type. NULL will be caught be the executor.
+ */
+
+ result->targetRange = transformExpr(pstate,
+ forPortionOf->target,
+ EXPR_KIND_FOR_PORTION);
+
+ actual_target_type = exprType(result->targetRange);
+
+ if (!can_coerce_type(1, &actual_target_type, &declared_target_type, COERCION_IMPLICIT))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("could not coerce FOR PORTION OF target from %s to %s",
+ format_type_be(actual_target_type),
+ format_type_be(declared_target_type)),
+ parser_errposition(pstate, exprLocation(forPortionOf->target))));
+
+ result->targetRange = coerce_type(pstate,
+ result->targetRange,
+ actual_target_type,
+ declared_target_type,
+ -1,
+ COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST,
+ exprLocation(forPortionOf->target));
+
+ /*
+ * XXX: For now we only support ranges and multiranges, so we fail on
+ * anything else.
+ */
+ if (!type_is_range(attbasetype) && !type_is_multirange(attbasetype))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column \"%s\" of relation \"%s\" is not a range or multirange type",
+ forPortionOf->range_name,
+ RelationGetRelationName(targetrel)),
+ parser_errposition(pstate, forPortionOf->location)));
+
+ }
+ else
+ {
+ Oid rngsubtype;
+ Oid declared_arg_types[2];
+ Oid actual_arg_types[2];
+ List *args;
+
+ /*
+ * Make sure it's a range column. XXX: We could support this syntax on
+ * multirange columns too, if we just built a one-range multirange
+ * from the FROM/TO phrases.
+ */
+ if (!type_is_range(attbasetype))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column \"%s\" of relation \"%s\" is not a range type",
+ forPortionOf->range_name,
+ RelationGetRelationName(targetrel)),
+ parser_errposition(pstate, forPortionOf->location)));
+
+ rngsubtype = get_range_subtype(attbasetype);
+ declared_arg_types[0] = rngsubtype;
+ declared_arg_types[1] = rngsubtype;
+
+ /*
+ * Build a range from the FROM ... TO ... bounds. This should give a
+ * constant result, so we accept functions like NOW() but not column
+ * references, subqueries, etc.
+ */
+ result->targetFrom = transformExpr(pstate,
+ forPortionOf->target_start,
+ EXPR_KIND_FOR_PORTION);
+ result->targetTo = transformExpr(pstate,
+ forPortionOf->target_end,
+ EXPR_KIND_FOR_PORTION);
+ actual_arg_types[0] = exprType(result->targetFrom);
+ actual_arg_types[1] = exprType(result->targetTo);
+ args = list_make2(copyObject(result->targetFrom),
+ copyObject(result->targetTo));
+
+ /*
+ * Check the bound types separately, for better error message and
+ * location
+ */
+ if (!can_coerce_type(1, actual_arg_types, declared_arg_types, COERCION_IMPLICIT))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("could not coerce FOR PORTION OF %s bound from %s to %s",
+ "FROM",
+ format_type_be(actual_arg_types[0]),
+ format_type_be(declared_arg_types[0])),
+ parser_errposition(pstate, exprLocation(forPortionOf->target_start))));
+ if (!can_coerce_type(1, &actual_arg_types[1], &declared_arg_types[1], COERCION_IMPLICIT))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("could not coerce FOR PORTION OF %s bound from %s to %s",
+ "TO",
+ format_type_be(actual_arg_types[1]),
+ format_type_be(declared_arg_types[1])),
+ parser_errposition(pstate, exprLocation(forPortionOf->target_end))));
+
+ make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types);
+ result->targetRange = (Node *) makeFuncExpr(get_range_constructor2(attbasetype),
+ attbasetype,
+ args,
+ InvalidOid, InvalidOid, COERCE_EXPLICIT_CALL);
+ }
+ if (contain_volatile_functions_after_planning((Expr *) result->targetRange))
+ ereport(ERROR,
+ (errmsg("FOR PORTION OF bounds cannot contain volatile functions")));
+
+ /*
+ * Build overlapsExpr to use as an extra qual. This means we only hit rows
+ * matching the FROM & TO bounds. We must look up the overlaps operator
+ * (usually "&&").
+ */
+ opclass = GetDefaultOpClass(attr->atttypid, GIST_AM_OID);
+ if (!OidIsValid(opclass))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("data type %s has no default operator class for access method \"%s\"",
+ format_type_be(attr->atttypid), "gist"),
+ errhint("You must define a default operator class for the data type.")));
+
+ /* Look up the operators and functions we need. */
+ GetOperatorFromCompareType(opclass, InvalidOid, COMPARE_OVERLAP, &opid, &strat);
+ op = makeNode(OpExpr);
+ op->opno = opid;
+ op->opfuncid = get_opcode(opid);
+ op->opresulttype = BOOLOID;
+ op->args = list_make2(copyObject(rangeVar), copyObject(result->targetRange));
+ result->overlapsExpr = (Node *) op;
+
+ /*
+ * Look up the without_portion func. This computes the bounds of temporal
+ * leftovers.
+ *
+ * XXX: Find a more extensible way to look up the function, permitting
+ * user-defined types. An opclass support function doesn't make sense,
+ * since there is no index involved. Perhaps a type support function.
+ */
+ if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype))
+ switch (opcintype)
+ {
+ case ANYRANGEOID:
+ result->withoutPortionProc = F_RANGE_MINUS_MULTI;
+ break;
+ case ANYMULTIRANGEOID:
+ result->withoutPortionProc = F_MULTIRANGE_MINUS_MULTI;
+ break;
+ default:
+ elog(ERROR, "unexpected opcintype: %u", opcintype);
+ }
+ else
+ elog(ERROR, "unexpected opclass: %u", opclass);
+
+ if (isUpdate)
+ {
+ /*
+ * Now make sure we update the start/end time of the record. For a
+ * range col (r) this is `r = r * targetRange` (where * is the
+ * intersect operator).
+ */
+ Oid intersectoperoid;
+ List *funcArgs;
+ Node *rangeTLEExpr;
+ TargetEntry *tle;
+
+ /*
+ * Whatever operator is used for intersect by temporal foreign keys,
+ * we can use its backing procedure for intersects in FOR PORTION OF.
+ * XXX: Share code with FindFKPeriodOpers?
+ */
+ switch (opcintype)
+ {
+ case ANYRANGEOID:
+ intersectoperoid = OID_RANGE_INTERSECT_RANGE_OP;
+ break;
+ case ANYMULTIRANGEOID:
+ intersectoperoid = OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP;
+ break;
+ default:
+ elog(ERROR, "unexpected opcintype: %u", opcintype);
+ }
+ funcid = get_opcode(intersectoperoid);
+ if (!OidIsValid(funcid))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("could not identify an intersect function for type %s",
+ format_type_be(opcintype)));
+
+ funcArgs = list_make2(copyObject(rangeVar),
+ copyObject(result->targetRange));
+ rangeTLEExpr = (Node *) makeFuncExpr(funcid, attbasetype, funcArgs,
+ InvalidOid, InvalidOid,
+ COERCE_EXPLICIT_CALL);
+
+ /*
+ * Coerce to domain if necessary. If we skip this, we will allow
+ * updating to forbidden values.
+ */
+ rangeTLEExpr = coerce_type(pstate,
+ rangeTLEExpr,
+ attbasetype,
+ attr->atttypid,
+ -1,
+ COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST,
+ exprLocation(forPortionOf->target));
+
+ /* Make a TLE to set the range column */
+ result->rangeTargetList = NIL;
+ tle = makeTargetEntry((Expr *) rangeTLEExpr, range_attno,
+ forPortionOf->range_name, false);
+ result->rangeTargetList = lappend(result->rangeTargetList, tle);
+
+ /*
+ * The range column will change, but you don't need UPDATE permission
+ * on it, so we don't add to updatedCols here. XXX: If
+ * https://www.postgresql.org/message-id/CACJufxEtY1hdLcx%3DFhnqp-ERcV1PhbvELG5COy_CZjoEW76ZPQ%40mail.gmail.com
+ * is merged (only validate CHECK constraints if they depend on one of
+ * the columns being UPDATEd), we need to make sure that code knows
+ * that we are updating the application-time column.
+ */
+ }
+ else
+ result->rangeTargetList = NIL;
+
+ result->range_name = forPortionOf->range_name;
+ result->location = forPortionOf->location;
+ result->targetLocation = forPortionOf->target_location;
+
+ return result;
+}
/*
* BuildOnConflictExcludedTargetlist
stmt->relation->inh,
true,
ACL_UPDATE);
+
+ if (stmt->forPortionOf)
+ qry->forPortionOf = transformForPortionOfClause(pstate,
+ qry->resultRelation,
+ stmt->forPortionOf,
+ true);
+
nsitem = pstate->p_target_nsitem;
/* subqueries in FROM cannot access the result relation */
* Now we are done with SELECT-like processing, and can get on with
* transforming the target list to match the UPDATE target columns.
*/
- qry->targetList = transformUpdateTargetList(pstate, stmt->targetList);
+ qry->targetList = transformUpdateTargetList(pstate, stmt->targetList,
+ qry->forPortionOf);
qry->rtable = pstate->p_rtable;
qry->rteperminfos = pstate->p_rteperminfos;
* handle SET clause in UPDATE/MERGE/INSERT ... ON CONFLICT UPDATE
*/
List *
-transformUpdateTargetList(ParseState *pstate, List *origTlist)
+transformUpdateTargetList(ParseState *pstate, List *origTlist, ForPortionOfExpr *forPortionOf)
{
List *tlist = NIL;
RTEPermissionInfo *target_perminfo;
errhint("SET target columns cannot be qualified with the relation name.") : 0,
parser_errposition(pstate, origTarget->location)));
+ /*
+ * If this is a FOR PORTION OF update, forbid directly setting the
+ * range column, since that would conflict with the implicit updates.
+ */
+ if (forPortionOf != NULL)
+ {
+ if (attrno == forPortionOf->rangeVar->varattno)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot update column \"%s\" because it is used in FOR PORTION OF",
+ origTarget->name),
+ parser_errposition(pstate, origTarget->location)));
+ }
+
updateTargetListEntry(pstate, tle, origTarget->name,
attrno,
origTarget->indirection,
%type <range> relation_expr
%type <range> extended_relation_expr
%type <range> relation_expr_opt_alias
+%type <alias> for_portion_of_opt_alias
+%type <node> for_portion_of_clause
%type <node> tablesample_clause opt_repeatable_clause
%type <target> target_el set_target insert_column_item
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
PARALLEL PARAMETER PARSER PARTIAL PARTITION PARTITIONS PASSING PASSWORD PATH
- PERIOD PLACING PLAN PLANS POLICY
+ PERIOD PLACING PLAN PLANS POLICY PORTION
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PROPERTIES PROPERTY PUBLICATION
* json_predicate_type_constraint and json_key_uniqueness_constraint_opt
* productions (see comments there).
*
+ * TO is assigned the same precedence as IDENT, to support the opt_interval
+ * production (see comment there).
+ *
* Like the UNBOUNDED PRECEDING/FOLLOWING case, NESTED is assigned a lower
* precedence than PATH to fix ambiguity in the json_table production.
*/
%nonassoc UNBOUNDED NESTED /* ideally would have same precedence as IDENT */
%nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
- SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT PATH
+ SET KEYS OBJECT_P SCALAR TO USING VALUE_P WITH WITHOUT PATH
%left Op OPERATOR RIGHT_ARROW '|' /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
n->withClause = $1;
$$ = (Node *) n;
}
+ | opt_with_clause DELETE_P FROM relation_expr
+ for_portion_of_clause for_portion_of_opt_alias
+ using_clause where_or_current_clause returning_clause
+ {
+ DeleteStmt *n = makeNode(DeleteStmt);
+
+ n->relation = $4;
+ n->forPortionOf = (ForPortionOfClause *) $5;
+ n->relation->alias = $6;
+ n->usingClause = $7;
+ n->whereClause = $8;
+ n->returningClause = $9;
+ n->withClause = $1;
+ $$ = (Node *) n;
+ }
;
using_clause:
n->withClause = $1;
$$ = (Node *) n;
}
+ | opt_with_clause UPDATE relation_expr
+ for_portion_of_clause for_portion_of_opt_alias
+ SET set_clause_list
+ from_clause
+ where_or_current_clause
+ returning_clause
+ {
+ UpdateStmt *n = makeNode(UpdateStmt);
+
+ n->relation = $3;
+ n->forPortionOf = (ForPortionOfClause *) $4;
+ n->relation->alias = $5;
+ n->targetList = $7;
+ n->fromClause = $8;
+ n->whereClause = $9;
+ n->returningClause = $10;
+ n->withClause = $1;
+ $$ = (Node *) n;
+ }
;
set_clause_list:
}
;
+/*
+ * If an UPDATE/DELETE has FOR PORTION OF, then the relation_expr is separated
+ * from its potential alias by the for_portion_of_clause. So this production
+ * handles the potential alias in those cases. We need to solve the same
+ * problems as relation_expr_opt_alias, in particular resolving a shift/reduce
+ * conflict where "set set" could be an alias plus the SET keyword, or the SET
+ * keyword then a column name. As above, we force the latter interpretation by
+ * giving the non-alias choice a higher precedence.
+ */
+for_portion_of_opt_alias:
+ AS ColId
+ {
+ Alias *alias = makeNode(Alias);
+
+ alias->aliasname = $2;
+ $$ = alias;
+ }
+ | BareColLabel
+ {
+ Alias *alias = makeNode(Alias);
+
+ alias->aliasname = $1;
+ $$ = alias;
+ }
+ | /* empty */ %prec UMINUS { $$ = NULL; }
+ ;
+
+for_portion_of_clause:
+ FOR PORTION OF ColId '(' a_expr ')'
+ {
+ ForPortionOfClause *n = makeNode(ForPortionOfClause);
+ n->range_name = $4;
+ n->location = @4;
+ n->target = $6;
+ n->target_location = @6;
+ $$ = (Node *) n;
+ }
+ | FOR PORTION OF ColId FROM a_expr TO a_expr
+ {
+ ForPortionOfClause *n = makeNode(ForPortionOfClause);
+ n->range_name = $4;
+ n->location = @4;
+ n->target_start = $6;
+ n->target_end = $8;
+ n->target_location = @5;
+ $$ = (Node *) n;
+ }
+ ;
+
/*
* TABLESAMPLE decoration in a FROM item
*/
| /*EMPTY*/ { $$ = false; }
;
+/*
+ * We need to handle this shift/reduce conflict:
+ * FOR PORTION OF valid_at FROM t + INTERVAL '1' YEAR TO MONTH.
+ * We don't see far enough ahead to know if there is another TO coming.
+ * We prefer to interpret this as FROM (t + INTERVAL '1' YEAR TO MONTH),
+ * i.e. to shift.
+ * That gives the user the option of adding parentheses to get the other meaning.
+ * If we reduced, intervals could never have a TO.
+ */
opt_interval:
- YEAR_P
+ YEAR_P %prec IS
{ $$ = list_make1(makeIntConst(INTERVAL_MASK(YEAR), @1)); }
| MONTH_P
{ $$ = list_make1(makeIntConst(INTERVAL_MASK(MONTH), @1)); }
- | DAY_P
+ | DAY_P %prec IS
{ $$ = list_make1(makeIntConst(INTERVAL_MASK(DAY), @1)); }
- | HOUR_P
+ | HOUR_P %prec IS
{ $$ = list_make1(makeIntConst(INTERVAL_MASK(HOUR), @1)); }
- | MINUTE_P
+ | MINUTE_P %prec IS
{ $$ = list_make1(makeIntConst(INTERVAL_MASK(MINUTE), @1)); }
| interval_second
{ $$ = $1; }
| PLAN
| PLANS
| POLICY
+ | PORTION
| PRECEDING
| PREPARE
| PREPARED
| PLAN
| PLANS
| POLICY
+ | PORTION
| POSITION
| PRECEDING
| PREPARE
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_FOR_PORTION:
+ if (isAgg)
+ err = _("aggregate functions are not allowed in FOR PORTION OF expressions");
+ else
+ err = _("grouping operations are not allowed in FOR PORTION OF expressions");
+
+ break;
case EXPR_KIND_PROPGRAPH_PROPERTY:
if (isAgg)
case EXPR_KIND_PROPGRAPH_PROPERTY:
err = _("window functions are not allowed in property definition expressions");
break;
+ case EXPR_KIND_FOR_PORTION:
+ err = _("window functions are not allowed in FOR PORTION OF expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
case T_JoinExpr:
case T_FromExpr:
case T_OnConflictExpr:
+ case T_ForPortionOfExpr:
case T_SortGroupClause:
case T_MergeAction:
(void) expression_tree_walker(node,
case EXPR_KIND_PARTITION_BOUND:
err = _("cannot use column reference in partition bound expression");
break;
+ case EXPR_KIND_FOR_PORTION:
+ err = _("cannot use column reference in FOR PORTION OF expression");
+ break;
/*
* There is intentionally no default: case here, so that the
case EXPR_KIND_PROPGRAPH_PROPERTY:
err = _("cannot use subquery in property definition expression");
break;
+ case EXPR_KIND_FOR_PORTION:
+ err = _("cannot use subquery in FOR PORTION OF expression");
+ break;
/*
* There is intentionally no default: case here, so that the
return "CYCLE";
case EXPR_KIND_PROPGRAPH_PROPERTY:
return "property definition expression";
+ case EXPR_KIND_FOR_PORTION:
+ return "FOR PORTION OF";
/*
* There is intentionally no default: case here, so that the
case EXPR_KIND_PROPGRAPH_PROPERTY:
err = _("set-returning functions are not allowed in property definition expressions");
break;
+ case EXPR_KIND_FOR_PORTION:
+ err = _("set-returning functions are not allowed in FOR PORTION OF expressions");
+ break;
/*
* There is intentionally no default: case here, so that the
case CMD_UPDATE:
action->targetList =
transformUpdateTargetList(pstate,
- mergeWhenClause->targetList);
+ mergeWhenClause->targetList, NULL);
break;
case CMD_DELETE:
break;
&parsetree->hasSubLinks);
}
+ if (parsetree->forPortionOf && parsetree->commandType == CMD_UPDATE)
+ {
+ /*
+ * Like the INSERT/UPDATE code above, update the resnos in the
+ * auxiliary UPDATE targetlist to refer to columns of the base
+ * relation.
+ */
+ foreach(lc, parsetree->forPortionOf->rangeTargetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ TargetEntry *view_tle;
+
+ if (tle->resjunk)
+ continue;
+
+ view_tle = get_tle_by_resno(view_targetlist, tle->resno);
+ if (view_tle != NULL && !view_tle->resjunk && IsA(view_tle->expr, Var))
+ tle->resno = ((Var *) view_tle->expr)->varattno;
+ else
+ elog(ERROR, "attribute number %d not found in view targetlist",
+ tle->resno);
+ }
+ }
+
/*
* For UPDATE/DELETE/MERGE, pull up any WHERE quals from the view. We
* know that any Vars in the quals must reference the one base relation,
else if (event == CMD_UPDATE)
{
Assert(parsetree->override == OVERRIDING_NOT_SET);
+
+ if (parsetree->forPortionOf)
+ {
+ /*
+ * Don't add FOR PORTION OF details until we're done rewriting
+ * a view update, so that we don't add the same qual and TLE
+ * on the recursion.
+ *
+ * Views don't need to do anything special here to remap Vars;
+ * that is handled by the tree walker.
+ */
+ if (rt_entry_relation->rd_rel->relkind != RELKIND_VIEW)
+ {
+ ListCell *tl;
+
+ /*
+ * Add qual: UPDATE FOR PORTION OF should be limited to
+ * rows that overlap the target range.
+ */
+ AddQual(parsetree, parsetree->forPortionOf->overlapsExpr);
+
+ /* Update FOR PORTION OF column(s) automatically. */
+ foreach(tl, parsetree->forPortionOf->rangeTargetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(tl);
+
+ parsetree->targetList = lappend(parsetree->targetList, tle);
+ }
+ }
+ }
+
parsetree->targetList =
rewriteTargetListIU(parsetree->targetList,
parsetree->commandType,
}
else if (event == CMD_DELETE)
{
- /* Nothing to do here */
+ if (parsetree->forPortionOf)
+ {
+ /*
+ * Don't add FOR PORTION OF details until we're done rewriting
+ * a view delete, so that we don't add the same qual on the
+ * recursion.
+ *
+ * Views don't need to do anything special here to remap Vars;
+ * that is handled by the tree walker.
+ */
+ if (rt_entry_relation->rd_rel->relkind != RELKIND_VIEW)
+ {
+ /*
+ * Add qual: DELETE FOR PORTION OF should be limited to
+ * rows that overlap the target range.
+ */
+ AddQual(parsetree, parsetree->forPortionOf->overlapsExpr);
+ }
+ }
}
else
elog(ERROR, "unrecognized commandType: %d", (int) event);
deparse_context *context);
static void get_column_alias_list(deparse_columns *colinfo,
deparse_context *context);
+static void get_for_portion_of(ForPortionOfExpr *forPortionOf,
+ deparse_context *context);
static void get_from_clause_coldeflist(RangeTblFunction *rtfunc,
deparse_columns *colinfo,
deparse_context *context);
only_marker(rte),
generate_relation_name(rte->relid, NIL));
+ /* Print the FOR PORTION OF, if needed */
+ get_for_portion_of(query->forPortionOf, context);
+
/* Print the relation alias, if needed */
get_rte_alias(rte, query->resultRelation, false, context);
only_marker(rte),
generate_relation_name(rte->relid, NIL));
+ /* Print the FOR PORTION OF, if needed */
+ get_for_portion_of(query->forPortionOf, context);
+
/* Print the relation alias, if needed */
get_rte_alias(rte, query->resultRelation, false, context);
quote_identifier(refname));
}
+/*
+ * get_for_portion_of - print FOR PORTION OF if needed
+ * XXX: Newlines would help here, at least when pretty-printing. But then the
+ * alias and SET will be on their own line with a leading space.
+ */
+static void
+get_for_portion_of(ForPortionOfExpr *forPortionOf, deparse_context *context)
+{
+ if (forPortionOf)
+ {
+ appendStringInfo(context->buf, " FOR PORTION OF %s",
+ quote_identifier(forPortionOf->range_name));
+
+ /*
+ * Try to write it as FROM ... TO ... if we received it that way,
+ * otherwise (targetExpr).
+ */
+ if (forPortionOf->targetFrom && forPortionOf->targetTo)
+ {
+ appendStringInfoString(context->buf, " FROM ");
+ get_rule_expr(forPortionOf->targetFrom, context, false);
+ appendStringInfoString(context->buf, " TO ");
+ get_rule_expr(forPortionOf->targetTo, context, false);
+ }
+ else
+ {
+ appendStringInfoString(context->buf, " (");
+ get_rule_expr(forPortionOf->targetRange, context, false);
+ appendStringInfoString(context->buf, ")");
+ }
+ }
+}
+
/*
* get_column_alias_list - print column alias list for an RTE
*
return InvalidOid;
}
+/*
+ * get_range_constructor2
+ * Gets the 2-arg constructor for the given rangetype.
+ *
+ * Raises an error if not found.
+ */
+RegProcedure
+get_range_constructor2(Oid rangeOid)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(rangeOid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_range rngtup = (Form_pg_range) GETSTRUCT(tp);
+ RegProcedure result;
+
+ result = rngtup->rngconstruct2;
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ elog(ERROR, "cache lookup failed for range type %u", rangeOid);
+}
+
/*
* get_range_multirange
* Returns the multirange type of a given range type
#include "partitioning/partdefs.h"
#include "storage/buf.h"
#include "utils/reltrigger.h"
+#include "utils/typcache.h"
/*
ExprState *mas_whenqual; /* WHEN [NOT] MATCHED AND conditions */
} MergeActionState;
+/*
+ * ForPortionOfState
+ *
+ * Executor state of a FOR PORTION OF operation.
+ */
+typedef struct ForPortionOfState
+{
+ NodeTag type;
+
+ char *fp_rangeName; /* the column named in FOR PORTION OF */
+ Oid fp_rangeType; /* the type of the FOR PORTION OF expression */
+ int fp_rangeAttno; /* the attno of the range column */
+ Datum fp_targetRange; /* the range/multirange from FOR PORTION OF */
+ TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */
+ TupleTableSlot *fp_Existing; /* slot to store old tuple */
+ TupleTableSlot *fp_Leftover; /* slot to store leftover */
+} ForPortionOfState;
+
/*
* ResultRelInfo
*
/* for MERGE, expr state for checking the join condition */
ExprState *ri_MergeJoinCondition;
+ /* FOR PORTION OF evaluation state */
+ ForPortionOfState *ri_forPortionOf;
+
/* partition check expression state (NULL if not set up yet) */
ExprState *ri_PartitionCheckExpr;
*/
int resultRelation pg_node_attr(query_jumble_ignore);
+ /* FOR PORTION OF clause for UPDATE/DELETE */
+ ForPortionOfExpr *forPortionOf;
+
/* has aggregates in tlist or havingQual */
bool hasAggs pg_node_attr(query_jumble_ignore);
/* has window functions in tlist */
bool pushedDown; /* pushed down from higher query level? */
} RowMarkClause;
+/*
+ * ForPortionOfClause
+ * representation of FOR PORTION OF <range-name> FROM <target-start> TO
+ * <target-end> or FOR PORTION OF <range-name> (<target>)
+ */
+typedef struct ForPortionOfClause
+{
+ NodeTag type;
+ char *range_name; /* column name of the range/multirange */
+ ParseLoc location; /* token location, or -1 if unknown */
+ ParseLoc target_location; /* token location, or -1 if unknown */
+ Node *target; /* Expr from FOR PORTION OF col (...) syntax */
+ Node *target_start; /* Expr from FROM ... TO ... syntax */
+ Node *target_end; /* Expr from FROM ... TO ... syntax */
+} ForPortionOfClause;
+
/*
* WithClause -
* representation of WITH clause
Node *whereClause; /* qualifications */
ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
+ ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */
} DeleteStmt;
/* ----------------------
List *fromClause; /* optional from clause for more tables */
ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
+ ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */
} UpdateStmt;
/* ----------------------
List *returningLists; /* per-target-table RETURNING tlists */
List *rowMarks; /* PlanRowMarks (non-locking only) */
OnConflictExpr *onconflict; /* ON CONFLICT clause, or NULL */
+ ForPortionOfExpr *forPortionOf; /* FOR PORTION OF clause for UPDATE/DELETE */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
List *mergeActionLists; /* per-target-table lists of actions for
* MERGE */
List *onConflictCols;
/* WHERE for ON CONFLICT DO SELECT/UPDATE */
Node *onConflictWhere;
+ /* FOR PORTION OF clause for UPDATE/DELETE */
+ Node *forPortionOf;
/* RTI of the EXCLUDED pseudo relation */
Index exclRelRTI;
/* tlist of the EXCLUDED pseudo relation */
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
+/*----------
+ * ForPortionOfExpr - represents a FOR PORTION OF ... expression
+ *
+ * We set up an expression to make a range from the FROM/TO bounds,
+ * so that we can use range operators with it.
+ *
+ * Then we set up an overlaps expression between that and the range column,
+ * so that we can find the rows we need to update/delete.
+ *
+ * If the user used the FROM ... TO ... syntax, we save the individual
+ * expressions so that we can deparse them.
+ *
+ * In the executor we'll also build an intersect expression between the
+ * targeted range and the range column, so that we can update the start/end
+ * bounds of the UPDATE'd record.
+ *----------
+ */
+typedef struct ForPortionOfExpr
+{
+ NodeTag type;
+ Var *rangeVar; /* Range column */
+ char *range_name; /* Range name */
+ Node *targetFrom; /* FOR PORTION OF FROM bound, if given */
+ Node *targetTo; /* FOR PORTION OF TO bound, if given */
+ Node *targetRange; /* FOR PORTION OF bounds as a range/multirange */
+ Oid rangeType; /* (base)type of targetRange */
+ bool isDomain; /* Is rangeVar a domain? */
+ Node *overlapsExpr; /* range && targetRange */
+ List *rangeTargetList; /* List of TargetEntrys to set the time
+ * column(s) */
+ Oid withoutPortionProc; /* SRF proc for old_range - target_range */
+ ParseLoc location; /* token location, or -1 if unknown */
+ ParseLoc targetLocation; /* token location, or -1 if unknown */
+} ForPortionOfExpr;
+
#endif /* PRIMNODES_H */
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
List *mergeActionLists, List *mergeJoinConditions,
- int epqParam);
+ ForPortionOfExpr *forPortionOf, int epqParam);
extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
List *stmtcols, List *icolumns, List *attrnos,
bool strip_indirection);
extern List *transformUpdateTargetList(ParseState *pstate,
- List *origTlist);
+ List *origTlist,
+ ForPortionOfExpr *forPortionOf);
extern void transformReturningClause(ParseState *pstate, Query *qry,
ReturningClause *returningClause,
ParseExprKind exprKind);
PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("portion", PORTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("preceding", PRECEDING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("precision", PRECISION, COL_NAME_KEYWORD, AS_LABEL)
EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */
EXPR_KIND_UPDATE_TARGET, /* UPDATE assignment target item */
EXPR_KIND_MERGE_WHEN, /* MERGE WHEN [NOT] MATCHED condition */
+ EXPR_KIND_FOR_PORTION, /* UPDATE/DELETE FOR PORTION OF item */
EXPR_KIND_GROUP_BY, /* GROUP BY */
EXPR_KIND_ORDER_BY, /* ORDER BY */
EXPR_KIND_DISTINCT_ON, /* DISTINCT ON */
extern char *get_namespace_name_or_temp(Oid nspid);
extern Oid get_range_subtype(Oid rangeOid);
extern Oid get_range_collation(Oid rangeOid);
+extern Oid get_range_constructor2(Oid rangeOid);
extern Oid get_range_multirange(Oid rangeOid);
extern Oid get_multirange_range(Oid multirangeOid);
extern Oid get_index_column_opclass(Oid index_oid, int attno);
--- /dev/null
+-- Tests for UPDATE/DELETE FOR PORTION OF
+SET datestyle TO ISO, YMD;
+-- Works on non-PK columns
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2020-01-01)', 'one');
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = 'one^1';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-01-15) | one
+ [1,2) | [2018-01-15,2019-01-01) | one^1
+ [1,2) | [2019-01-01,2020-01-01) | one
+(3 rows)
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-01-15) | one
+ [1,2) | [2018-01-15,2019-01-01) | one^1
+ [1,2) | [2019-01-01,2019-01-15) | one
+ [1,2) | [2019-01-20,2020-01-01) | one
+(4 rows)
+
+-- With a table alias with AS
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t
+ SET name = 'one^2';
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t;
+-- With a table alias without AS
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t
+ SET name = 'one^3';
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t;
+-- UPDATE with FROM
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02'
+ SET name = 'one^4'
+ FROM (SELECT '[1,2)'::int4range) AS t2(id)
+ WHERE for_portion_of_test.id = t2.id;
+-- DELETE with USING
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03'
+ USING (SELECT '[1,2)'::int4range) AS t2(id)
+ WHERE for_portion_of_test.id = t2.id;
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-01-15) | one
+ [1,2) | [2018-01-15,2019-01-01) | one^1
+ [1,2) | [2019-01-01,2019-01-15) | one
+ [1,2) | [2019-01-20,2019-02-01) | one
+ [1,2) | [2019-02-01,2019-02-03) | one^2
+ [1,2) | [2019-02-04,2019-02-05) | one^3
+ [1,2) | [2019-02-06,2019-03-01) | one
+ [1,2) | [2019-03-01,2019-03-02) | one^4
+ [1,2) | [2019-03-03,2020-01-01) | one
+(9 rows)
+
+-- Works on more than one range
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid1_at daterange,
+ valid2_at daterange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test (id, valid1_at, valid2_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one');
+UPDATE for_portion_of_test
+ FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL
+ SET name = 'foo';
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+ id | valid1_at | valid2_at | name
+-------+-------------------------+-------------------------+------
+ [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2025-01-01) | one
+ [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2025-01-01) | foo
+(2 rows)
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL
+ SET name = 'bar';
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+ id | valid1_at | valid2_at | name
+-------+-------------------------+-------------------------+------
+ [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one
+ [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar
+ [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2018-01-15) | foo
+ [1,2) | [2018-01-15,2018-02-03) | [2018-01-15,2025-01-01) | bar
+(4 rows)
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL;
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+ id | valid1_at | valid2_at | name
+-------+-------------------------+-------------------------+------
+ [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one
+ [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar
+ [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo
+ [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2025-01-01) | bar
+(4 rows)
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL;
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+ id | valid1_at | valid2_at | name
+-------+-------------------------+-------------------------+------
+ [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one
+ [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2018-01-20) | bar
+ [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo
+ [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2018-01-20) | bar
+(4 rows)
+
+-- Test with NULLs in the scalar/range key columns.
+-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint
+-- but FOR PORTION OF shouldn't require that.
+DROP TABLE for_portion_of_test;
+CREATE UNLOGGED TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', NULL, '1 null'),
+ ('[1,2)', '(,)', '1 unbounded'),
+ ('[1,2)', 'empty', '1 empty'),
+ (NULL, NULL, NULL),
+ (NULL, daterange('2018-01-01', '2019-01-01'), 'null key');
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ SET name = 'NULL to NULL';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+--------------
+ [1,2) | empty | 1 empty
+ [1,2) | (,) | NULL to NULL
+ [1,2) | | 1 null
+ | [2018-01-01,2019-01-01) | NULL to NULL
+ | |
+(5 rows)
+
+DROP TABLE for_portion_of_test;
+--
+-- UPDATE tests
+--
+CREATE TABLE for_portion_of_test (
+ id int4range NOT NULL,
+ valid_at daterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+ ('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+ ('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+ ('[2,3)', '[2018-01-01,2018-01-05)', 'two'),
+ ('[3,4)', '[2018-01-01,)', 'three'),
+ ('[4,5)', '(,2018-04-01)', 'four'),
+ ('[5,6)', '(,)', 'five')
+ ;
+\set QUIET false
+-- Updating with a missing column fails
+UPDATE for_portion_of_test
+ FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+ SET name = 'foo'
+ WHERE id = '[5,6)';
+ERROR: column "invalid_at" of relation "for_portion_of_test" does not exist
+LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+ ^
+-- Updating the range fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO NULL
+ SET valid_at = '[1990-01-01,1999-01-01)'
+ WHERE id = '[5,6)';
+ERROR: cannot update column "valid_at" because it is used in FOR PORTION OF
+LINE 3: SET valid_at = '[1990-01-01,1999-01-01)'
+ ^
+-- The wrong start type fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM 1 TO '2020-01-01'
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+ERROR: could not coerce FOR PORTION OF FROM bound from integer to date
+LINE 2: FOR PORTION OF valid_at FROM 1 TO '2020-01-01'
+ ^
+-- The wrong end type fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2000-01-01' TO 4
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+ERROR: could not coerce FOR PORTION OF TO bound from integer to date
+LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO 4
+ ^
+-- Updating with timestamps reversed fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+ SET name = 'three^1'
+ WHERE id = '[3,4)';
+ERROR: range lower bound must be less than or equal to range upper bound
+-- Updating with a subquery fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01'
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+ERROR: cannot use subquery in FOR PORTION OF expression
+LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '201...
+ ^
+-- Updating with a column fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM lower(valid_at) TO NULL
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+ERROR: cannot use column reference in FOR PORTION OF expression
+LINE 2: FOR PORTION OF valid_at FROM lower(valid_at) TO NULL
+ ^
+-- Updating with timestamps equal does nothing
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+ SET name = 'three^0'
+ WHERE id = '[3,4)';
+UPDATE 0
+-- Updating a finite/open portion with a finite/open target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO NULL
+ SET name = 'three^1'
+ WHERE id = '[3,4)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+---------
+ [3,4) | [2018-01-01,2018-06-01) | three
+ [3,4) | [2018-06-01,) | three^1
+(2 rows)
+
+-- Updating a finite/open portion with an open/finite target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-03-01'
+ SET name = 'three^2'
+ WHERE id = '[3,4)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+---------
+ [3,4) | [2018-01-01,2018-03-01) | three^2
+ [3,4) | [2018-03-01,2018-06-01) | three
+ [3,4) | [2018-06-01,) | three^1
+(3 rows)
+
+-- Updating an open/finite portion with an open/finite target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-02-01'
+ SET name = 'four^1'
+ WHERE id = '[4,5)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+--------
+ [4,5) | (,2018-02-01) | four^1
+ [4,5) | [2018-02-01,2018-04-01) | four
+(2 rows)
+
+-- Updating an open/finite portion with a finite/open target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO NULL
+ SET name = 'four^2'
+ WHERE id = '[4,5)';
+UPDATE 2
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+--------
+ [4,5) | (,2017-01-01) | four^1
+ [4,5) | [2017-01-01,2018-02-01) | four^2
+ [4,5) | [2018-02-01,2018-04-01) | four^2
+(3 rows)
+
+-- Updating a finite/finite portion with an exact fit
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01'
+ SET name = 'four^3'
+ WHERE id = '[4,5)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+--------
+ [4,5) | (,2017-01-01) | four^1
+ [4,5) | [2017-01-01,2018-02-01) | four^3
+ [4,5) | [2018-02-01,2018-04-01) | four^2
+(3 rows)
+
+-- Updating an enclosed span
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ SET name = 'two^2'
+ WHERE id = '[2,3)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [2,3) | [2018-01-01,2018-01-05) | two^2
+(1 row)
+
+-- Updating an open/open portion with a finite/finite target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01'
+ SET name = 'five^1'
+ WHERE id = '[5,6)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+--------
+ [5,6) | (,2018-01-01) | five
+ [5,6) | [2018-01-01,2019-01-01) | five^1
+ [5,6) | [2019-01-01,) | five
+(3 rows)
+
+-- Updating an enclosed span with separate protruding spans
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01'
+ SET name = 'five^2'
+ WHERE id = '[5,6)';
+UPDATE 3
+SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+--------
+ [5,6) | (,2017-01-01) | five
+ [5,6) | [2017-01-01,2018-01-01) | five^2
+ [5,6) | [2018-01-01,2019-01-01) | five^2
+ [5,6) | [2019-01-01,2020-01-01) | five^2
+ [5,6) | [2020-01-01,) | five
+(5 rows)
+
+-- Updating multiple enclosed spans
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ SET name = 'one^2'
+ WHERE id = '[1,2)';
+UPDATE 3
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-02-03) | one^2
+ [1,2) | [2018-02-03,2018-03-03) | one^2
+ [1,2) | [2018-03-03,2018-04-04) | one^2
+(3 rows)
+
+-- Updating with a direct target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15'))
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-02-03) | one^2
+ [1,2) | [2018-02-03,2018-03-03) | one^2
+ [1,2) | [2018-03-03,2018-03-10) | one^2
+ [1,2) | [2018-03-10,2018-03-15) | one^3
+ [1,2) | [2018-03-15,2018-04-04) | one^2
+(5 rows)
+
+-- Updating with a direct target, coerced from a string
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)')
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+UPDATE 1
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-02-03) | one^2
+ [1,2) | [2018-02-03,2018-03-03) | one^2
+ [1,2) | [2018-03-03,2018-03-10) | one^2
+ [1,2) | [2018-03-10,2018-03-15) | one^3
+ [1,2) | [2018-03-15,2018-03-17) | one^3
+ [1,2) | [2018-03-17,2018-04-04) | one^2
+(6 rows)
+
+-- Updating with a direct target of the wrong range subtype fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (int4range(1, 4))
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+ERROR: could not coerce FOR PORTION OF target from int4range to daterange
+LINE 2: FOR PORTION OF valid_at (int4range(1, 4))
+ ^
+-- Updating with a direct target of a non-rangetype fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (4)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+ERROR: could not coerce FOR PORTION OF target from integer to daterange
+LINE 2: FOR PORTION OF valid_at (4)
+ ^
+-- Updating with a direct target of NULL fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (NULL)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+ERROR: FOR PORTION OF target was null
+LINE 2: FOR PORTION OF valid_at (NULL)
+ ^
+-- Updating with a direct target of empty does nothing
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at ('empty')
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+UPDATE 0
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-02-03) | one^2
+ [1,2) | [2018-02-03,2018-03-03) | one^2
+ [1,2) | [2018-03-03,2018-03-10) | one^2
+ [1,2) | [2018-03-10,2018-03-15) | one^3
+ [1,2) | [2018-03-15,2018-03-17) | one^3
+ [1,2) | [2018-03-17,2018-04-04) | one^2
+(6 rows)
+
+-- Updating the non-range part of the PK:
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-15' TO NULL
+ SET id = '[6,7)'
+ WHERE id = '[1,2)';
+UPDATE 5
+SELECT * FROM for_portion_of_test WHERE id IN ('[1,2)', '[6,7)') ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-02-03) | one^2
+ [1,2) | [2018-02-03,2018-02-15) | one^2
+ [6,7) | [2018-02-15,2018-03-03) | one^2
+ [6,7) | [2018-03-03,2018-03-10) | one^2
+ [6,7) | [2018-03-10,2018-03-15) | one^3
+ [6,7) | [2018-03-15,2018-03-17) | one^3
+ [6,7) | [2018-03-17,2018-04-04) | one^2
+(7 rows)
+
+-- UPDATE with no WHERE clause
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2030-01-01' TO NULL
+ SET name = name || '*';
+UPDATE 2
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+----------
+ [1,2) | [2018-01-02,2018-02-03) | one^2
+ [1,2) | [2018-02-03,2018-02-15) | one^2
+ [2,3) | [2018-01-01,2018-01-05) | two^2
+ [3,4) | [2018-01-01,2018-03-01) | three^2
+ [3,4) | [2018-03-01,2018-06-01) | three
+ [3,4) | [2018-06-01,2030-01-01) | three^1
+ [3,4) | [2030-01-01,) | three^1*
+ [4,5) | (,2017-01-01) | four^1
+ [4,5) | [2017-01-01,2018-02-01) | four^3
+ [4,5) | [2018-02-01,2018-04-01) | four^2
+ [5,6) | (,2017-01-01) | five
+ [5,6) | [2017-01-01,2018-01-01) | five^2
+ [5,6) | [2018-01-01,2019-01-01) | five^2
+ [5,6) | [2019-01-01,2020-01-01) | five^2
+ [5,6) | [2020-01-01,2030-01-01) | five
+ [5,6) | [2030-01-01,) | five*
+ [6,7) | [2018-02-15,2018-03-03) | one^2
+ [6,7) | [2018-03-03,2018-03-10) | one^2
+ [6,7) | [2018-03-10,2018-03-15) | one^3
+ [6,7) | [2018-03-15,2018-03-17) | one^3
+ [6,7) | [2018-03-17,2018-04-04) | one^2
+(21 rows)
+
+\set QUIET true
+-- Updating with a shift/reduce conflict
+-- (requires a tsrange column)
+CREATE UNLOGGED TABLE for_portion_of_test2 (
+ id int4range,
+ valid_at tsrange,
+ name text
+);
+INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES
+ ('[1,2)', '[2000-01-01,2020-01-01)', 'one');
+-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes)
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at
+ FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE
+ TO '2012-01-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+-- TO is used for the bound but not the INTERVAL:
+-- syntax error
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at
+ FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR
+ TO '2014-01-01'
+ SET name = 'one^2'
+ WHERE id = '[1,2)';
+ERROR: syntax error at or near "'2014-01-01'"
+LINE 4: TO '2014-01-01'
+ ^
+-- adding parens fixes it
+-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes)
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at
+ FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR)
+ TO '2016-01-01'
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-----------------------------------------------+-------
+ [1,2) | ["2000-01-01 00:00:00","2011-03-01 01:02:00") | one
+ [1,2) | ["2011-03-01 01:02:00","2012-01-01 00:00:00") | one^1
+ [1,2) | ["2012-01-01 00:00:00","2015-03-01 01:00:00") | one
+ [1,2) | ["2015-03-01 01:00:00","2016-01-01 00:00:00") | one^3
+ [1,2) | ["2016-01-01 00:00:00","2020-01-01 00:00:00") | one
+(5 rows)
+
+DROP TABLE for_portion_of_test2;
+-- UPDATE FOR PORTION OF in a CTE:
+-- The outer query sees the table how it was before the updates,
+-- and with no leftovers yet,
+-- but it also sees the new values via the RETURNING clause.
+-- (We test RETURNING more directly, without a CTE, below.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[10,11)', '[2018-01-01,2020-01-01)', 'ten');
+WITH update_apr AS (
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01'
+ SET name = 'Apr 2018'
+ WHERE id = '[10,11)'
+ RETURNING id, valid_at, name
+)
+SELECT *
+ FROM for_portion_of_test AS t, update_apr
+ WHERE t.id = update_apr.id;
+ id | valid_at | name | id | valid_at | name
+---------+-------------------------+------+---------+-------------------------+----------
+ [10,11) | [2018-01-01,2020-01-01) | ten | [10,11) | [2018-04-01,2018-05-01) | Apr 2018
+(1 row)
+
+SELECT * FROM for_portion_of_test WHERE id = '[10,11)' ORDER BY id, valid_at;
+ id | valid_at | name
+---------+-------------------------+----------
+ [10,11) | [2018-01-01,2018-04-01) | ten
+ [10,11) | [2018-04-01,2018-05-01) | Apr 2018
+ [10,11) | [2018-05-01,2020-01-01) | ten
+(3 rows)
+
+-- UPDATE FOR PORTION OF with current_date
+-- (We take care not to make the expectation depend on the timestamp.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[99,100)', '[2000-01-01,)', 'foo');
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM current_date TO null
+ SET name = 'bar'
+ WHERE id = '[99,100)';
+SELECT name, lower(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date - 1;
+ name | lower
+------+------------
+ foo | 2000-01-01
+(1 row)
+
+SELECT name, upper(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date + 1;
+ name | upper
+------+-------
+ bar |
+(1 row)
+
+-- UPDATE FOR PORTION OF with clock_timestamp()
+-- fails because the function is volatile:
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM clock_timestamp()::date TO null
+ SET name = 'baz'
+ WHERE id = '[99,100)';
+ERROR: FOR PORTION OF bounds cannot contain volatile functions
+-- clean up:
+DELETE FROM for_portion_of_test WHERE id = '[99,100)';
+-- Not visible to UPDATE:
+-- Tuples updated/inserted within the CTE are not visible to the main query yet,
+-- but neither are old tuples the CTE changed:
+-- (This is the same behavior as without FOR PORTION OF.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[11,12)', '[2018-01-01,2020-01-01)', 'eleven');
+WITH update_apr AS (
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01'
+ SET name = 'Apr 2018'
+ WHERE id = '[11,12)'
+ RETURNING id, valid_at, name
+)
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ AS t
+ SET name = 'May 2018'
+ FROM update_apr AS j
+ WHERE t.id = j.id;
+SELECT * FROM for_portion_of_test WHERE id = '[11,12)' ORDER BY id, valid_at;
+ id | valid_at | name
+---------+-------------------------+----------
+ [11,12) | [2018-01-01,2018-04-01) | eleven
+ [11,12) | [2018-04-01,2018-05-01) | Apr 2018
+ [11,12) | [2018-05-01,2020-01-01) | eleven
+(3 rows)
+
+DELETE FROM for_portion_of_test WHERE id IN ('[10,11)', '[11,12)');
+-- UPDATE FOR PORTION OF in a PL/pgSQL function
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[10,11)', '[2018-01-01,2020-01-01)', 'ten');
+CREATE FUNCTION fpo_update(_id int4range, _target_from date, _target_til date)
+RETURNS void LANGUAGE plpgsql AS
+$$
+BEGIN
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM $2 TO $3
+ SET name = concat(_target_from::text, ' to ', _target_til::text)
+ WHERE id = $1;
+END;
+$$;
+SELECT fpo_update('[10,11)', '2015-01-01', '2019-01-01');
+ fpo_update
+------------
+
+(1 row)
+
+SELECT * FROM for_portion_of_test WHERE id = '[10,11)';
+ id | valid_at | name
+---------+-------------------------+--------------------------
+ [10,11) | [2018-01-01,2019-01-01) | 2015-01-01 to 2019-01-01
+ [10,11) | [2019-01-01,2020-01-01) | ten
+(2 rows)
+
+-- UPDATE FOR PORTION OF in a compiled SQL function
+CREATE FUNCTION fpo_update()
+RETURNS text
+BEGIN ATOMIC
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = 'one^1'
+ RETURNING name;
+END;
+\sf+ fpo_update()
+ CREATE OR REPLACE FUNCTION public.fpo_update()
+ RETURNS text
+ LANGUAGE sql
+1 BEGIN ATOMIC
+2 UPDATE for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' SET name = 'one^1'::text
+3 RETURNING for_portion_of_test.name;
+4 END
+CREATE OR REPLACE function fpo_update()
+RETURNS text
+BEGIN ATOMIC
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01'))
+ SET name = 'one^1'
+ RETURNING name;
+END;
+\sf+ fpo_update()
+ CREATE OR REPLACE FUNCTION public.fpo_update()
+ RETURNS text
+ LANGUAGE sql
+1 BEGIN ATOMIC
+2 UPDATE for_portion_of_test FOR PORTION OF valid_at ((daterange('2018-01-15'::date, '2020-01-01'::date) * daterange('2019-01-01'::date, '2022-01-01'::date))) SET name = 'one^1'::text
+3 RETURNING for_portion_of_test.name;
+4 END
+DROP FUNCTION fpo_update();
+DROP TABLE for_portion_of_test;
+--
+-- DELETE tests
+--
+CREATE TABLE for_portion_of_test (
+ id int4range NOT NULL,
+ valid_at daterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+ ('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+ ('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+ ('[2,3)', '[2018-01-01,2018-01-05)', 'two'),
+ ('[3,4)', '[2018-01-01,)', 'three'),
+ ('[4,5)', '(,2018-04-01)', 'four'),
+ ('[5,6)', '(,)', 'five'),
+ ('[6,7)', '[2018-01-01,)', 'six'),
+ ('[7,8)', '(,2018-04-01)', 'seven'),
+ ('[8,9)', '[2018-01-02,2018-02-03)', 'eight'),
+ ('[8,9)', '[2018-02-03,2018-03-03)', 'eight'),
+ ('[8,9)', '[2018-03-03,2018-04-04)', 'eight')
+ ;
+\set QUIET false
+-- Deleting with a missing column fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+ WHERE id = '[5,6)';
+ERROR: column "invalid_at" of relation "for_portion_of_test" does not exist
+LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+ ^
+-- The wrong start type fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM 1 TO '2020-01-01'
+ WHERE id = '[3,4)';
+ERROR: could not coerce FOR PORTION OF FROM bound from integer to date
+LINE 2: FOR PORTION OF valid_at FROM 1 TO '2020-01-01'
+ ^
+-- The wrong end type fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2000-01-01' TO 4
+ WHERE id = '[3,4)';
+ERROR: could not coerce FOR PORTION OF TO bound from integer to date
+LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO 4
+ ^
+-- Deleting with timestamps reversed fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+ WHERE id = '[3,4)';
+ERROR: range lower bound must be less than or equal to range upper bound
+-- Deleting with a subquery fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01'
+ WHERE id = '[3,4)';
+ERROR: cannot use subquery in FOR PORTION OF expression
+LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '201...
+ ^
+-- Deleting with a column fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM lower(valid_at) TO NULL
+ WHERE id = '[3,4)';
+ERROR: cannot use column reference in FOR PORTION OF expression
+LINE 2: FOR PORTION OF valid_at FROM lower(valid_at) TO NULL
+ ^
+-- Deleting with timestamps equal does nothing
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+ WHERE id = '[3,4)';
+DELETE 0
+-- Deleting a finite/open portion with a finite/open target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO NULL
+ WHERE id = '[3,4)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [3,4) | [2018-01-01,2018-06-01) | three
+(1 row)
+
+-- Deleting a finite/open portion with an open/finite target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-03-01'
+ WHERE id = '[6,7)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[6,7)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+---------------+------
+ [6,7) | [2018-03-01,) | six
+(1 row)
+
+-- Deleting an open/finite portion with an open/finite target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-02-01'
+ WHERE id = '[4,5)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [4,5) | [2018-02-01,2018-04-01) | four
+(1 row)
+
+-- Deleting an open/finite portion with a finite/open target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO NULL
+ WHERE id = '[7,8)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[7,8)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+---------------+-------
+ [7,8) | (,2017-01-01) | seven
+(1 row)
+
+-- Deleting a finite/finite portion with an exact fit
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-04-01'
+ WHERE id = '[4,5)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+-- Deleting an enclosed span
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ WHERE id = '[2,3)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+-- Deleting an open/open portion with a finite/finite target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01'
+ WHERE id = '[5,6)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+---------------+------
+ [5,6) | (,2018-01-01) | five
+ [5,6) | [2019-01-01,) | five
+(2 rows)
+
+-- Deleting an enclosed span with separate protruding spans
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-03' TO '2018-03-03'
+ WHERE id = '[1,2)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [1,2) | [2018-01-02,2018-02-03) | one
+ [1,2) | [2018-03-03,2018-04-04) | one
+(2 rows)
+
+-- Deleting multiple enclosed spans
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ WHERE id = '[8,9)';
+DELETE 3
+SELECT * FROM for_portion_of_test WHERE id = '[8,9)' ORDER BY id, valid_at;
+ id | valid_at | name
+----+----------+------
+(0 rows)
+
+-- Deleting with a direct target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15'))
+ WHERE id = '[1,2)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [1,2) | [2018-01-02,2018-02-03) | one
+ [1,2) | [2018-03-03,2018-03-10) | one
+ [1,2) | [2018-03-15,2018-04-04) | one
+(3 rows)
+
+-- Deleting with a direct target, coerced from a string
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)')
+ WHERE id = '[1,2)';
+DELETE 1
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [1,2) | [2018-01-02,2018-02-03) | one
+ [1,2) | [2018-03-03,2018-03-10) | one
+ [1,2) | [2018-03-17,2018-04-04) | one
+(3 rows)
+
+-- Deleting with a direct target of the wrong range subtype fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (int4range(1, 4))
+ WHERE id = '[1,2)';
+ERROR: could not coerce FOR PORTION OF target from int4range to daterange
+LINE 2: FOR PORTION OF valid_at (int4range(1, 4))
+ ^
+-- Deleting with a direct target of a non-rangetype fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (4)
+ WHERE id = '[1,2)';
+ERROR: could not coerce FOR PORTION OF target from integer to daterange
+LINE 2: FOR PORTION OF valid_at (4)
+ ^
+-- Deleting with a direct target of NULL fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (NULL)
+ WHERE id = '[1,2)';
+ERROR: FOR PORTION OF target was null
+LINE 2: FOR PORTION OF valid_at (NULL)
+ ^
+-- Deleting with a direct target of empty does nothing
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at ('empty')
+ WHERE id = '[1,2)';
+DELETE 0
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [1,2) | [2018-01-02,2018-02-03) | one
+ [1,2) | [2018-03-03,2018-03-10) | one
+ [1,2) | [2018-03-17,2018-04-04) | one
+(3 rows)
+
+-- DELETE with no WHERE clause
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2030-01-01' TO NULL;
+DELETE 2
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-02-03) | one
+ [1,2) | [2018-03-03,2018-03-10) | one
+ [1,2) | [2018-03-17,2018-04-04) | one
+ [3,4) | [2018-01-01,2018-06-01) | three
+ [5,6) | (,2018-01-01) | five
+ [5,6) | [2019-01-01,2030-01-01) | five
+ [6,7) | [2018-03-01,2030-01-01) | six
+ [7,8) | (,2017-01-01) | seven
+(8 rows)
+
+\set QUIET true
+-- UPDATE ... RETURNING returns only the updated values
+-- (not the inserted side values, which are added by a separate "statement"):
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15'
+ SET name = 'three^3'
+ WHERE id = '[3,4)'
+ RETURNING *;
+ id | valid_at | name
+-------+-------------------------+---------
+ [3,4) | [2018-02-01,2018-02-15) | three^3
+(1 row)
+
+-- UPDATE ... RETURNING supports NEW and OLD valid_at
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-10' TO '2018-02-20'
+ SET name = 'three^4'
+ WHERE id = '[3,4)'
+ RETURNING OLD.name, NEW.name, OLD.valid_at, NEW.valid_at;
+ name | name | valid_at | valid_at
+---------+---------+-------------------------+-------------------------
+ three^3 | three^4 | [2018-02-01,2018-02-15) | [2018-02-10,2018-02-15)
+ three | three^4 | [2018-02-15,2018-06-01) | [2018-02-15,2018-02-20)
+(2 rows)
+
+-- DELETE FOR PORTION OF with current_date
+-- (We take care not to make the expectation depend on the timestamp.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[99,100)', '[2000-01-01,)', 'foo');
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM current_date TO null
+ WHERE id = '[99,100)';
+SELECT name, lower(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date - 1;
+ name | lower
+------+------------
+ foo | 2000-01-01
+(1 row)
+
+SELECT name, upper(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date + 1;
+ name | upper
+------+-------
+(0 rows)
+
+-- DELETE FOR PORTION OF with clock_timestamp()
+-- fails because the function is volatile:
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM clock_timestamp()::date TO null
+ WHERE id = '[99,100)';
+ERROR: FOR PORTION OF bounds cannot contain volatile functions
+-- clean up:
+DELETE FROM for_portion_of_test WHERE id = '[99,100)';
+-- DELETE ... RETURNING returns the deleted values, regardless of bounds
+-- (not the inserted side values, which are added by a separate "statement"):
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-02' TO '2018-02-03'
+ WHERE id = '[3,4)'
+ RETURNING *;
+ id | valid_at | name
+-------+-------------------------+---------
+ [3,4) | [2018-02-01,2018-02-10) | three^3
+(1 row)
+
+-- DELETE FOR PORTION OF in a PL/pgSQL function
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[10,11)', '[2018-01-01,2020-01-01)', 'ten');
+CREATE FUNCTION fpo_delete(_id int4range, _target_from date, _target_til date)
+RETURNS void LANGUAGE plpgsql AS
+$$
+BEGIN
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM $2 TO $3
+ WHERE id = $1;
+END;
+$$;
+SELECT fpo_delete('[10,11)', '2015-01-01', '2019-01-01');
+ fpo_delete
+------------
+
+(1 row)
+
+SELECT * FROM for_portion_of_test WHERE id = '[10,11)';
+ id | valid_at | name
+---------+-------------------------+------
+ [10,11) | [2019-01-01,2020-01-01) | ten
+(1 row)
+
+DELETE FROM for_portion_of_test WHERE id IN ('[10,11)');
+-- DELETE FOR PORTION OF in a compiled SQL function
+CREATE FUNCTION fpo_delete()
+RETURNS text
+BEGIN ATOMIC
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ RETURNING name;
+END;
+\sf+ fpo_delete()
+ CREATE OR REPLACE FUNCTION public.fpo_delete()
+ RETURNS text
+ LANGUAGE sql
+1 BEGIN ATOMIC
+2 DELETE FROM for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+3 RETURNING for_portion_of_test.name;
+4 END
+CREATE OR REPLACE function fpo_delete()
+RETURNS text
+BEGIN ATOMIC
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01'))
+ RETURNING name;
+END;
+\sf+ fpo_delete()
+ CREATE OR REPLACE FUNCTION public.fpo_delete()
+ RETURNS text
+ LANGUAGE sql
+1 BEGIN ATOMIC
+2 DELETE FROM for_portion_of_test FOR PORTION OF valid_at ((daterange('2018-01-15'::date, '2020-01-01'::date) * daterange('2019-01-01'::date, '2022-01-01'::date)))
+3 RETURNING for_portion_of_test.name;
+4 END
+DROP FUNCTION fpo_delete();
+-- test domains and CHECK constraints
+-- With a domain on a rangetype
+CREATE DOMAIN daterange_d AS daterange CHECK (upper(VALUE) <> '2005-05-05'::date);
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at daterange_d,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '[2000-01-01,2020-01-01)', 'one'),
+ (2, '[2000-01-01,2020-01-01)', 'two');
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '[2000-01-01,2005-05-05)', 'nope');
+ERROR: value for domain daterange_d violates check constraint "daterange_d_check"
+-- UPDATE works:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05'
+ SET name = 'one^1'
+ WHERE id = 1;
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)')
+ SET name = 'one^2'
+ WHERE id = 1;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+ id | valid_at | name
+----+-------------------------+-------
+ 1 | [2000-01-01,2010-01-01) | one
+ 1 | [2010-01-01,2010-01-05) | one^1
+ 1 | [2010-01-05,2010-01-07) | one
+ 1 | [2010-01-07,2010-01-09) | one^2
+ 1 | [2010-01-09,2020-01-01) | one
+(5 rows)
+
+-- The target is allowed to violate the domain:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05'
+ SET name = 'miss'
+ WHERE id = -1;
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)')
+ SET name = 'miss'
+ WHERE id = -1;
+-- test the updated row violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05'
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: value for domain daterange_d violates check constraint "daterange_d_check"
+-- test inserts violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01'
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: value for domain daterange_d violates check constraint "daterange_d_check"
+-- test updated row violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-11'
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check"
+DETAIL: Failing row contains (1, [2000-01-01,2001-01-11), one^3).
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02'
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check"
+DETAIL: Failing row contains (1, [2002-02-02,2010-01-01), one).
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+ id | valid_at | name
+----+-------------------------+-------
+ 1 | [2000-01-01,2010-01-01) | one
+ 1 | [2010-01-01,2010-01-05) | one^1
+ 1 | [2010-01-05,2010-01-07) | one
+ 1 | [2010-01-07,2010-01-09) | one^2
+ 1 | [2010-01-09,2020-01-01) | one
+(5 rows)
+
+-- DELETE works:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05'
+ WHERE id = 2;
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)')
+ WHERE id = 2;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+ id | valid_at | name
+----+-------------------------+------
+ 2 | [2000-01-01,2010-01-01) | two
+ 2 | [2010-01-05,2010-01-07) | two
+ 2 | [2010-01-09,2020-01-01) | two
+(3 rows)
+
+-- The target is allowed to violate the domain:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05'
+ WHERE id = -1;
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)')
+ WHERE id = -1;
+-- test inserts violating the domain
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01'
+ WHERE id = 2;
+ERROR: value for domain daterange_d violates check constraint "daterange_d_check"
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02');
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02'
+ WHERE id = 2;
+ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check"
+DETAIL: Failing row contains (2, [2002-02-02,2010-01-01), two).
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+ id | valid_at | name
+----+-------------------------+------
+ 2 | [2000-01-01,2010-01-01) | two
+ 2 | [2010-01-05,2010-01-07) | two
+ 2 | [2010-01-09,2020-01-01) | two
+(3 rows)
+
+DROP TABLE for_portion_of_test2;
+-- With a domain on a multirangetype
+CREATE FUNCTION multirange_lowers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$
+ SELECT array_agg(lower(r)) FROM UNNEST(mr) u(r);
+$$;
+CREATE FUNCTION multirange_uppers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$
+ SELECT array_agg(upper(r)) FROM UNNEST(mr) u(r);
+$$;
+CREATE DOMAIN datemultirange_d AS datemultirange CHECK (NOT '2005-05-05'::date = ANY (multirange_uppers(VALUE)));
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at datemultirange_d,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '{[2000-01-01,2020-01-01)}', 'one'),
+ (2, '{[2000-01-01,2020-01-01)}', 'two');
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '{[2000-01-01,2005-05-05)}', 'nope');
+ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check"
+-- UPDATE works:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}')
+ SET name = 'one^2'
+ WHERE id = 1;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+ id | valid_at | name
+----+---------------------------------------------------+-------
+ 1 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | one
+ 1 | {[2010-01-07,2010-01-09)} | one^2
+(2 rows)
+
+-- The target is allowed to violate the domain:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}')
+ SET name = 'miss'
+ WHERE id = -1;
+-- test the updated row violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check"
+-- test inserts violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check"
+-- test updated row violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2000-01-01,2001-01-11)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check"
+DETAIL: Failing row contains (1, {[2000-01-01,2001-01-11)}, one^3).
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at)));
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check"
+DETAIL: Failing row contains (1, {[2000-01-01,2001-01-01),[2002-02-02,2010-01-07),[2010-01-09,202..., one).
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+ id | valid_at | name
+----+---------------------------------------------------+-------
+ 1 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | one
+ 1 | {[2010-01-07,2010-01-09)} | one^2
+(2 rows)
+
+-- DELETE works:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}')
+ WHERE id = 2;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+ id | valid_at | name
+----+---------------------------------------------------+------
+ 2 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | two
+(1 row)
+
+-- The target is allowed to violate the domain:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}')
+ WHERE id = -1;
+-- test inserts violating the domain
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}')
+ WHERE id = 2;
+ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check"
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at)));
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}')
+ WHERE id = 2;
+ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check"
+DETAIL: Failing row contains (2, {[2000-01-01,2001-01-01),[2002-02-02,2010-01-07),[2010-01-09,202..., two).
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+ id | valid_at | name
+----+---------------------------------------------------+------
+ 2 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | two
+(1 row)
+
+DROP TABLE for_portion_of_test2;
+-- test on non-range/multirange columns
+-- With a direct target and a scalar column
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at date,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES (1, '2020-01-01', 'one');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('2010-01-01')
+ SET name = 'one^1';
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type
+LINE 2: FOR PORTION OF valid_at ('2010-01-01')
+ ^
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('2010-01-01');
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type
+LINE 2: FOR PORTION OF valid_at ('2010-01-01');
+ ^
+DROP TABLE for_portion_of_test2;
+-- With a direct target and a non-{,multi}range gistable column without overlaps
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at point,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES (1, '0,0', 'one');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1')
+ SET name = 'one^1';
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type
+LINE 2: FOR PORTION OF valid_at ('1,1')
+ ^
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1');
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type
+LINE 2: FOR PORTION OF valid_at ('1,1');
+ ^
+DROP TABLE for_portion_of_test2;
+-- With a direct target and a non-{,multi}range column with overlaps
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at box,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES (1, '0,0,4,4', 'one');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1,2,2')
+ SET name = 'one^1';
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type
+LINE 2: FOR PORTION OF valid_at ('1,1,2,2')
+ ^
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1,2,2');
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type
+LINE 2: FOR PORTION OF valid_at ('1,1,2,2');
+ ^
+DROP TABLE for_portion_of_test2;
+-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows
+CREATE FUNCTION dump_trigger()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ RAISE NOTICE '%: % % %:',
+ TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
+
+ IF TG_ARGV[0] THEN
+ RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table);
+ ELSE
+ RAISE NOTICE ' old: %', OLD.valid_at;
+ END IF;
+ IF TG_ARGV[1] THEN
+ RAISE NOTICE ' new: %', (SELECT string_agg(new_table::text, '\n ') FROM new_table);
+ ELSE
+ RAISE NOTICE ' new: %', NEW.valid_at;
+ END IF;
+
+ IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
+ RETURN NEW;
+ ELSIF TG_OP = 'DELETE' THEN
+ RETURN OLD;
+ END IF;
+END;
+$$;
+-- statement triggers:
+CREATE TRIGGER fpo_before_stmt
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_insert_stmt
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_update_stmt
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_delete_stmt
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+-- row triggers:
+CREATE TRIGGER fpo_before_row
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_insert_row
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01'
+ SET name = 'five^3'
+ WHERE id = '[5,6)';
+NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE UPDATE ROW:
+NOTICE: old: [2019-01-01,2030-01-01)
+NOTICE: new: [2021-01-01,2022-01-01)
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2019-01-01,2021-01-01)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2019-01-01,2021-01-01)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2022-01-01,2030-01-01)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2022-01-01,2030-01-01)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_after_update_row: AFTER UPDATE ROW:
+NOTICE: old: [2019-01-01,2030-01-01)
+NOTICE: new: [2021-01-01,2022-01-01)
+NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01'
+ WHERE id = '[5,6)';
+NOTICE: fpo_before_stmt: BEFORE DELETE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE DELETE ROW:
+NOTICE: old: [2022-01-01,2030-01-01)
+NOTICE: new: <NULL>
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2022-01-01,2023-01-01)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2022-01-01,2023-01-01)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2024-01-01,2030-01-01)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2024-01-01,2030-01-01)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_after_delete_row: AFTER DELETE ROW:
+NOTICE: old: [2022-01-01,2030-01-01)
+NOTICE: new: <NULL>
+NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+---------
+ [1,2) | [2018-01-02,2018-02-03) | one
+ [1,2) | [2018-03-03,2018-03-10) | one
+ [1,2) | [2018-03-17,2018-04-04) | one
+ [3,4) | [2018-01-01,2018-02-01) | three
+ [3,4) | [2018-02-01,2018-02-02) | three^3
+ [3,4) | [2018-02-03,2018-02-10) | three^3
+ [3,4) | [2018-02-10,2018-02-15) | three^4
+ [3,4) | [2018-02-15,2018-02-20) | three^4
+ [3,4) | [2018-02-20,2018-06-01) | three
+ [5,6) | (,2018-01-01) | five
+ [5,6) | [2019-01-01,2021-01-01) | five
+ [5,6) | [2021-01-01,2022-01-01) | five^3
+ [5,6) | [2022-01-01,2023-01-01) | five
+ [5,6) | [2024-01-01,2030-01-01) | five
+ [6,7) | [2018-03-01,2030-01-01) | six
+ [7,8) | (,2017-01-01) | seven
+(16 rows)
+
+-- Triggers with a custom transition table name:
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one');
+-- statement triggers:
+CREATE TRIGGER fpo_before_stmt
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_insert_stmt
+ AFTER INSERT ON for_portion_of_test
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, true);
+CREATE TRIGGER fpo_after_update_stmt
+ AFTER UPDATE ON for_portion_of_test
+ REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, true);
+CREATE TRIGGER fpo_after_delete_stmt
+ AFTER DELETE ON for_portion_of_test
+ REFERENCING OLD TABLE AS old_table
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, false);
+-- row triggers:
+CREATE TRIGGER fpo_before_row
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE TRIGGER fpo_after_insert_row
+ AFTER INSERT ON for_portion_of_test
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, true);
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, true);
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ REFERENCING OLD TABLE AS old_table
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, false);
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = '2018-01-15_to_2019-01-01';
+NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE UPDATE ROW:
+NOTICE: old: [2018-01-01,2020-01-01)
+NOTICE: new: [2018-01-15,2019-01-01)
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2018-01-01,2018-01-15)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one)
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2019-01-01,2020-01-01)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one)
+NOTICE: fpo_after_update_row: AFTER UPDATE ROW:
+NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE: new: ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01)
+NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT:
+NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE: new: ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01)
+ROLLBACK;
+BEGIN;
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-21';
+NOTICE: fpo_before_stmt: BEFORE DELETE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE DELETE ROW:
+NOTICE: old: [2018-01-01,2020-01-01)
+NOTICE: new: <NULL>
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2018-01-21,2020-01-01)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one)
+NOTICE: fpo_after_delete_row: AFTER DELETE ROW:
+NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE: new: <NULL>
+NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT:
+NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE: new: <NULL>
+ROLLBACK;
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-02'
+ SET name = 'NULL_to_2018-01-01';
+NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE UPDATE ROW:
+NOTICE: old: [2018-01-01,2020-01-01)
+NOTICE: new: [2018-01-01,2018-01-02)
+NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: <NULL>
+NOTICE: fpo_before_row: BEFORE INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2018-01-02,2020-01-01)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one)
+NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT:
+NOTICE: old: <NULL>
+NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one)
+NOTICE: fpo_after_update_row: AFTER UPDATE ROW:
+NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01)
+NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT:
+NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01)
+ROLLBACK;
+-- Deferred triggers
+-- (must be CONSTRAINT triggers thus AFTER ROW with no transition tables)
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-01,2020-01-01)', 'one');
+CREATE CONSTRAINT TRIGGER fpo_after_insert_row
+ AFTER INSERT ON for_portion_of_test
+ DEFERRABLE INITIALLY DEFERRED
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE CONSTRAINT TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ DEFERRABLE INITIALLY DEFERRED
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+CREATE CONSTRAINT TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ DEFERRABLE INITIALLY DEFERRED
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = '2018-01-15_to_2019-01-01';
+COMMIT;
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2018-01-01,2018-01-15)
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2019-01-01,2020-01-01)
+NOTICE: fpo_after_update_row: AFTER UPDATE ROW:
+NOTICE: old: [2018-01-01,2020-01-01)
+NOTICE: new: [2018-01-15,2019-01-01)
+BEGIN;
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-21';
+COMMIT;
+NOTICE: fpo_after_insert_row: AFTER INSERT ROW:
+NOTICE: old: <NULL>
+NOTICE: new: [2018-01-21,2019-01-01)
+NOTICE: fpo_after_delete_row: AFTER DELETE ROW:
+NOTICE: old: [2018-01-15,2019-01-01)
+NOTICE: new: <NULL>
+NOTICE: fpo_after_delete_row: AFTER DELETE ROW:
+NOTICE: old: [2018-01-01,2018-01-15)
+NOTICE: new: <NULL>
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-02'
+ SET name = 'NULL_to_2018-01-01';
+COMMIT;
+SELECT * FROM for_portion_of_test;
+ id | valid_at | name
+-------+-------------------------+--------------------------
+ [1,2) | [2019-01-01,2020-01-01) | one
+ [1,2) | [2018-01-21,2019-01-01) | 2018-01-15_to_2019-01-01
+(2 rows)
+
+-- test FOR PORTION OF from triggers during FOR PORTION OF:
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-01,2020-01-01)', 'one'),
+ ('[2,3)', '[2018-01-01,2020-01-01)', 'two'),
+ ('[3,4)', '[2018-01-01,2020-01-01)', 'three'),
+ ('[4,5)', '[2018-01-01,2020-01-01)', 'four');
+CREATE FUNCTION trg_fpo_update()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ IF pg_trigger_depth() = 1 THEN
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-03-01'
+ SET name = CONCAT(name, '^')
+ WHERE id = OLD.id;
+ END IF;
+ RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END;
+END;
+$$;
+CREATE FUNCTION trg_fpo_delete()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ IF pg_trigger_depth() = 1 THEN
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-03-01' TO '2018-04-01'
+ WHERE id = OLD.id;
+ END IF;
+ RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END;
+END;
+$$;
+-- UPDATE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update();
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ SET name = CONCAT(name, '*')
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [1,2) | [2018-01-01,2018-02-01) | one
+ [1,2) | [2018-02-01,2018-03-01) | one^
+ [1,2) | [2018-03-01,2018-05-01) | one
+ [1,2) | [2018-05-01,2018-06-01) | one*
+ [1,2) | [2018-06-01,2020-01-01) | one
+(5 rows)
+
+DROP TRIGGER fpo_after_update_row ON for_portion_of_test;
+-- UPDATE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update();
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [2,3) | [2018-01-01,2018-02-01) | two
+ [2,3) | [2018-02-01,2018-03-01) | two^
+ [2,3) | [2018-03-01,2018-05-01) | two
+ [2,3) | [2018-06-01,2020-01-01) | two
+(4 rows)
+
+DROP TRIGGER fpo_after_delete_row ON for_portion_of_test;
+-- DELETE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete();
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ SET name = CONCAT(name, '*')
+ WHERE id = '[3,4)';
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+--------
+ [3,4) | [2018-01-01,2018-03-01) | three
+ [3,4) | [2018-04-01,2018-05-01) | three
+ [3,4) | [2018-05-01,2018-06-01) | three*
+ [3,4) | [2018-06-01,2020-01-01) | three
+(4 rows)
+
+DROP TRIGGER fpo_after_update_row ON for_portion_of_test;
+-- DELETE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete();
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ WHERE id = '[4,5)';
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [4,5) | [2018-01-01,2018-03-01) | four
+ [4,5) | [2018-04-01,2018-05-01) | four
+ [4,5) | [2018-06-01,2020-01-01) | four
+(3 rows)
+
+DROP TRIGGER fpo_after_delete_row ON for_portion_of_test;
+-- Test with multiranges
+CREATE TABLE for_portion_of_test2 (
+ id int4range NOT NULL,
+ valid_at datemultirange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES
+ ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'),
+ ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'),
+ ('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'),
+ ('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three');
+ ;
+-- Updating with FROM/TO
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range type
+LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01'
+ ^
+-- Updating with multirange
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-10', '2018-02-10'), daterange('2018-03-05', '2018-05-01')))
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------+-------
+ [1,2) | {[2018-01-02,2018-01-10),[2018-02-10,2018-03-03)} | one
+ [1,2) | {[2018-01-10,2018-02-03),[2018-02-04,2018-02-10)} | one^1
+ [1,2) | {[2018-03-03,2018-03-05)} | one
+ [1,2) | {[2018-03-05,2018-04-04)} | one^1
+(4 rows)
+
+-- Updating with string coercion
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2018-03-05,2018-03-10)}')
+ SET name = 'one^2'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------+-------
+ [1,2) | {[2018-01-02,2018-01-10),[2018-02-10,2018-03-03)} | one
+ [1,2) | {[2018-01-10,2018-02-03),[2018-02-04,2018-02-10)} | one^1
+ [1,2) | {[2018-03-03,2018-03-05)} | one
+ [1,2) | {[2018-03-05,2018-03-10)} | one^2
+ [1,2) | {[2018-03-10,2018-04-04)} | one^1
+(5 rows)
+
+-- Updating with the wrong range subtype fails
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1,4)}'::int4multirange)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+ERROR: could not coerce FOR PORTION OF target from int4multirange to datemultirange
+LINE 2: FOR PORTION OF valid_at ('{[1,4)}'::int4multirange)
+ ^
+-- Updating with a non-multirangetype fails
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at (4)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+ERROR: could not coerce FOR PORTION OF target from integer to datemultirange
+LINE 2: FOR PORTION OF valid_at (4)
+ ^
+-- Updating with NULL fails
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at (NULL)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+ERROR: FOR PORTION OF target was null
+LINE 2: FOR PORTION OF valid_at (NULL)
+ ^
+-- Updating with empty does nothing
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{}')
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------+-------
+ [1,2) | {[2018-01-02,2018-01-10),[2018-02-10,2018-03-03)} | one
+ [1,2) | {[2018-01-10,2018-02-03),[2018-02-04,2018-02-10)} | one^1
+ [1,2) | {[2018-03-03,2018-03-05)} | one
+ [1,2) | {[2018-03-05,2018-03-10)} | one^2
+ [1,2) | {[2018-03-10,2018-04-04)} | one^1
+(5 rows)
+
+-- Deleting with FROM/TO
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range type
+LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01'
+ ^
+-- Deleting with multirange
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-15', '2018-02-15'), daterange('2018-03-01', '2018-03-15')))
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[2,3)' ORDER BY valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------------------------------+------
+ [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-15,2018-05-01)} | two
+(1 row)
+
+-- Deleting with string coercion
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2018-03-05,2018-03-20)}')
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[2,3)' ORDER BY valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------------------------------+------
+ [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-20,2018-05-01)} | two
+(1 row)
+
+-- Deleting with the wrong range subtype fails
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1,4)}'::int4multirange)
+ WHERE id = '[2,3)';
+ERROR: could not coerce FOR PORTION OF target from int4multirange to datemultirange
+LINE 2: FOR PORTION OF valid_at ('{[1,4)}'::int4multirange)
+ ^
+-- Deleting with a non-multirangetype fails
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at (4)
+ WHERE id = '[2,3)';
+ERROR: could not coerce FOR PORTION OF target from integer to datemultirange
+LINE 2: FOR PORTION OF valid_at (4)
+ ^
+-- Deleting with NULL fails
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at (NULL)
+ WHERE id = '[2,3)';
+ERROR: FOR PORTION OF target was null
+LINE 2: FOR PORTION OF valid_at (NULL)
+ ^
+-- Deleting with empty does nothing
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{}')
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+ id | valid_at | name
+-------+---------------------------------------------------------------------------+-------
+ [1,2) | {[2018-01-02,2018-01-10),[2018-02-10,2018-03-03)} | one
+ [1,2) | {[2018-01-10,2018-02-03),[2018-02-04,2018-02-10)} | one^1
+ [1,2) | {[2018-03-03,2018-03-05)} | one
+ [1,2) | {[2018-03-05,2018-03-10)} | one^2
+ [1,2) | {[2018-03-10,2018-04-04)} | one^1
+ [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-20,2018-05-01)} | two
+ [3,4) | {[2018-01-01,)} | three
+(7 rows)
+
+DROP TABLE for_portion_of_test2;
+-- Test with a custom range type
+CREATE TYPE mydaterange AS range(subtype=date);
+CREATE TABLE for_portion_of_test2 (
+ id int4range NOT NULL,
+ valid_at mydaterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+ ('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+ ('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+ ('[2,3)', '[2018-01-01,2018-05-01)', 'two'),
+ ('[3,4)', '[2018-01-01,)', 'three');
+ ;
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15'
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2018-01-02,2018-01-10) | one
+ [1,2) | [2018-01-10,2018-02-03) | one^1
+ [1,2) | [2018-02-03,2018-02-10) | one^1
+ [1,2) | [2018-02-10,2018-03-03) | one
+ [1,2) | [2018-03-03,2018-04-04) | one
+ [2,3) | [2018-01-01,2018-01-15) | two
+ [2,3) | [2018-02-15,2018-05-01) | two
+ [3,4) | [2018-01-01,) | three
+(8 rows)
+
+DROP TABLE for_portion_of_test2;
+DROP TYPE mydaterange;
+-- Test FOR PORTION OF against a partitioned table.
+-- temporal_partitioned_1 has the same attnums as the root
+-- temporal_partitioned_3 has the different attnums from the root
+-- temporal_partitioned_5 has the different attnums too, but reversed
+CREATE TABLE temporal_partitioned (
+ id int4range,
+ valid_at daterange,
+ name text,
+ CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)');
+CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)');
+CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)');
+ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3;
+ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL;
+ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)');
+ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5;
+ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL;
+ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)');
+INSERT INTO temporal_partitioned (id, valid_at, name) VALUES
+ ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'),
+ ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'),
+ ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five');
+SELECT * FROM temporal_partitioned;
+ id | valid_at | name
+-------+-------------------------+-------
+ [1,2) | [2000-01-01,2010-01-01) | one
+ [3,4) | [2000-01-01,2010-01-01) | three
+ [5,6) | [2000-01-01,2010-01-01) | five
+(3 rows)
+
+-- Update without moving within partition 1
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+-- Update without moving within partition 3
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01'
+ SET name = 'three^1'
+ WHERE id = '[3,4)';
+-- Update without moving within partition 5
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01'
+ SET name = 'five^1'
+ WHERE id = '[5,6)';
+-- Move from partition 1 to partition 3
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01'
+ SET name = 'one^2',
+ id = '[4,5)'
+ WHERE id = '[1,2)';
+-- Move from partition 3 to partition 1
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01'
+ SET name = 'three^2',
+ id = '[2,3)'
+ WHERE id = '[3,4)';
+-- Move from partition 5 to partition 3
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01'
+ SET name = 'five^2',
+ id = '[3,4)'
+ WHERE id = '[5,6)';
+-- Update all partitions at once (each with leftovers)
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+---------
+ [1,2) | [2000-01-01,2000-03-01) | one
+ [1,2) | [2000-03-01,2000-04-01) | one^1
+ [1,2) | [2000-04-01,2000-06-01) | one
+ [1,2) | [2000-07-01,2010-01-01) | one
+ [2,3) | [2000-06-01,2000-07-01) | three^2
+ [3,4) | [2000-01-01,2000-03-01) | three
+ [3,4) | [2000-03-01,2000-04-01) | three^1
+ [3,4) | [2000-04-01,2000-06-01) | three
+ [3,4) | [2000-06-01,2000-07-01) | five^2
+ [3,4) | [2000-07-01,2010-01-01) | three
+ [4,5) | [2000-06-01,2000-07-01) | one^2
+ [5,6) | [2000-01-01,2000-03-01) | five
+ [5,6) | [2000-03-01,2000-04-01) | five^1
+ [5,6) | [2000-04-01,2000-06-01) | five
+ [5,6) | [2000-07-01,2010-01-01) | five
+(15 rows)
+
+SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at;
+ id | valid_at | name
+-------+-------------------------+---------
+ [1,2) | [2000-01-01,2000-03-01) | one
+ [1,2) | [2000-03-01,2000-04-01) | one^1
+ [1,2) | [2000-04-01,2000-06-01) | one
+ [1,2) | [2000-07-01,2010-01-01) | one
+ [2,3) | [2000-06-01,2000-07-01) | three^2
+(5 rows)
+
+SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at;
+ name | id | valid_at
+---------+-------+-------------------------
+ three | [3,4) | [2000-01-01,2000-03-01)
+ three^1 | [3,4) | [2000-03-01,2000-04-01)
+ three | [3,4) | [2000-04-01,2000-06-01)
+ five^2 | [3,4) | [2000-06-01,2000-07-01)
+ three | [3,4) | [2000-07-01,2010-01-01)
+ one^2 | [4,5) | [2000-06-01,2000-07-01)
+(6 rows)
+
+SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
+ name | valid_at | id
+--------+-------------------------+-------
+ five | [2000-01-01,2000-03-01) | [5,6)
+ five^1 | [2000-03-01,2000-04-01) | [5,6)
+ five | [2000-04-01,2000-06-01) | [5,6)
+ five | [2000-07-01,2010-01-01) | [5,6)
+(4 rows)
+
+DROP TABLE temporal_partitioned;
+RESET datestyle;
DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc).
SET SESSION AUTHORIZATION regress_priv_user1;
DROP TABLE errtst;
+-- test column-level privileges on the range used in FOR PORTION OF
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE t1 (
+ c1 int4range,
+ valid_at tsrange,
+ CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS)
+);
+-- UPDATE requires select permission on the valid_at column (but not update):
+GRANT SELECT (c1) ON t1 TO regress_priv_user2;
+GRANT UPDATE (c1) ON t1 TO regress_priv_user2;
+GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user3;
+GRANT UPDATE (c1) ON t1 TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user2;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+ERROR: permission denied for table t1
+SET SESSION AUTHORIZATION regress_priv_user3;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+SET SESSION AUTHORIZATION regress_priv_user1;
+-- DELETE requires select permission on the valid_at column:
+GRANT DELETE ON t1 TO regress_priv_user2;
+GRANT DELETE ON t1 TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user2;
+DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01';
+ERROR: permission denied for table t1
+SET SESSION AUTHORIZATION regress_priv_user3;
+DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01';
+SET SESSION AUTHORIZATION regress_priv_user1;
+DROP TABLE t1;
-- test column-level privileges when involved with DELETE
SET SESSION AUTHORIZATION regress_priv_user1;
ALTER TABLE atest6 ADD COLUMN three integer;
drop view uv_iocu_view;
drop table uv_iocu_tab;
+-- Check UPDATE FOR PORTION OF works correctly
+create table uv_fpo_tab (id int4range, valid_at tsrange, b float,
+ constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps));
+insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0);
+create view uv_fpo_view as
+ select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab;
+insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1);
+select * from uv_fpo_view order by id, valid_at;
+ b | c | valid_at | id | two
+---+---+---------------------------------------------------------+-------+-----
+ 1 | 2 | ["Fri Jan 01 00:00:00 2010","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0
+ 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0
+(2 rows)
+
+update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]';
+select * from uv_fpo_view order by id, valid_at;
+ b | c | valid_at | id | two
+---+---+---------------------------------------------------------+-------+-----
+ 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0
+ 2 | 3 | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0
+ 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0
+(3 rows)
+
+delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]';
+select * from uv_fpo_view order by id, valid_at;
+ b | c | valid_at | id | two
+---+---+---------------------------------------------------------+-------+-----
+ 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0
+ 2 | 3 | ["Thu Jan 01 00:00:00 2015","Sun Jan 01 00:00:00 2017") | [1,2) | 2.0
+ 0 | 1 | ["Sat Jan 01 00:00:00 2022","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0
+(3 rows)
+
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
--
-- We leave behind several tables to test pg_dump etc:
-- temporal_rng, temporal_rng2,
--- temporal_fk_rng2rng.
+-- temporal_fk_rng2rng, temporal_fk2_rng2rng.
SET datestyle TO ISO, YMD;
--
-- test input parser
('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'),
('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar')
;
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+ SET name = name || '1';
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+ SET name = name || '2'
+ WHERE id = '[2,3)';
+SELECT * FROM temporal3 ORDER BY id, valid_at;
+ id | valid_at | id2 | name
+-------+-------------------------+--------+-------
+ [1,2) | [2000-01-01,2000-05-01) | [7,8) | foo
+ [1,2) | [2000-05-01,2000-07-01) | [7,8) | foo1
+ [1,2) | [2000-07-01,2010-01-01) | [7,8) | foo
+ [2,3) | [2000-01-01,2000-04-01) | [9,10) | bar
+ [2,3) | [2000-04-01,2000-05-01) | [9,10) | bar2
+ [2,3) | [2000-05-01,2000-06-01) | [9,10) | bar12
+ [2,3) | [2000-06-01,2000-07-01) | [9,10) | bar1
+ [2,3) | [2000-07-01,2010-01-01) | [9,10) | bar
+(8 rows)
+
+-- conflicting id only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3');
+ERROR: conflicting key value violates exclusion constraint "temporal3_pk"
+DETAIL: Key (id, valid_at)=([1,2), [2005-01-01,2006-01-01)) conflicts with existing key (id, valid_at)=([1,2), [2000-07-01,2010-01-01)).
+-- conflicting id2 only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3');
+ERROR: conflicting key value violates exclusion constraint "temporal3_uniq"
+DETAIL: Key (id2, valid_at)=([9,10), [2005-01-01,2010-01-01)) conflicts with existing key (id2, valid_at)=([9,10), [2000-07-01,2010-01-01)).
DROP TABLE temporal3;
--
-- test changing the PK's dependencies
('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'),
('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'),
('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three');
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+-------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
- [3,4) | [2000-01-01,2010-01-01) | three
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
+ tableoid | id | valid_at | name
+----------+-------+-------------------------+-------
+ tp1 | [1,2) | [2000-01-01,2000-02-01) | one
+ tp1 | [1,2) | [2000-02-01,2000-03-01) | one
+ tp2 | [3,4) | [2000-01-01,2010-01-01) | three
(3 rows)
-SELECT * FROM tp1 ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
-(2 rows)
-
-SELECT * FROM tp2 ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+-------
- [3,4) | [2000-01-01,2010-01-01) | three
-(1 row)
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,2)';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,5)'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,3)'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,4)';
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
+ tableoid | id | valid_at | name
+----------+-------+-------------------------+-------
+ tp1 | [1,2) | [2000-01-01,2000-01-15) | one
+ tp1 | [1,2) | [2000-01-15,2000-02-01) | one2
+ tp1 | [1,2) | [2000-02-01,2000-02-15) | one2
+ tp1 | [1,2) | [2000-02-15,2000-02-20) | one
+ tp1 | [1,2) | [2000-02-25,2000-03-01) | one
+ tp1 | [2,3) | [2002-01-01,2003-01-01) | three
+ tp2 | [3,4) | [2000-01-01,2000-01-15) | three
+ tp2 | [3,4) | [2000-02-15,2002-01-01) | three
+ tp2 | [3,4) | [2003-01-01,2010-01-01) | three
+ tp2 | [4,5) | [2000-02-20,2000-02-25) | one
+(10 rows)
DROP TABLE temporal_partitioned;
-- temporal UNIQUE:
('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'),
('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'),
('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three');
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+-------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
- [3,4) | [2000-01-01,2010-01-01) | three
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
+ tableoid | id | valid_at | name
+----------+-------+-------------------------+-------
+ tp1 | [1,2) | [2000-01-01,2000-02-01) | one
+ tp1 | [1,2) | [2000-02-01,2000-03-01) | one
+ tp2 | [3,4) | [2000-01-01,2010-01-01) | three
(3 rows)
-SELECT * FROM tp1 ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
-(2 rows)
-
-SELECT * FROM tp2 ORDER BY id, valid_at;
- id | valid_at | name
--------+-------------------------+-------
- [3,4) | [2000-01-01,2010-01-01) | three
-(1 row)
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,2)';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,5)'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,3)'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,4)';
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
+ tableoid | id | valid_at | name
+----------+-------+-------------------------+-------
+ tp1 | [1,2) | [2000-01-01,2000-01-15) | one
+ tp1 | [1,2) | [2000-01-15,2000-02-01) | one2
+ tp1 | [1,2) | [2000-02-01,2000-02-15) | one2
+ tp1 | [1,2) | [2000-02-15,2000-02-20) | one
+ tp1 | [1,2) | [2000-02-25,2000-03-01) | one
+ tp1 | [2,3) | [2002-01-01,2003-01-01) | three
+ tp2 | [3,4) | [2000-01-01,2000-01-15) | three
+ tp2 | [3,4) | [2000-02-15,2002-01-01) | three
+ tp2 | [3,4) | [2003-01-01,2010-01-01) | three
+ tp2 | [4,5) | [2000-02-20,2000-02-25) | one
+(10 rows)
DROP TABLE temporal_partitioned;
-- ALTER TABLE REPLICA IDENTITY
WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+-- changing an unreferenced part is okay:
+UPDATE temporal_rng
+ FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_rng
+ FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+ id | valid_at
+-------+-------------------------
+ [5,6) | [2016-02-01,2016-03-01)
+ [5,6) | [2018-01-01,2018-01-02)
+ [5,6) | [2018-01-03,2018-02-01)
+ [7,8) | [2018-01-02,2018-01-03)
+(4 rows)
+
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [3,4) | [2018-01-05,2018-01-10) | [5,6)
+(1 row)
+
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
COMMIT;
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+ id | valid_at
+-------+-------------------------
+ [5,6) | [2018-01-01,2018-01-02)
+ [5,6) | [2018-01-03,2018-02-01)
+(2 rows)
+
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [3,4) | [2018-01-05,2018-01-10) | [5,6)
+(1 row)
+
+-- deleting just a part fails:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+WHERE id = '[5,6)';
+ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+ id | valid_at
+-------+-------------------------
+ [5,6) | [2018-01-01,2018-01-02)
+ [5,6) | [2018-01-03,2018-02-01)
+(2 rows)
+
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [3,4) | [2018-01-05,2018-01-10) | [5,6)
+(1 row)
+
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
ON DELETE RESTRICT;
ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD
--
--- test ON UPDATE/DELETE options
+-- rng2rng test ON UPDATE/DELETE options
--
-- test FK referenced updates CASCADE
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
ON DELETE CASCADE ON UPDATE CASCADE;
ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
-- test FK referenced updates SET NULL
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)');
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
ON DELETE SET NULL ON UPDATE SET NULL;
ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
-- test FK referenced updates SET DEFAULT
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null));
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)');
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
ADD CONSTRAINT temporal_fk_rng2rng_fk
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- changing an unreferenced part is okay:
+UPDATE temporal_mltrng
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_mltrng
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- then delete the objecting FK record and the same PK update succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+UPDATE temporal_mltrng SET id = '[7,8)'
+ WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
--
-- test FK referenced updates RESTRICT
--
COMMIT;
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+WHERE id = '[5,6)';
+-- deleting just a part fails:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+WHERE id = '[5,6)';
+ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- then delete the objecting FK record and the same PK delete succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
--
-- FK between partitioned tables: ranges
--
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse create_property_graph
+test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse create_property_graph for_portion_of
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
--- /dev/null
+-- Tests for UPDATE/DELETE FOR PORTION OF
+
+SET datestyle TO ISO, YMD;
+
+-- Works on non-PK columns
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2020-01-01)', 'one');
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = 'one^1';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+-- With a table alias with AS
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t
+ SET name = 'one^2';
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t;
+
+-- With a table alias without AS
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t
+ SET name = 'one^3';
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t;
+
+-- UPDATE with FROM
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02'
+ SET name = 'one^4'
+ FROM (SELECT '[1,2)'::int4range) AS t2(id)
+ WHERE for_portion_of_test.id = t2.id;
+
+-- DELETE with USING
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03'
+ USING (SELECT '[1,2)'::int4range) AS t2(id)
+ WHERE for_portion_of_test.id = t2.id;
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+-- Works on more than one range
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid1_at daterange,
+ valid2_at daterange,
+ name text NOT NULL
+);
+INSERT INTO for_portion_of_test (id, valid1_at, valid2_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one');
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL
+ SET name = 'foo';
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL
+ SET name = 'bar';
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL;
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL;
+SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at;
+
+-- Test with NULLs in the scalar/range key columns.
+-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint
+-- but FOR PORTION OF shouldn't require that.
+DROP TABLE for_portion_of_test;
+CREATE UNLOGGED TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', NULL, '1 null'),
+ ('[1,2)', '(,)', '1 unbounded'),
+ ('[1,2)', 'empty', '1 empty'),
+ (NULL, NULL, NULL),
+ (NULL, daterange('2018-01-01', '2019-01-01'), 'null key');
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ SET name = 'NULL to NULL';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+DROP TABLE for_portion_of_test;
+
+--
+-- UPDATE tests
+--
+
+CREATE TABLE for_portion_of_test (
+ id int4range NOT NULL,
+ valid_at daterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+ ('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+ ('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+ ('[2,3)', '[2018-01-01,2018-01-05)', 'two'),
+ ('[3,4)', '[2018-01-01,)', 'three'),
+ ('[4,5)', '(,2018-04-01)', 'four'),
+ ('[5,6)', '(,)', 'five')
+ ;
+\set QUIET false
+
+-- Updating with a missing column fails
+UPDATE for_portion_of_test
+ FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+ SET name = 'foo'
+ WHERE id = '[5,6)';
+
+-- Updating the range fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO NULL
+ SET valid_at = '[1990-01-01,1999-01-01)'
+ WHERE id = '[5,6)';
+
+-- The wrong start type fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM 1 TO '2020-01-01'
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+
+-- The wrong end type fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2000-01-01' TO 4
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+
+-- Updating with timestamps reversed fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+ SET name = 'three^1'
+ WHERE id = '[3,4)';
+
+-- Updating with a subquery fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01'
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+
+-- Updating with a column fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM lower(valid_at) TO NULL
+ SET name = 'nope'
+ WHERE id = '[3,4)';
+
+-- Updating with timestamps equal does nothing
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+ SET name = 'three^0'
+ WHERE id = '[3,4)';
+
+-- Updating a finite/open portion with a finite/open target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO NULL
+ SET name = 'three^1'
+ WHERE id = '[3,4)';
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+
+-- Updating a finite/open portion with an open/finite target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-03-01'
+ SET name = 'three^2'
+ WHERE id = '[3,4)';
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+
+-- Updating an open/finite portion with an open/finite target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-02-01'
+ SET name = 'four^1'
+ WHERE id = '[4,5)';
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+
+-- Updating an open/finite portion with a finite/open target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO NULL
+ SET name = 'four^2'
+ WHERE id = '[4,5)';
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+
+-- Updating a finite/finite portion with an exact fit
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01'
+ SET name = 'four^3'
+ WHERE id = '[4,5)';
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+
+-- Updating an enclosed span
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ SET name = 'two^2'
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at;
+
+-- Updating an open/open portion with a finite/finite target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01'
+ SET name = 'five^1'
+ WHERE id = '[5,6)';
+SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at;
+
+-- Updating an enclosed span with separate protruding spans
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01'
+ SET name = 'five^2'
+ WHERE id = '[5,6)';
+SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at;
+
+-- Updating multiple enclosed spans
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ SET name = 'one^2'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- Updating with a direct target
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15'))
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- Updating with a direct target, coerced from a string
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)')
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- Updating with a direct target of the wrong range subtype fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (int4range(1, 4))
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+
+-- Updating with a direct target of a non-rangetype fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (4)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+
+-- Updating with a direct target of NULL fails
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (NULL)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+
+-- Updating with a direct target of empty does nothing
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at ('empty')
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- Updating the non-range part of the PK:
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-15' TO NULL
+ SET id = '[6,7)'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id IN ('[1,2)', '[6,7)') ORDER BY id, valid_at;
+
+-- UPDATE with no WHERE clause
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2030-01-01' TO NULL
+ SET name = name || '*';
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+\set QUIET true
+
+-- Updating with a shift/reduce conflict
+-- (requires a tsrange column)
+CREATE UNLOGGED TABLE for_portion_of_test2 (
+ id int4range,
+ valid_at tsrange,
+ name text
+);
+INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES
+ ('[1,2)', '[2000-01-01,2020-01-01)', 'one');
+-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes)
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at
+ FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE
+ TO '2012-01-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+
+-- TO is used for the bound but not the INTERVAL:
+-- syntax error
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at
+ FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR
+ TO '2014-01-01'
+ SET name = 'one^2'
+ WHERE id = '[1,2)';
+
+-- adding parens fixes it
+-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes)
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at
+ FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR)
+ TO '2016-01-01'
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+DROP TABLE for_portion_of_test2;
+
+-- UPDATE FOR PORTION OF in a CTE:
+-- The outer query sees the table how it was before the updates,
+-- and with no leftovers yet,
+-- but it also sees the new values via the RETURNING clause.
+-- (We test RETURNING more directly, without a CTE, below.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[10,11)', '[2018-01-01,2020-01-01)', 'ten');
+WITH update_apr AS (
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01'
+ SET name = 'Apr 2018'
+ WHERE id = '[10,11)'
+ RETURNING id, valid_at, name
+)
+SELECT *
+ FROM for_portion_of_test AS t, update_apr
+ WHERE t.id = update_apr.id;
+SELECT * FROM for_portion_of_test WHERE id = '[10,11)' ORDER BY id, valid_at;
+
+-- UPDATE FOR PORTION OF with current_date
+-- (We take care not to make the expectation depend on the timestamp.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[99,100)', '[2000-01-01,)', 'foo');
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM current_date TO null
+ SET name = 'bar'
+ WHERE id = '[99,100)';
+SELECT name, lower(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date - 1;
+SELECT name, upper(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date + 1;
+
+-- UPDATE FOR PORTION OF with clock_timestamp()
+-- fails because the function is volatile:
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM clock_timestamp()::date TO null
+ SET name = 'baz'
+ WHERE id = '[99,100)';
+
+-- clean up:
+DELETE FROM for_portion_of_test WHERE id = '[99,100)';
+
+-- Not visible to UPDATE:
+-- Tuples updated/inserted within the CTE are not visible to the main query yet,
+-- but neither are old tuples the CTE changed:
+-- (This is the same behavior as without FOR PORTION OF.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[11,12)', '[2018-01-01,2020-01-01)', 'eleven');
+WITH update_apr AS (
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01'
+ SET name = 'Apr 2018'
+ WHERE id = '[11,12)'
+ RETURNING id, valid_at, name
+)
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ AS t
+ SET name = 'May 2018'
+ FROM update_apr AS j
+ WHERE t.id = j.id;
+SELECT * FROM for_portion_of_test WHERE id = '[11,12)' ORDER BY id, valid_at;
+DELETE FROM for_portion_of_test WHERE id IN ('[10,11)', '[11,12)');
+
+-- UPDATE FOR PORTION OF in a PL/pgSQL function
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[10,11)', '[2018-01-01,2020-01-01)', 'ten');
+CREATE FUNCTION fpo_update(_id int4range, _target_from date, _target_til date)
+RETURNS void LANGUAGE plpgsql AS
+$$
+BEGIN
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM $2 TO $3
+ SET name = concat(_target_from::text, ' to ', _target_til::text)
+ WHERE id = $1;
+END;
+$$;
+SELECT fpo_update('[10,11)', '2015-01-01', '2019-01-01');
+SELECT * FROM for_portion_of_test WHERE id = '[10,11)';
+
+-- UPDATE FOR PORTION OF in a compiled SQL function
+CREATE FUNCTION fpo_update()
+RETURNS text
+BEGIN ATOMIC
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = 'one^1'
+ RETURNING name;
+END;
+\sf+ fpo_update()
+CREATE OR REPLACE function fpo_update()
+RETURNS text
+BEGIN ATOMIC
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01'))
+ SET name = 'one^1'
+ RETURNING name;
+END;
+\sf+ fpo_update()
+DROP FUNCTION fpo_update();
+
+DROP TABLE for_portion_of_test;
+
+--
+-- DELETE tests
+--
+
+CREATE TABLE for_portion_of_test (
+ id int4range NOT NULL,
+ valid_at daterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+ ('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+ ('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+ ('[2,3)', '[2018-01-01,2018-01-05)', 'two'),
+ ('[3,4)', '[2018-01-01,)', 'three'),
+ ('[4,5)', '(,2018-04-01)', 'four'),
+ ('[5,6)', '(,)', 'five'),
+ ('[6,7)', '[2018-01-01,)', 'six'),
+ ('[7,8)', '(,2018-04-01)', 'seven'),
+ ('[8,9)', '[2018-01-02,2018-02-03)', 'eight'),
+ ('[8,9)', '[2018-02-03,2018-03-03)', 'eight'),
+ ('[8,9)', '[2018-03-03,2018-04-04)', 'eight')
+ ;
+\set QUIET false
+
+-- Deleting with a missing column fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL
+ WHERE id = '[5,6)';
+
+-- The wrong start type fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM 1 TO '2020-01-01'
+ WHERE id = '[3,4)';
+
+-- The wrong end type fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2000-01-01' TO 4
+ WHERE id = '[3,4)';
+
+-- Deleting with timestamps reversed fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01'
+ WHERE id = '[3,4)';
+
+-- Deleting with a subquery fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01'
+ WHERE id = '[3,4)';
+
+-- Deleting with a column fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM lower(valid_at) TO NULL
+ WHERE id = '[3,4)';
+
+-- Deleting with timestamps equal does nothing
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01'
+ WHERE id = '[3,4)';
+
+-- Deleting a finite/open portion with a finite/open target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-06-01' TO NULL
+ WHERE id = '[3,4)';
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+
+-- Deleting a finite/open portion with an open/finite target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-03-01'
+ WHERE id = '[6,7)';
+SELECT * FROM for_portion_of_test WHERE id = '[6,7)' ORDER BY id, valid_at;
+
+-- Deleting an open/finite portion with an open/finite target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-02-01'
+ WHERE id = '[4,5)';
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+
+-- Deleting an open/finite portion with a finite/open target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2017-01-01' TO NULL
+ WHERE id = '[7,8)';
+SELECT * FROM for_portion_of_test WHERE id = '[7,8)' ORDER BY id, valid_at;
+
+-- Deleting a finite/finite portion with an exact fit
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-04-01'
+ WHERE id = '[4,5)';
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+
+-- Deleting an enclosed span
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at;
+
+-- Deleting an open/open portion with a finite/finite target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01'
+ WHERE id = '[5,6)';
+SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at;
+
+-- Deleting an enclosed span with separate protruding spans
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-03' TO '2018-03-03'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- Deleting multiple enclosed spans
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO NULL
+ WHERE id = '[8,9)';
+SELECT * FROM for_portion_of_test WHERE id = '[8,9)' ORDER BY id, valid_at;
+
+-- Deleting with a direct target
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15'))
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- Deleting with a direct target, coerced from a string
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)')
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- Deleting with a direct target of the wrong range subtype fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (int4range(1, 4))
+ WHERE id = '[1,2)';
+
+-- Deleting with a direct target of a non-rangetype fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (4)
+ WHERE id = '[1,2)';
+
+-- Deleting with a direct target of NULL fails
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (NULL)
+ WHERE id = '[1,2)';
+
+-- Deleting with a direct target of empty does nothing
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at ('empty')
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+-- DELETE with no WHERE clause
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2030-01-01' TO NULL;
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+\set QUIET true
+
+-- UPDATE ... RETURNING returns only the updated values
+-- (not the inserted side values, which are added by a separate "statement"):
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15'
+ SET name = 'three^3'
+ WHERE id = '[3,4)'
+ RETURNING *;
+
+-- UPDATE ... RETURNING supports NEW and OLD valid_at
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-10' TO '2018-02-20'
+ SET name = 'three^4'
+ WHERE id = '[3,4)'
+ RETURNING OLD.name, NEW.name, OLD.valid_at, NEW.valid_at;
+
+-- DELETE FOR PORTION OF with current_date
+-- (We take care not to make the expectation depend on the timestamp.)
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[99,100)', '[2000-01-01,)', 'foo');
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM current_date TO null
+ WHERE id = '[99,100)';
+SELECT name, lower(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date - 1;
+SELECT name, upper(valid_at) FROM for_portion_of_test
+ WHERE id = '[99,100)' AND valid_at @> current_date + 1;
+
+-- DELETE FOR PORTION OF with clock_timestamp()
+-- fails because the function is volatile:
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM clock_timestamp()::date TO null
+ WHERE id = '[99,100)';
+
+-- clean up:
+DELETE FROM for_portion_of_test WHERE id = '[99,100)';
+
+-- DELETE ... RETURNING returns the deleted values, regardless of bounds
+-- (not the inserted side values, which are added by a separate "statement"):
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-02' TO '2018-02-03'
+ WHERE id = '[3,4)'
+ RETURNING *;
+
+-- DELETE FOR PORTION OF in a PL/pgSQL function
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[10,11)', '[2018-01-01,2020-01-01)', 'ten');
+CREATE FUNCTION fpo_delete(_id int4range, _target_from date, _target_til date)
+RETURNS void LANGUAGE plpgsql AS
+$$
+BEGIN
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM $2 TO $3
+ WHERE id = $1;
+END;
+$$;
+SELECT fpo_delete('[10,11)', '2015-01-01', '2019-01-01');
+SELECT * FROM for_portion_of_test WHERE id = '[10,11)';
+DELETE FROM for_portion_of_test WHERE id IN ('[10,11)');
+
+-- DELETE FOR PORTION OF in a compiled SQL function
+CREATE FUNCTION fpo_delete()
+RETURNS text
+BEGIN ATOMIC
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ RETURNING name;
+END;
+\sf+ fpo_delete()
+CREATE OR REPLACE function fpo_delete()
+RETURNS text
+BEGIN ATOMIC
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01'))
+ RETURNING name;
+END;
+\sf+ fpo_delete()
+DROP FUNCTION fpo_delete();
+
+
+-- test domains and CHECK constraints
+
+-- With a domain on a rangetype
+CREATE DOMAIN daterange_d AS daterange CHECK (upper(VALUE) <> '2005-05-05'::date);
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at daterange_d,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '[2000-01-01,2020-01-01)', 'one'),
+ (2, '[2000-01-01,2020-01-01)', 'two');
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '[2000-01-01,2005-05-05)', 'nope');
+-- UPDATE works:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05'
+ SET name = 'one^1'
+ WHERE id = 1;
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)')
+ SET name = 'one^2'
+ WHERE id = 1;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+-- The target is allowed to violate the domain:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05'
+ SET name = 'miss'
+ WHERE id = -1;
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)')
+ SET name = 'miss'
+ WHERE id = -1;
+-- test the updated row violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05'
+ SET name = 'one^3'
+ WHERE id = 1;
+-- test inserts violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01'
+ SET name = 'one^3'
+ WHERE id = 1;
+-- test updated row violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-11'
+ SET name = 'one^3'
+ WHERE id = 1;
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02'
+ SET name = 'one^3'
+ WHERE id = 1;
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+-- DELETE works:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05'
+ WHERE id = 2;
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)')
+ WHERE id = 2;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+-- The target is allowed to violate the domain:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05'
+ WHERE id = -1;
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)')
+ WHERE id = -1;
+-- test inserts violating the domain
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01'
+ WHERE id = 2;
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02');
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02'
+ WHERE id = 2;
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+DROP TABLE for_portion_of_test2;
+
+-- With a domain on a multirangetype
+CREATE FUNCTION multirange_lowers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$
+ SELECT array_agg(lower(r)) FROM UNNEST(mr) u(r);
+$$;
+CREATE FUNCTION multirange_uppers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$
+ SELECT array_agg(upper(r)) FROM UNNEST(mr) u(r);
+$$;
+CREATE DOMAIN datemultirange_d AS datemultirange CHECK (NOT '2005-05-05'::date = ANY (multirange_uppers(VALUE)));
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at datemultirange_d,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '{[2000-01-01,2020-01-01)}', 'one'),
+ (2, '{[2000-01-01,2020-01-01)}', 'two');
+INSERT INTO for_portion_of_test2 VALUES
+ (1, '{[2000-01-01,2005-05-05)}', 'nope');
+-- UPDATE works:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}')
+ SET name = 'one^2'
+ WHERE id = 1;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+-- The target is allowed to violate the domain:
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}')
+ SET name = 'miss'
+ WHERE id = -1;
+-- test the updated row violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+-- test inserts violating the domain
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+-- test updated row violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2000-01-01,2001-01-11)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at)));
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}')
+ SET name = 'one^3'
+ WHERE id = 1;
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at;
+-- DELETE works:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}')
+ WHERE id = 2;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+-- The target is allowed to violate the domain:
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}')
+ WHERE id = -1;
+-- test inserts violating the domain
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}')
+ WHERE id = 2;
+-- test inserts violating CHECK constraints
+ALTER TABLE for_portion_of_test2
+ ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at)));
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}')
+ WHERE id = 2;
+ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check;
+SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at;
+DROP TABLE for_portion_of_test2;
+
+-- test on non-range/multirange columns
+
+-- With a direct target and a scalar column
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at date,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES (1, '2020-01-01', 'one');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('2010-01-01')
+ SET name = 'one^1';
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('2010-01-01');
+DROP TABLE for_portion_of_test2;
+
+-- With a direct target and a non-{,multi}range gistable column without overlaps
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at point,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES (1, '0,0', 'one');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1')
+ SET name = 'one^1';
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1');
+DROP TABLE for_portion_of_test2;
+
+-- With a direct target and a non-{,multi}range column with overlaps
+CREATE TABLE for_portion_of_test2 (
+ id integer,
+ valid_at box,
+ name text
+);
+INSERT INTO for_portion_of_test2 VALUES (1, '0,0,4,4', 'one');
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1,2,2')
+ SET name = 'one^1';
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('1,1,2,2');
+DROP TABLE for_portion_of_test2;
+
+-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows
+
+CREATE FUNCTION dump_trigger()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ RAISE NOTICE '%: % % %:',
+ TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
+
+ IF TG_ARGV[0] THEN
+ RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table);
+ ELSE
+ RAISE NOTICE ' old: %', OLD.valid_at;
+ END IF;
+ IF TG_ARGV[1] THEN
+ RAISE NOTICE ' new: %', (SELECT string_agg(new_table::text, '\n ') FROM new_table);
+ ELSE
+ RAISE NOTICE ' new: %', NEW.valid_at;
+ END IF;
+
+ IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
+ RETURN NEW;
+ ELSIF TG_OP = 'DELETE' THEN
+ RETURN OLD;
+ END IF;
+END;
+$$;
+
+-- statement triggers:
+
+CREATE TRIGGER fpo_before_stmt
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_insert_stmt
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_update_stmt
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_delete_stmt
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
+-- row triggers:
+
+CREATE TRIGGER fpo_before_row
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_insert_row
+ AFTER INSERT ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01'
+ SET name = 'five^3'
+ WHERE id = '[5,6)';
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01'
+ WHERE id = '[5,6)';
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+-- Triggers with a custom transition table name:
+
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one');
+
+-- statement triggers:
+
+CREATE TRIGGER fpo_before_stmt
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_insert_stmt
+ AFTER INSERT ON for_portion_of_test
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, true);
+
+CREATE TRIGGER fpo_after_update_stmt
+ AFTER UPDATE ON for_portion_of_test
+ REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, true);
+
+CREATE TRIGGER fpo_after_delete_stmt
+ AFTER DELETE ON for_portion_of_test
+ REFERENCING OLD TABLE AS old_table
+ FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, false);
+
+-- row triggers:
+
+CREATE TRIGGER fpo_before_row
+ BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE TRIGGER fpo_after_insert_row
+ AFTER INSERT ON for_portion_of_test
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, true);
+
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, true);
+
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ REFERENCING OLD TABLE AS old_table
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, false);
+
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = '2018-01-15_to_2019-01-01';
+ROLLBACK;
+
+BEGIN;
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-21';
+ROLLBACK;
+
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-02'
+ SET name = 'NULL_to_2018-01-01';
+ROLLBACK;
+
+-- Deferred triggers
+-- (must be CONSTRAINT triggers thus AFTER ROW with no transition tables)
+
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-01,2020-01-01)', 'one');
+
+CREATE CONSTRAINT TRIGGER fpo_after_insert_row
+ AFTER INSERT ON for_portion_of_test
+ DEFERRABLE INITIALLY DEFERRED
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE CONSTRAINT TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ DEFERRABLE INITIALLY DEFERRED
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+CREATE CONSTRAINT TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ DEFERRABLE INITIALLY DEFERRED
+ FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false);
+
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+ SET name = '2018-01-15_to_2019-01-01';
+COMMIT;
+
+BEGIN;
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-21';
+COMMIT;
+
+BEGIN;
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM NULL TO '2018-01-02'
+ SET name = 'NULL_to_2018-01-01';
+COMMIT;
+
+SELECT * FROM for_portion_of_test;
+
+-- test FOR PORTION OF from triggers during FOR PORTION OF:
+
+DROP TABLE for_portion_of_test;
+CREATE TABLE for_portion_of_test (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-01,2020-01-01)', 'one'),
+ ('[2,3)', '[2018-01-01,2020-01-01)', 'two'),
+ ('[3,4)', '[2018-01-01,2020-01-01)', 'three'),
+ ('[4,5)', '[2018-01-01,2020-01-01)', 'four');
+
+CREATE FUNCTION trg_fpo_update()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ IF pg_trigger_depth() = 1 THEN
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-03-01'
+ SET name = CONCAT(name, '^')
+ WHERE id = OLD.id;
+ END IF;
+ RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END;
+END;
+$$;
+
+CREATE FUNCTION trg_fpo_delete()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ IF pg_trigger_depth() = 1 THEN
+ DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-03-01' TO '2018-04-01'
+ WHERE id = OLD.id;
+ END IF;
+ RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END;
+END;
+$$;
+
+-- UPDATE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF
+
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update();
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ SET name = CONCAT(name, '*')
+ WHERE id = '[1,2)';
+
+SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at;
+
+DROP TRIGGER fpo_after_update_row ON for_portion_of_test;
+
+-- UPDATE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF
+
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update();
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ WHERE id = '[2,3)';
+
+SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at;
+
+DROP TRIGGER fpo_after_delete_row ON for_portion_of_test;
+
+-- DELETE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF
+
+CREATE TRIGGER fpo_after_update_row
+ AFTER UPDATE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete();
+
+UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ SET name = CONCAT(name, '*')
+ WHERE id = '[3,4)';
+
+SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at;
+
+DROP TRIGGER fpo_after_update_row ON for_portion_of_test;
+
+-- DELETE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF
+
+CREATE TRIGGER fpo_after_delete_row
+ AFTER DELETE ON for_portion_of_test
+ FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete();
+
+DELETE FROM for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+ WHERE id = '[4,5)';
+
+SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at;
+
+DROP TRIGGER fpo_after_delete_row ON for_portion_of_test;
+
+-- Test with multiranges
+
+CREATE TABLE for_portion_of_test2 (
+ id int4range NOT NULL,
+ valid_at datemultirange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES
+ ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'),
+ ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'),
+ ('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'),
+ ('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three');
+ ;
+
+-- Updating with FROM/TO
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+-- Updating with multirange
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-10', '2018-02-10'), daterange('2018-03-05', '2018-05-01')))
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at;
+-- Updating with string coercion
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2018-03-05,2018-03-10)}')
+ SET name = 'one^2'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at;
+-- Updating with the wrong range subtype fails
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1,4)}'::int4multirange)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+-- Updating with a non-multirangetype fails
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at (4)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+-- Updating with NULL fails
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at (NULL)
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+-- Updating with empty does nothing
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at ('{}')
+ SET name = 'one^3'
+ WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at;
+
+-- Deleting with FROM/TO
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+-- Deleting with multirange
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-15', '2018-02-15'), daterange('2018-03-01', '2018-03-15')))
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[2,3)' ORDER BY valid_at;
+-- Deleting with string coercion
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[2018-03-05,2018-03-20)}')
+ WHERE id = '[2,3)';
+SELECT * FROM for_portion_of_test2 WHERE id = '[2,3)' ORDER BY valid_at;
+-- Deleting with the wrong range subtype fails
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{[1,4)}'::int4multirange)
+ WHERE id = '[2,3)';
+-- Deleting with a non-multirangetype fails
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at (4)
+ WHERE id = '[2,3)';
+-- Deleting with NULL fails
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at (NULL)
+ WHERE id = '[2,3)';
+-- Deleting with empty does nothing
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at ('{}')
+ WHERE id = '[2,3)';
+
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+
+DROP TABLE for_portion_of_test2;
+
+-- Test with a custom range type
+
+CREATE TYPE mydaterange AS range(subtype=date);
+
+CREATE TABLE for_portion_of_test2 (
+ id int4range NOT NULL,
+ valid_at mydaterange NOT NULL,
+ name text NOT NULL,
+ CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES
+ ('[1,2)', '[2018-01-02,2018-02-03)', 'one'),
+ ('[1,2)', '[2018-02-03,2018-03-03)', 'one'),
+ ('[1,2)', '[2018-03-03,2018-04-04)', 'one'),
+ ('[2,3)', '[2018-01-01,2018-05-01)', 'two'),
+ ('[3,4)', '[2018-01-01,)', 'three');
+ ;
+
+UPDATE for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+
+DELETE FROM for_portion_of_test2
+ FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15'
+ WHERE id = '[2,3)';
+
+SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at;
+
+DROP TABLE for_portion_of_test2;
+DROP TYPE mydaterange;
+
+-- Test FOR PORTION OF against a partitioned table.
+-- temporal_partitioned_1 has the same attnums as the root
+-- temporal_partitioned_3 has the different attnums from the root
+-- temporal_partitioned_5 has the different attnums too, but reversed
+
+CREATE TABLE temporal_partitioned (
+ id int4range,
+ valid_at daterange,
+ name text,
+ CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)');
+CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)');
+CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)');
+
+ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3;
+ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL;
+ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)');
+
+ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5;
+ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at;
+ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL;
+ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)');
+
+INSERT INTO temporal_partitioned (id, valid_at, name) VALUES
+ ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'),
+ ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'),
+ ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five');
+
+SELECT * FROM temporal_partitioned;
+
+-- Update without moving within partition 1
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01'
+ SET name = 'one^1'
+ WHERE id = '[1,2)';
+
+-- Update without moving within partition 3
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01'
+ SET name = 'three^1'
+ WHERE id = '[3,4)';
+
+-- Update without moving within partition 5
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01'
+ SET name = 'five^1'
+ WHERE id = '[5,6)';
+
+-- Move from partition 1 to partition 3
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01'
+ SET name = 'one^2',
+ id = '[4,5)'
+ WHERE id = '[1,2)';
+
+-- Move from partition 3 to partition 1
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01'
+ SET name = 'three^2',
+ id = '[2,3)'
+ WHERE id = '[3,4)';
+
+-- Move from partition 5 to partition 3
+UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01'
+ SET name = 'five^2',
+ id = '[3,4)'
+ WHERE id = '[5,6)';
+
+-- Update all partitions at once (each with leftovers)
+
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at;
+SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at;
+SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
+
+DROP TABLE temporal_partitioned;
+
+RESET datestyle;
SET SESSION AUTHORIZATION regress_priv_user1;
DROP TABLE errtst;
+-- test column-level privileges on the range used in FOR PORTION OF
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE t1 (
+ c1 int4range,
+ valid_at tsrange,
+ CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS)
+);
+-- UPDATE requires select permission on the valid_at column (but not update):
+GRANT SELECT (c1) ON t1 TO regress_priv_user2;
+GRANT UPDATE (c1) ON t1 TO regress_priv_user2;
+GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user3;
+GRANT UPDATE (c1) ON t1 TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user2;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+SET SESSION AUTHORIZATION regress_priv_user3;
+UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
+SET SESSION AUTHORIZATION regress_priv_user1;
+-- DELETE requires select permission on the valid_at column:
+GRANT DELETE ON t1 TO regress_priv_user2;
+GRANT DELETE ON t1 TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user2;
+DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01';
+SET SESSION AUTHORIZATION regress_priv_user3;
+DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01';
+SET SESSION AUTHORIZATION regress_priv_user1;
+DROP TABLE t1;
+
-- test column-level privileges when involved with DELETE
SET SESSION AUTHORIZATION regress_priv_user1;
ALTER TABLE atest6 ADD COLUMN three integer;
drop view uv_iocu_view;
drop table uv_iocu_tab;
+-- Check UPDATE FOR PORTION OF works correctly
+create table uv_fpo_tab (id int4range, valid_at tsrange, b float,
+ constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps));
+insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0);
+create view uv_fpo_view as
+ select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab;
+
+insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1);
+select * from uv_fpo_view order by id, valid_at;
+update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]';
+select * from uv_fpo_view order by id, valid_at;
+delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]';
+select * from uv_fpo_view order by id, valid_at;
+
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
--
-- We leave behind several tables to test pg_dump etc:
-- temporal_rng, temporal_rng2,
--- temporal_fk_rng2rng.
+-- temporal_fk_rng2rng, temporal_fk2_rng2rng.
SET datestyle TO ISO, YMD;
('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'),
('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar')
;
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
+ SET name = name || '1';
+UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
+ SET name = name || '2'
+ WHERE id = '[2,3)';
+SELECT * FROM temporal3 ORDER BY id, valid_at;
+-- conflicting id only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3');
+-- conflicting id2 only:
+INSERT INTO temporal3 (id, valid_at, id2, name)
+ VALUES
+ ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3');
DROP TABLE temporal3;
--
('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'),
('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'),
('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three');
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
-SELECT * FROM tp1 ORDER BY id, valid_at;
-SELECT * FROM tp2 ORDER BY id, valid_at;
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,2)';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,5)'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,3)'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,4)';
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
-- temporal UNIQUE:
('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'),
('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'),
('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three');
-SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
-SELECT * FROM tp1 ORDER BY id, valid_at;
-SELECT * FROM tp2 ORDER BY id, valid_at;
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ SET name = 'one2'
+ WHERE id = '[1,2)';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
+ SET id = '[4,5)'
+ WHERE name = 'one';
+UPDATE temporal_partitioned
+ FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
+ SET id = '[2,3)'
+ WHERE name = 'three';
+DELETE FROM temporal_partitioned
+ FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
+ WHERE id = '[3,4)';
+SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
-- ALTER TABLE REPLICA IDENTITY
-- changing the scalar part fails:
UPDATE temporal_rng SET id = '[7,8)'
WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
+-- changing an unreferenced part is okay:
+UPDATE temporal_rng
+ FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_rng
+ FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
COMMIT;
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
+WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+-- deleting just a part fails:
+DELETE FROM temporal_rng
+FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
+WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
ON DELETE RESTRICT;
--
--- test ON UPDATE/DELETE options
+-- rng2rng test ON UPDATE/DELETE options
--
-- test FK referenced updates CASCADE
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
ON DELETE CASCADE ON UPDATE CASCADE;
-- test FK referenced updates SET NULL
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)');
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
ON DELETE SET NULL ON UPDATE SET NULL;
-- test FK referenced updates SET DEFAULT
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null));
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)');
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
ADD CONSTRAINT temporal_fk_rng2rng_fk
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
COMMIT;
-- changing the scalar part fails:
+UPDATE temporal_mltrng SET id = '[7,8)'
+ WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
+-- changing an unreferenced part is okay:
+UPDATE temporal_mltrng
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_mltrng
+ FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+ SET id = '[7,8)'
+ WHERE id = '[5,6)';
+-- then delete the objecting FK record and the same PK update succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
UPDATE temporal_mltrng SET id = '[7,8)'
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
COMMIT;
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+WHERE id = '[5,6)';
+-- deleting just a part fails:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+WHERE id = '[5,6)';
+-- then delete the objecting FK record and the same PK delete succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
--
-- FK between partitioned tables: ranges
qq(psql:<stdin>:1: ERROR: cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't UPDATE temporal_no_key DEFAULT");
+# No need to test again with FOR PORTION OF
($result, $stdout, $stderr) = $node_publisher->psql('postgres',
"DELETE FROM temporal_no_key WHERE id = '[3,4)'");
qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't DELETE temporal_no_key DEFAULT");
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't DELETE FOR PORTION OF temporal_no_key DEFAULT");
$node_publisher->wait_for_catchup('sub1');
$node_publisher->safe_psql('postgres',
"UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"
+);
$node_publisher->safe_psql('postgres',
"DELETE FROM temporal_pk WHERE id = '[3,4)'");
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
$node_publisher->wait_for_catchup('sub1');
$result = $node_subscriber->safe_psql('postgres',
"SELECT * FROM temporal_pk ORDER BY id, valid_at");
is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
-[2,3)|[2000-01-01,2010-01-01)|b
+[2,3)|[2000-01-01,2001-01-01)|b
+[2,3)|[2001-01-01,2002-01-01)|c
+[2,3)|[2003-01-01,2010-01-01)|b
[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk DEFAULT');
# replicate with a unique key:
qq(psql:<stdin>:1: ERROR: cannot update table "temporal_unique" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't UPDATE temporal_unique DEFAULT");
+# No need to test again with FOR PORTION OF
($result, $stdout, $stderr) = $node_publisher->psql('postgres',
"DELETE FROM temporal_unique WHERE id = '[3,4)'");
qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't DELETE temporal_unique DEFAULT");
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't DELETE FOR PORTION OF temporal_unique DEFAULT");
$node_publisher->wait_for_catchup('sub1');
$node_publisher->safe_psql('postgres',
"UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'");
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_no_key FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"
+);
$node_publisher->safe_psql('postgres',
"DELETE FROM temporal_no_key WHERE id = '[3,4)'");
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
$node_publisher->wait_for_catchup('sub1');
$result = $node_subscriber->safe_psql('postgres',
"SELECT * FROM temporal_no_key ORDER BY id, valid_at");
is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
-[2,3)|[2000-01-01,2010-01-01)|b
+[2,3)|[2000-01-01,2001-01-01)|b
+[2,3)|[2001-01-01,2002-01-01)|c
+[2,3)|[2003-01-01,2010-01-01)|b
[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key FULL');
# replicate with a primary key:
$node_publisher->safe_psql('postgres',
"UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"
+);
$node_publisher->safe_psql('postgres',
"DELETE FROM temporal_pk WHERE id = '[3,4)'");
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
$node_publisher->wait_for_catchup('sub1');
$result = $node_subscriber->safe_psql('postgres',
"SELECT * FROM temporal_pk ORDER BY id, valid_at");
is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
-[2,3)|[2000-01-01,2010-01-01)|b
+[2,3)|[2000-01-01,2001-01-01)|b
+[2,3)|[2001-01-01,2002-01-01)|c
+[2,3)|[2003-01-01,2010-01-01)|b
[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk FULL');
# replicate with a unique key:
$node_publisher->safe_psql('postgres',
"UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_unique FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"
+);
$node_publisher->safe_psql('postgres',
"DELETE FROM temporal_unique WHERE id = '[3,4)'");
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
$node_publisher->wait_for_catchup('sub1');
$result = $node_subscriber->safe_psql('postgres',
"SELECT * FROM temporal_unique ORDER BY id, valid_at");
is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
-[2,3)|[2000-01-01,2010-01-01)|b
+[2,3)|[2000-01-01,2001-01-01)|b
+[2,3)|[2001-01-01,2002-01-01)|c
+[2,3)|[2003-01-01,2010-01-01)|b
[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique FULL');
# cleanup
$node_publisher->safe_psql('postgres',
"UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"
+);
$node_publisher->safe_psql('postgres',
"DELETE FROM temporal_pk WHERE id = '[3,4)'");
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
$node_publisher->wait_for_catchup('sub1');
$result = $node_subscriber->safe_psql('postgres',
"SELECT * FROM temporal_pk ORDER BY id, valid_at");
is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
-[2,3)|[2000-01-01,2010-01-01)|b
+[2,3)|[2000-01-01,2001-01-01)|b
+[2,3)|[2001-01-01,2002-01-01)|c
+[2,3)|[2003-01-01,2010-01-01)|b
[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk USING INDEX');
# replicate with a unique key:
$node_publisher->safe_psql('postgres',
"UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_unique FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"
+);
$node_publisher->safe_psql('postgres',
"DELETE FROM temporal_unique WHERE id = '[3,4)'");
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
$node_publisher->wait_for_catchup('sub1');
$result = $node_subscriber->safe_psql('postgres',
"SELECT * FROM temporal_unique ORDER BY id, valid_at");
is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
-[2,3)|[2000-01-01,2010-01-01)|b
+[2,3)|[2000-01-01,2001-01-01)|b
+[2,3)|[2001-01-01,2002-01-01)|c
+[2,3)|[2003-01-01,2010-01-01)|b
[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique USING INDEX');
# cleanup
qq(psql:<stdin>:1: ERROR: cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't UPDATE temporal_no_key NOTHING");
+# No need to test again with FOR PORTION OF
($result, $stdout, $stderr) = $node_publisher->psql('postgres',
"DELETE FROM temporal_no_key WHERE id = '[3,4)'");
qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't DELETE temporal_no_key NOTHING");
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't DELETE temporal_no_key NOTHING");
$node_publisher->wait_for_catchup('sub1');
qq(psql:<stdin>:1: ERROR: cannot update table "temporal_pk" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't UPDATE temporal_pk NOTHING");
+# No need to test again with FOR PORTION OF
($result, $stdout, $stderr) = $node_publisher->psql('postgres',
"DELETE FROM temporal_pk WHERE id = '[3,4)'");
qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_pk" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't DELETE temporal_pk NOTHING");
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_pk" because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't DELETE temporal_pk NOTHING");
$node_publisher->wait_for_catchup('sub1');
qq(psql:<stdin>:1: ERROR: cannot update table "temporal_unique" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't UPDATE temporal_unique NOTHING");
+# No need to test again with FOR PORTION OF
($result, $stdout, $stderr) = $node_publisher->psql('postgres',
"DELETE FROM temporal_unique WHERE id = '[3,4)'");
qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
"can't DELETE temporal_unique NOTHING");
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"
+);
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't DELETE FOR PORTION OF temporal_unique NOTHING");
$node_publisher->wait_for_catchup('sub1');
ForEachState
ForFiveState
ForFourState
+ForPortionOfClause
+ForPortionOfExpr
+ForPortionOfState
ForThreeState
ForeignAsyncConfigureWait_function
ForeignAsyncNotify_function
projects / postgresql.git / commitdiff
