Dear Hackers, Here are patches adding new TG_* vars to PL/pgSQL, PL/Python, PL/Perl, and PL/Tcl, holding FOR PORTION OF details. These let triggers respond appropriately when FOR PORTION OF is used in the original query. That is useful for replication, auditing, and general functionality. We ourselves need this information for temporal foreign key actions (but in C, not in a PL)[0]. This also came up a few times in the v19 beta feedback to FOR PORTION OF. In particular, it would help to include FOR PORTION OF information even for INSERT triggers fired because of the temporal leftovers.
Patch 1 is the same as patch 2 from [0], but I'm repeating it here so that the main patches compile. The PL/pgSQL patch is fairly mature: I've been including it in valid-time submissions for at least a few years now. The other PL/* patches are new. They don't have to all be committed together, but I think we should make the behavior as consistent as possible across languages, so one commitfest entry seems best. For Tcl, it's hard for me to judge if I'm presenting the information in an idiomatic way. Any feedback would be appreciated! [0] https://www.postgresql.org/message-id/CA%2BrenyUts-ksRKL3V%2B9kTnRCAJdb8Xc3T1L%2ByCVscNZQKT4Yvw%40mail.gmail.com Yours, -- Paul ~{:-) [email protected]
From 6bbab97146a8b4e0f05cde346b44d1c20b81c783 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Tue, 28 Jul 2026 21:49:43 -0700 Subject: [PATCH v1 5/5] Expose FOR PORTION OF to pltcl triggers It is helpful for triggers to see what the FOR PORTION OF clause specified: both the column name and the targeted bounds. We already pass this information to plpgsql trigger functions; this commit does the same for pltcl, using the new $TG_period_name and $TG_period_bounds variables. These are also set when inserting temporal leftovers, so that triggers can distinguish regular inserts from those automatic ones. Since an INSERT can't use FOR PORTION OF itself, there is no ambiguity. Author: Paul A. Jungwirth <[email protected]> --- doc/src/sgml/pltcl.sgml | 31 +++++++++ src/pl/tcl/expected/pltcl_trigger.out | 90 +++++++++++++++++++++++++++ src/pl/tcl/pltcl.c | 28 ++++++++- src/pl/tcl/sql/pltcl_trigger.sql | 35 +++++++++++ 4 files changed, 183 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index 9fd008a99d7..62dd04d589e 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -678,6 +678,37 @@ SELECT 'doesn''t' AS ret </listitem> </varlistentry> + <varlistentry> + <term><varname>$TG_period_name</varname></term> + <listitem> + <para> + The column name used in a <literal>FOR PORTION OF</literal> clause, + or else the empty string. Also set for the implicit + <command>INSERT</command> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><varname>$TG_period_bounds</varname></term> + <listitem> + <para> + The range/multirange given as the bounds of a + <literal>FOR PORTION OF</literal> clause, either directly (with + parens syntax) or computed from the <literal>FROM</literal> and + <literal>TO</literal> bounds. The empty string if + <literal>FOR PORTION OF</literal> was not used. Also set for the + implicit <command>INSERT</command> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. This is a text value based on the type's + output function, since the type can't be known at function creation + time. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><varname>$NEW</varname></term> <listitem> diff --git a/src/pl/tcl/expected/pltcl_trigger.out b/src/pl/tcl/expected/pltcl_trigger.out index 5298e50a5ec..516e8f03340 100644 --- a/src/pl/tcl/expected/pltcl_trigger.out +++ b/src/pl/tcl/expected/pltcl_trigger.out @@ -625,6 +625,8 @@ NOTICE: OLD: {} NOTICE: TG_level: STATEMENT NOTICE: TG_name: statement_trigger NOTICE: TG_op: INSERT +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -636,6 +638,8 @@ NOTICE: OLD: {} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig NOTICE: TG_op: INSERT +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -648,6 +652,8 @@ NOTICE: OLD: {} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig_before NOTICE: TG_op: INSERT +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i j k} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_generated @@ -659,6 +665,8 @@ NOTICE: OLD: {} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig_after NOTICE: TG_op: INSERT +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i j k} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_generated @@ -671,6 +679,8 @@ NOTICE: OLD: {i: 1, j: 2} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig_before NOTICE: TG_op: UPDATE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i j k} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_generated @@ -682,6 +692,8 @@ NOTICE: OLD: {i: 1, j: 2} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig_after NOTICE: TG_op: UPDATE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i j k} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_generated @@ -694,6 +706,8 @@ NOTICE: OLD: {i: 11, j: 22} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig_before NOTICE: TG_op: DELETE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i j k} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_generated @@ -705,6 +719,8 @@ NOTICE: OLD: {i: 11, j: 22} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig_after NOTICE: TG_op: DELETE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i j k} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_generated @@ -717,6 +733,8 @@ NOTICE: OLD: {} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_view_trig NOTICE: TG_op: INSERT +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_view @@ -729,6 +747,8 @@ NOTICE: OLD: {i: 1, v: insert} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_view_trig NOTICE: TG_op: UPDATE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_view @@ -741,6 +761,8 @@ NOTICE: OLD: {i: 1, v: insert} NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_view_trig NOTICE: TG_op: DELETE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test_view @@ -753,6 +775,8 @@ NOTICE: OLD: {} NOTICE: TG_level: STATEMENT NOTICE: TG_name: statement_trigger NOTICE: TG_op: UPDATE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -766,6 +790,8 @@ NOTICE: OLD: {} NOTICE: TG_level: STATEMENT NOTICE: TG_name: statement_trigger NOTICE: TG_op: UPDATE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -777,6 +803,8 @@ NOTICE: OLD: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: in NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig NOTICE: TG_op: UPDATE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -789,6 +817,8 @@ NOTICE: OLD: {} NOTICE: TG_level: STATEMENT NOTICE: TG_name: statement_trigger NOTICE: TG_op: DELETE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -800,6 +830,8 @@ NOTICE: OLD: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: up NOTICE: TG_level: ROW NOTICE: TG_name: show_trigger_data_trig NOTICE: TG_op: DELETE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -812,6 +844,8 @@ NOTICE: OLD: {} NOTICE: TG_level: STATEMENT NOTICE: TG_name: statement_trigger NOTICE: TG_op: TRUNCATE +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -827,6 +861,8 @@ NOTICE: OLD: {} NOTICE: TG_level: STATEMENT NOTICE: TG_name: statement_trigger NOTICE: TG_op: INSERT +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -841,6 +877,8 @@ NOTICE: OLD: {} NOTICE: TG_level: STATEMENT NOTICE: TG_name: statement_trigger NOTICE: TG_op: INSERT +NOTICE: TG_period_bounds: {} +NOTICE: TG_period_name: {} NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} NOTICE: TG_relid: bogus:12345 NOTICE: TG_table_name: trigger_test @@ -887,3 +925,55 @@ SELECT * FROM trigger_test_generated; ---+---+--- (0 rows) +-- test FOR PORTION OF +CREATE TABLE temporal_trigger_test ( + id int, + valid_at daterange, + v text +); +INSERT INTO temporal_trigger_test VALUES (1, '[2018-01-01,2020-01-01)', 'one'); +CREATE FUNCTION for_portion_of_trigger_func() RETURNS trigger +LANGUAGE pltcl +AS $$ +elog NOTICE "$TG_when $TG_op: TG_period_name = '$TG_period_name', TG_period_bounds = '$TG_period_bounds'" +return OK +$$; +CREATE TRIGGER for_portion_of_trigger_trig + BEFORE INSERT OR UPDATE OR DELETE ON temporal_trigger_test + FOR EACH ROW EXECUTE PROCEDURE for_portion_of_trigger_func(); +UPDATE temporal_trigger_test + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET v = 'updated'; +NOTICE: BEFORE UPDATE: TG_period_name = 'valid_at', TG_period_bounds = '[01-01-2019,06-01-2019)' +NOTICE: BEFORE INSERT: TG_period_name = 'valid_at', TG_period_bounds = '[01-01-2019,06-01-2019)' +NOTICE: BEFORE INSERT: TG_period_name = 'valid_at', TG_period_bounds = '[01-01-2019,06-01-2019)' +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + id | valid_at | v +----+-------------------------+--------- + 1 | [01-01-2018,01-01-2019) | one + 1 | [01-01-2019,06-01-2019) | updated + 1 | [06-01-2019,01-01-2020) | one +(3 rows) + +DELETE FROM temporal_trigger_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-09-01'; +NOTICE: BEFORE DELETE: TG_period_name = 'valid_at', TG_period_bounds = '[06-01-2018,09-01-2018)' +NOTICE: BEFORE INSERT: TG_period_name = 'valid_at', TG_period_bounds = '[06-01-2018,09-01-2018)' +NOTICE: BEFORE INSERT: TG_period_name = 'valid_at', TG_period_bounds = '[06-01-2018,09-01-2018)' +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + id | valid_at | v +----+-------------------------+--------- + 1 | [01-01-2018,06-01-2018) | one + 1 | [09-01-2018,01-01-2019) | one + 1 | [01-01-2019,06-01-2019) | updated + 1 | [06-01-2019,01-01-2020) | one +(4 rows) + +-- no FOR PORTION OF, so the variables are empty +UPDATE temporal_trigger_test SET v = 'all of it'; +NOTICE: BEFORE UPDATE: TG_period_name = '', TG_period_bounds = '' +NOTICE: BEFORE UPDATE: TG_period_name = '', TG_period_bounds = '' +NOTICE: BEFORE UPDATE: TG_period_name = '', TG_period_bounds = '' +NOTICE: BEFORE UPDATE: TG_period_name = '', TG_period_bounds = '' +DROP TABLE temporal_trigger_test; +DROP FUNCTION for_portion_of_trigger_func(); diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c index e2c6d99a6de..aa6b2fcf32b 100644 --- a/src/pl/tcl/pltcl.c +++ b/src/pl/tcl/pltcl.c @@ -1249,6 +1249,32 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state, else elog(ERROR, "unrecognized LEVEL tg_event: %u", trigdata->tg_event); + /* + * The FOR PORTION OF details for TG_period_name and + * TG_period_bounds. The bounds could be any range or multirange + * type, so the best we can offer is their text representation. + */ + if (trigdata->tg_temporal) + { + ForPortionOfState *fpo = trigdata->tg_temporal; + Oid funcid; + bool varlena; + + Tcl_ListObjAppendElement(NULL, tcl_cmd, + Tcl_NewStringObj(utf_e2u(fpo->fp_rangeName), -1)); + + getTypeOutputInfo(fpo->fp_rangeType, &funcid, &varlena); + stroid = OidOutputFunctionCall(funcid, fpo->fp_targetRange); + Tcl_ListObjAppendElement(NULL, tcl_cmd, + Tcl_NewStringObj(utf_e2u(stroid), -1)); + pfree(stroid); + } + else + { + Tcl_ListObjAppendElement(NULL, tcl_cmd, Tcl_NewObj()); + Tcl_ListObjAppendElement(NULL, tcl_cmd, Tcl_NewObj()); + } + /* Finally append the arguments from CREATE TRIGGER */ for (i = 0; i < trigdata->tg_trigger->tgnargs; i++) Tcl_ListObjAppendElement(NULL, tcl_cmd, @@ -1703,7 +1729,7 @@ compile_pltcl_function(Oid fn_oid, Oid tgreloid, { /* trigger procedure has fixed args */ Tcl_DStringAppend(&proc_internal_args, - "TG_name TG_relid TG_table_name TG_table_schema TG_relatts TG_when TG_level TG_op __PLTcl_Tup_NEW __PLTcl_Tup_OLD args", + "TG_name TG_relid TG_table_name TG_table_schema TG_relatts TG_when TG_level TG_op __PLTcl_Tup_NEW __PLTcl_Tup_OLD TG_period_name TG_period_bounds args", -1); } else if (is_event_trigger) diff --git a/src/pl/tcl/sql/pltcl_trigger.sql b/src/pl/tcl/sql/pltcl_trigger.sql index 0ed00f49526..9152eaeb0f6 100644 --- a/src/pl/tcl/sql/pltcl_trigger.sql +++ b/src/pl/tcl/sql/pltcl_trigger.sql @@ -602,3 +602,38 @@ FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); TRUNCATE trigger_test_generated; INSERT INTO trigger_test_generated (i) VALUES (1); SELECT * FROM trigger_test_generated; + +-- test FOR PORTION OF + +CREATE TABLE temporal_trigger_test ( + id int, + valid_at daterange, + v text +); +INSERT INTO temporal_trigger_test VALUES (1, '[2018-01-01,2020-01-01)', 'one'); + +CREATE FUNCTION for_portion_of_trigger_func() RETURNS trigger +LANGUAGE pltcl +AS $$ +elog NOTICE "$TG_when $TG_op: TG_period_name = '$TG_period_name', TG_period_bounds = '$TG_period_bounds'" +return OK +$$; + +CREATE TRIGGER for_portion_of_trigger_trig + BEFORE INSERT OR UPDATE OR DELETE ON temporal_trigger_test + FOR EACH ROW EXECUTE PROCEDURE for_portion_of_trigger_func(); + +UPDATE temporal_trigger_test + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET v = 'updated'; +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + +DELETE FROM temporal_trigger_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-09-01'; +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + +-- no FOR PORTION OF, so the variables are empty +UPDATE temporal_trigger_test SET v = 'all of it'; + +DROP TABLE temporal_trigger_test; +DROP FUNCTION for_portion_of_trigger_func(); -- 2.47.3
From 87aed61ef6aec338b1fcbf5329d356702d3be8dc Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Tue, 28 Jul 2026 21:49:03 -0700 Subject: [PATCH v1 4/5] Expose FOR PORTION OF to plpython triggers It is helpful for triggers to see what the FOR PORTION OF clause specified: both the column name and the targeted bounds. We already pass this information to plpgsql trigger functions; this commit does the same for plpython, using the new TD["period_name"] and TD["period_bounds"] elements. These are also set when inserting temporal leftovers, so that triggers can distinguish regular inserts from those automatic ones. Since an INSERT can't use FOR PORTION OF itself, there is no ambiguity. Author: Paul A. Jungwirth <[email protected]> --- doc/src/sgml/plpython.sgml | 31 +++++++++++ src/pl/plpython/expected/plpython_trigger.out | 54 +++++++++++++++++++ src/pl/plpython/plpy_exec.c | 27 ++++++++++ src/pl/plpython/sql/plpython_trigger.sql | 38 +++++++++++++ 4 files changed, 150 insertions(+) diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index c860a47a2e1..19077556a1c 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -751,6 +751,37 @@ $$ LANGUAGE plpython3u; </listitem> </varlistentry> + <varlistentry> + <term><literal>TD["period_name"]</literal></term> + <listitem> + <para> + contains the column name used in a <literal>FOR PORTION OF</literal> + clause. This key is not present if <literal>FOR PORTION OF</literal> + was not used. Also set for the implicit + <literal>INSERT</literal> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>TD["period_bounds"]</literal></term> + <listitem> + <para> + contains the range/multirange given as the bounds of a + <literal>FOR PORTION OF</literal> clause, either directly (with parens + syntax) or computed from the <literal>FROM</literal> and + <literal>TO</literal> bounds. This key is not present if + <literal>FOR PORTION OF</literal> was not used. Also set for the + implicit <literal>INSERT</literal> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. This is a text value based on the type's output + function, since the type can't be known at function creation time. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>TD["args"]</literal></term> <listitem> diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out index bd35b220c5e..907b6b0933d 100644 --- a/src/pl/plpython/expected/plpython_trigger.out +++ b/src/pl/plpython/expected/plpython_trigger.out @@ -673,3 +673,57 @@ NOTICE: TD[event] => ddl_command_start ; TD[tag] => DROP TABLE NOTICE: TD[event] => ddl_command_end ; TD[tag] => DROP TABLE DROP EVENT TRIGGER python_a_snitch; DROP EVENT TRIGGER python_b_snitch; +-- FOR PORTION OF +CREATE TABLE temporal_trigger_test ( + id int, + valid_at daterange, + v text +); +INSERT INTO temporal_trigger_test VALUES (1, '[2018-01-01,2020-01-01)', 'one'); +CREATE FUNCTION for_portion_of_trigger_func() RETURNS trigger +LANGUAGE plpython3u +AS $$ +plpy.notice("TD[when] => " + TD["when"] + " ; TD[event] => " + TD["event"] + + " ; TD[period_name] => " + repr(TD.get("period_name")) + + " ; TD[period_bounds] => " + repr(TD.get("period_bounds"))) +return None +$$; +CREATE TRIGGER for_portion_of_trigger_trig + BEFORE INSERT OR UPDATE OR DELETE ON temporal_trigger_test + FOR EACH ROW EXECUTE PROCEDURE for_portion_of_trigger_func(); +UPDATE temporal_trigger_test + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET v = 'updated'; +NOTICE: TD[when] => BEFORE ; TD[event] => UPDATE ; TD[period_name] => 'valid_at' ; TD[period_bounds] => '[2019-01-01,2019-06-01)' +NOTICE: TD[when] => BEFORE ; TD[event] => INSERT ; TD[period_name] => 'valid_at' ; TD[period_bounds] => '[2019-01-01,2019-06-01)' +NOTICE: TD[when] => BEFORE ; TD[event] => INSERT ; TD[period_name] => 'valid_at' ; TD[period_bounds] => '[2019-01-01,2019-06-01)' +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + id | valid_at | v +----+-------------------------+--------- + 1 | [2018-01-01,2019-01-01) | one + 1 | [2019-01-01,2019-06-01) | updated + 1 | [2019-06-01,2020-01-01) | one +(3 rows) + +DELETE FROM temporal_trigger_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-09-01'; +NOTICE: TD[when] => BEFORE ; TD[event] => DELETE ; TD[period_name] => 'valid_at' ; TD[period_bounds] => '[2018-06-01,2018-09-01)' +NOTICE: TD[when] => BEFORE ; TD[event] => INSERT ; TD[period_name] => 'valid_at' ; TD[period_bounds] => '[2018-06-01,2018-09-01)' +NOTICE: TD[when] => BEFORE ; TD[event] => INSERT ; TD[period_name] => 'valid_at' ; TD[period_bounds] => '[2018-06-01,2018-09-01)' +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + id | valid_at | v +----+-------------------------+--------- + 1 | [2018-01-01,2018-06-01) | one + 1 | [2018-09-01,2019-01-01) | one + 1 | [2019-01-01,2019-06-01) | updated + 1 | [2019-06-01,2020-01-01) | one +(4 rows) + +-- no FOR PORTION OF, so the keys are absent +UPDATE temporal_trigger_test SET v = 'all of it'; +NOTICE: TD[when] => BEFORE ; TD[event] => UPDATE ; TD[period_name] => None ; TD[period_bounds] => None +NOTICE: TD[when] => BEFORE ; TD[event] => UPDATE ; TD[period_name] => None ; TD[period_bounds] => None +NOTICE: TD[when] => BEFORE ; TD[event] => UPDATE ; TD[period_name] => None ; TD[period_bounds] => None +NOTICE: TD[when] => BEFORE ; TD[event] => UPDATE ; TD[period_name] => None ; TD[period_bounds] => None +DROP TABLE temporal_trigger_test; +DROP FUNCTION for_portion_of_trigger_func(); diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.c index 14e63625070..68c531bd578 100644 --- a/src/pl/plpython/plpy_exec.c +++ b/src/pl/plpython/plpy_exec.c @@ -16,6 +16,7 @@ #include "plpy_subxactobject.h" #include "plpy_util.h" #include "utils/fmgrprotos.h" +#include "utils/lsyscache.h" static void ShutdownPLyFunction(Datum arg); static PyObject *PLy_function_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc); @@ -803,6 +804,8 @@ PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *r *pltrelid, *plttablename, *plttableschema, + *pltperiodname, + *pltperiodbounds, *pltargs, *pytnew, *pytold, @@ -858,6 +861,30 @@ PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *r Py_DECREF(plttableschema); pfree(stroid); + /* + * If the query used FOR PORTION OF, report the column name and the + * targeted bounds; otherwise leave these keys out of the dictionary. + * The bounds could be any range or multirange type, so the best we + * can offer is their text representation. + */ + if (tdata->tg_temporal) + { + ForPortionOfState *fpo = tdata->tg_temporal; + Oid funcid; + bool varlena; + + pltperiodname = PLyUnicode_FromString(fpo->fp_rangeName); + PyDict_SetItemString(pltdata, "period_name", pltperiodname); + Py_DECREF(pltperiodname); + + getTypeOutputInfo(fpo->fp_rangeType, &funcid, &varlena); + stroid = OidOutputFunctionCall(funcid, fpo->fp_targetRange); + pltperiodbounds = PLyUnicode_FromString(stroid); + PyDict_SetItemString(pltdata, "period_bounds", pltperiodbounds); + Py_DECREF(pltperiodbounds); + pfree(stroid); + } + if (TRIGGER_FIRED_BEFORE(tdata->tg_event)) pltwhen = PLyUnicode_FromString("BEFORE"); else if (TRIGGER_FIRED_AFTER(tdata->tg_event)) diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql index e1a552e079f..273e605257d 100644 --- a/src/pl/plpython/sql/plpython_trigger.sql +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -516,3 +516,41 @@ DROP TABLE foo; DROP EVENT TRIGGER python_a_snitch; DROP EVENT TRIGGER python_b_snitch; + + +-- FOR PORTION OF + +CREATE TABLE temporal_trigger_test ( + id int, + valid_at daterange, + v text +); +INSERT INTO temporal_trigger_test VALUES (1, '[2018-01-01,2020-01-01)', 'one'); + +CREATE FUNCTION for_portion_of_trigger_func() RETURNS trigger +LANGUAGE plpython3u +AS $$ +plpy.notice("TD[when] => " + TD["when"] + " ; TD[event] => " + TD["event"] + + " ; TD[period_name] => " + repr(TD.get("period_name")) + + " ; TD[period_bounds] => " + repr(TD.get("period_bounds"))) +return None +$$; + +CREATE TRIGGER for_portion_of_trigger_trig + BEFORE INSERT OR UPDATE OR DELETE ON temporal_trigger_test + FOR EACH ROW EXECUTE PROCEDURE for_portion_of_trigger_func(); + +UPDATE temporal_trigger_test + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET v = 'updated'; +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + +DELETE FROM temporal_trigger_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-09-01'; +SELECT * FROM temporal_trigger_test ORDER BY valid_at; + +-- no FOR PORTION OF, so the keys are absent +UPDATE temporal_trigger_test SET v = 'all of it'; + +DROP TABLE temporal_trigger_test; +DROP FUNCTION for_portion_of_trigger_func(); -- 2.47.3
From a8b5a16d880995ac1982ac171408ef9e152f7c38 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Tue, 28 Jul 2026 21:48:18 -0700 Subject: [PATCH v1 3/5] Expose FOR PORTION OF to plperl triggers It is helpful for triggers to see what the FOR PORTION OF clause specified: both the column name and the targeted bounds. We already pass this information to plpgsql trigger functions; this commit does the same for plperl, using the new $_TD->{period_name} and $_TD->{period_bounds} elements. These are also set when inserting temporal leftovers, so that triggers can distinguish regular inserts from those automatic ones. Since an INSERT can't use FOR PORTION OF itself, there is no ambiguity. Author: Paul A. Jungwirth <[email protected]> --- doc/src/sgml/plperl.sgml | 30 +++++++++++++ src/pl/plperl/expected/plperl_trigger.out | 55 +++++++++++++++++++++++ src/pl/plperl/plperl.c | 21 ++++++++- src/pl/plperl/sql/plperl_trigger.sql | 38 ++++++++++++++++ 4 files changed, 143 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 6f018645f11..92b656c4d68 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1290,6 +1290,36 @@ $$ LANGUAGE plperl; </listitem> </varlistentry> + <varlistentry> + <term><literal>$_TD->{period_name}</literal></term> + <listitem> + <para> + The column name used in a <literal>FOR PORTION OF</literal> clause, + or else <literal>undef</literal>. Also set for the implicit + <literal>INSERT</literal> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>$_TD->{period_bounds}</literal></term> + <listitem> + <para> + The range/multirange given as the bounds of a + <literal>FOR PORTION OF</literal> clause, either directly (with parens + syntax) or computed from the <literal>FROM</literal> and + <literal>TO</literal> bounds. <literal>undef</literal> if + <literal>FOR PORTION OF</literal> was not used. Also set for the + implicit <literal>INSERT</literal> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. This is a text value based on the type's output + function, since the type can't be known at function creation time. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>$_TD->{argc}</literal></term> <listitem> diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.out index 42c52ecbba8..0b02f2a027d 100644 --- a/src/pl/plperl/expected/plperl_trigger.out +++ b/src/pl/plperl/expected/plperl_trigger.out @@ -391,3 +391,58 @@ SELECT * FROM trigger_test_generated; ---+---+--- (0 rows) +-- test FOR PORTION OF +CREATE TABLE trigger_test_temporal ( + id int, + valid_at daterange, + v varchar +); +INSERT INTO trigger_test_temporal VALUES (1, '[2018-01-01,2020-01-01)', 'one'); +CREATE FUNCTION for_portion_of_trigger() RETURNS trigger LANGUAGE plperl AS $$ + my $name = $_TD->{period_name}; + my $bounds = $_TD->{period_bounds}; + elog(NOTICE, "$_TD->{when} $_TD->{event}: period_name = " + . (defined $name ? "'$name'" : 'undef') + . ", period_bounds = " + . (defined $bounds ? "'$bounds'" : 'undef')); + return; +$$; +CREATE TRIGGER for_portion_of_trig +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_temporal +FOR EACH ROW EXECUTE PROCEDURE for_portion_of_trigger(); +UPDATE trigger_test_temporal + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET v = 'updated'; +NOTICE: BEFORE UPDATE: period_name = 'valid_at', period_bounds = '[01-01-2019,06-01-2019)' +NOTICE: BEFORE INSERT: period_name = 'valid_at', period_bounds = '[01-01-2019,06-01-2019)' +NOTICE: BEFORE INSERT: period_name = 'valid_at', period_bounds = '[01-01-2019,06-01-2019)' +SELECT * FROM trigger_test_temporal ORDER BY valid_at; + id | valid_at | v +----+-------------------------+--------- + 1 | [01-01-2018,01-01-2019) | one + 1 | [01-01-2019,06-01-2019) | updated + 1 | [06-01-2019,01-01-2020) | one +(3 rows) + +DELETE FROM trigger_test_temporal + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-09-01'; +NOTICE: BEFORE DELETE: period_name = 'valid_at', period_bounds = '[06-01-2018,09-01-2018)' +NOTICE: BEFORE INSERT: period_name = 'valid_at', period_bounds = '[06-01-2018,09-01-2018)' +NOTICE: BEFORE INSERT: period_name = 'valid_at', period_bounds = '[06-01-2018,09-01-2018)' +SELECT * FROM trigger_test_temporal ORDER BY valid_at; + id | valid_at | v +----+-------------------------+--------- + 1 | [01-01-2018,06-01-2018) | one + 1 | [09-01-2018,01-01-2019) | one + 1 | [01-01-2019,06-01-2019) | updated + 1 | [06-01-2019,01-01-2020) | one +(4 rows) + +-- no FOR PORTION OF, so the variables are undef +UPDATE trigger_test_temporal SET v = 'all of it'; +NOTICE: BEFORE UPDATE: period_name = undef, period_bounds = undef +NOTICE: BEFORE UPDATE: period_name = undef, period_bounds = undef +NOTICE: BEFORE UPDATE: period_name = undef, period_bounds = undef +NOTICE: BEFORE UPDATE: period_name = undef, period_bounds = undef +DROP TABLE trigger_test_temporal; +DROP FUNCTION for_portion_of_trigger(); diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c index a59dbc94372..0d948936f9b 100644 --- a/src/pl/plperl/plperl.c +++ b/src/pl/plperl/plperl.c @@ -1629,7 +1629,7 @@ plperl_trigger_build_args(FunctionCallInfo fcinfo) HV *hv; hv = newHV(); - hv_ksplit(hv, 12); /* pre-grow the hash */ + hv_ksplit(hv, 14); /* pre-grow the hash */ tdata = (TriggerData *) fcinfo->context; tupdesc = tdata->tg_relation->rd_att; @@ -1705,6 +1705,25 @@ plperl_trigger_build_args(FunctionCallInfo fcinfo) hv_store_string(hv, "table_schema", cstr2sv(SPI_getnspname(tdata->tg_relation))); + /* + * If the query used FOR PORTION OF, report the column name and the + * targeted bounds. The bounds could be any range or multirange type, so + * the best we can offer is their text representation. + */ + if (tdata->tg_temporal) + { + ForPortionOfState *fpo = tdata->tg_temporal; + Oid funcid; + bool varlena; + + hv_store_string(hv, "period_name", cstr2sv(fpo->fp_rangeName)); + + getTypeOutputInfo(fpo->fp_rangeType, &funcid, &varlena); + hv_store_string(hv, "period_bounds", + cstr2sv(OidOutputFunctionCall(funcid, + fpo->fp_targetRange))); + } + if (TRIGGER_FIRED_BEFORE(tdata->tg_event)) when = "BEFORE"; else if (TRIGGER_FIRED_AFTER(tdata->tg_event)) diff --git a/src/pl/plperl/sql/plperl_trigger.sql b/src/pl/plperl/sql/plperl_trigger.sql index 2798a02fa12..cf88658cc88 100644 --- a/src/pl/plperl/sql/plperl_trigger.sql +++ b/src/pl/plperl/sql/plperl_trigger.sql @@ -258,3 +258,41 @@ FOR EACH ROW EXECUTE PROCEDURE generated_test_func1(); TRUNCATE trigger_test_generated; INSERT INTO trigger_test_generated (i) VALUES (1); SELECT * FROM trigger_test_generated; + +-- test FOR PORTION OF + +CREATE TABLE trigger_test_temporal ( + id int, + valid_at daterange, + v varchar +); +INSERT INTO trigger_test_temporal VALUES (1, '[2018-01-01,2020-01-01)', 'one'); + +CREATE FUNCTION for_portion_of_trigger() RETURNS trigger LANGUAGE plperl AS $$ + my $name = $_TD->{period_name}; + my $bounds = $_TD->{period_bounds}; + elog(NOTICE, "$_TD->{when} $_TD->{event}: period_name = " + . (defined $name ? "'$name'" : 'undef') + . ", period_bounds = " + . (defined $bounds ? "'$bounds'" : 'undef')); + return; +$$; + +CREATE TRIGGER for_portion_of_trig +BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_temporal +FOR EACH ROW EXECUTE PROCEDURE for_portion_of_trigger(); + +UPDATE trigger_test_temporal + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET v = 'updated'; +SELECT * FROM trigger_test_temporal ORDER BY valid_at; + +DELETE FROM trigger_test_temporal + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-09-01'; +SELECT * FROM trigger_test_temporal ORDER BY valid_at; + +-- no FOR PORTION OF, so the variables are undef +UPDATE trigger_test_temporal SET v = 'all of it'; + +DROP TABLE trigger_test_temporal; +DROP FUNCTION for_portion_of_trigger(); -- 2.47.3
From ffd3194276ee6f928926e6528862c777ed9bce15 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Tue, 29 Oct 2024 18:54:37 -0700 Subject: [PATCH v1 2/5] Expose FOR PORTION OF to plpgsql triggers It is helpful for triggers to see what the FOR PORTION OF clause specified: both the column name and the targeted bounds. Our RI triggers require this information, and we are passing it as part of the TriggerData struct. This commit allows plpgsql trigger functions to access the same information, using the new TG_PERIOD_NAME and TG_PERIOD_BOUNDS variables. These variables are also set when inserting temporal leftovers, so that triggers can distinguish regular inserts from those automatic ones. Since an INSERT can't use FOR PORTION OF itself, there is no ambiguity. Author: Paul A. Jungwirth <[email protected]> --- .../expected/level_tracking.out | 2 +- doc/src/sgml/plpgsql.sgml | 29 +++ src/pl/plpgsql/src/pl_comp.c | 26 +++ src/pl/plpgsql/src/pl_exec.c | 32 +++ src/pl/plpgsql/src/plpgsql.h | 2 + src/test/regress/expected/for_portion_of.out | 193 ++++++++++++------ src/test/regress/sql/for_portion_of.sql | 47 ++++- 7 files changed, 264 insertions(+), 67 deletions(-) diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index 832d65e97ca..d664201498d 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -1598,7 +1598,7 @@ SELECT toplevel, calls, rows, plans, query FROM pg_stat_statements ORDER BY query COLLATE "C"; toplevel | calls | rows | plans | query ----------+-------+------+-------+----------------------------------------------------- - f | 2 | 2 | 0 | INSERT INTO audit_table VALUES ($15, TG_OP, NEW.id) + f | 2 | 2 | 0 | INSERT INTO audit_table VALUES ($17, TG_OP, NEW.id) t | 2 | 2 | 0 | INSERT INTO test_trigger VALUES ($1, $2) t | 1 | 1 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (3 rows) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 561f6e50d63..2750a2a40a2 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -4247,6 +4247,35 @@ ASSERT <replaceable class="parameter">condition</replaceable> <optional> , <repl </para> </listitem> </varlistentry> + + <varlistentry id="plpgsql-dml-trigger-tg-temporal-column"> + <term><varname>TG_PERIOD_NAME</varname> <type>text</type></term> + <listitem> + <para> + the column name used in a <literal>FOR PORTION OF</literal> clause, + or else <symbol>NULL</symbol>. Also set for the implicit + <literal>INSERT</literal> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. + </para> + </listitem> + </varlistentry> + + <varlistentry id="plpgsql-dml-trigger-tg-temporal-target"> + <term><varname>TG_PERIOD_BOUNDS</varname> <type>text</type></term> + <listitem> + <para> + the range/multirange given as the bounds of a + <literal>FOR PORTION OF</literal> clause, either directly (with parens syntax) + or computed from the <literal>FROM</literal> and <literal>TO</literal> bounds. + <symbol>NULL</symbol> if <literal>FOR PORTION OF</literal> was not used. + Also set for the implicit <literal>INSERT</literal> statements to add the + <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. This is a text value based on the type's output + function, since the type can't be known at function creation time. + </para> + </listitem> + </varlistentry> </variablelist> </para> diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index 75184b75707..9550bdcc112 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -617,6 +617,32 @@ plpgsql_compile_callback(FunctionCallInfo fcinfo, var->dtype = PLPGSQL_DTYPE_PROMISE; ((PLpgSQL_var *) var)->promise = PLPGSQL_PROMISE_TG_ARGV; + /* Add the variable tg_period_name */ + var = plpgsql_build_variable("tg_period_name", 0, + plpgsql_build_datatype(TEXTOID, + -1, + function->fn_input_collation, + NULL), + true); + Assert(var->dtype == PLPGSQL_DTYPE_VAR); + var->dtype = PLPGSQL_DTYPE_PROMISE; + ((PLpgSQL_var *) var)->promise = PLPGSQL_PROMISE_TG_PERIOD_NAME; + + /* + * Add the variable tg_period_bounds. This could be any rangetype + * or multirangetype or (XXX: someday) user-supplied type, so the + * best we can offer is a TEXT variable. + */ + var = plpgsql_build_variable("tg_period_bounds", 0, + plpgsql_build_datatype(TEXTOID, + -1, + function->fn_input_collation, + NULL), + true); + Assert(var->dtype == PLPGSQL_DTYPE_VAR); + var->dtype = PLPGSQL_DTYPE_PROMISE; + ((PLpgSQL_var *) var)->promise = PLPGSQL_PROMISE_TG_PERIOD_BOUNDS; + break; case PLPGSQL_EVENT_TRIGGER: diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 341beb496b8..68818477701 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -1416,6 +1416,7 @@ plpgsql_fulfill_promise(PLpgSQL_execstate *estate, PLpgSQL_var *var) { MemoryContext oldcontext; + ForPortionOfState *fpo; if (var->promise == PLPGSQL_PROMISE_NONE) return; /* nothing to do */ @@ -1547,6 +1548,37 @@ plpgsql_fulfill_promise(PLpgSQL_execstate *estate, } break; + case PLPGSQL_PROMISE_TG_PERIOD_NAME: + if (estate->trigdata == NULL) + elog(ERROR, "trigger promise is not in a trigger function"); + if (estate->trigdata->tg_temporal) + assign_text_var(estate, var, estate->trigdata->tg_temporal->fp_rangeName); + else + assign_simple_var(estate, var, (Datum) 0, true, false); + break; + + case PLPGSQL_PROMISE_TG_PERIOD_BOUNDS: + if (estate->trigdata == NULL) + elog(ERROR, "trigger promise is not in a trigger function"); + + fpo = estate->trigdata->tg_temporal; + if (fpo) + { + + Oid funcid; + bool varlena; + + getTypeOutputInfo(fpo->fp_rangeType, &funcid, &varlena); + Assert(OidIsValid(funcid)); + + assign_text_var(estate, var, + OidOutputFunctionCall(funcid, + fpo->fp_targetRange)); + } + else + assign_simple_var(estate, var, (Datum) 0, true, false); + break; + case PLPGSQL_PROMISE_TG_EVENT: if (estate->evtrigdata == NULL) elog(ERROR, "event trigger promise is not in an event trigger function"); diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index addb14a9959..70ffbb3b29a 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -85,6 +85,8 @@ typedef enum PLpgSQL_promise_type PLPGSQL_PROMISE_TG_ARGV, PLPGSQL_PROMISE_TG_EVENT, PLPGSQL_PROMISE_TG_TAG, + PLPGSQL_PROMISE_TG_PERIOD_NAME, + PLPGSQL_PROMISE_TG_PERIOD_BOUNDS, } PLpgSQL_promise_type; /* diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out index 1a53e549de0..08ffd806598 100644 --- a/src/test/regress/expected/for_portion_of.out +++ b/src/test/regress/expected/for_portion_of.out @@ -1350,8 +1350,13 @@ CREATE FUNCTION dump_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN - RAISE NOTICE '%: % % %:', - TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + IF TG_PERIOD_NAME IS NOT NULL THEN + RAISE NOTICE '%: % % FOR PORTION OF % (%) %:', + TG_NAME, TG_WHEN, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL; + ELSE + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + END IF; IF TG_ARGV[0] THEN RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); @@ -1407,85 +1412,85 @@ 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: fpo_before_stmt: BEFORE UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_stmt1: BEFORE UPDATE STATEMENT: +NOTICE: fpo_before_stmt1: BEFORE UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: fpo_before_row: BEFORE UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) ROW: NOTICE: old: [2019-01-01,2030-01-01) NOTICE: new: [2021-01-01,2022-01-01) -NOTICE: fpo_before_row1: BEFORE UPDATE ROW: +NOTICE: fpo_before_row1: BEFORE UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) 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: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2019-01-01,2021-01-01) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2019-01-01,2021-01-01) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2022-01-01,2030-01-01) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2022-01-01,2030-01-01) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) 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: fpo_after_update_stmt: AFTER UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) 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: fpo_before_stmt: BEFORE DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: fpo_before_row: BEFORE DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: [2022-01-01,2030-01-01) NOTICE: new: <NULL> -NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2022-01-01,2023-01-01) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2022-01-01,2023-01-01) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2024-01-01,2030-01-01) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2024-01-01,2030-01-01) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: [2022-01-01,2030-01-01) NOTICE: new: <NULL> -NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: fpo_after_delete_stmt: AFTER DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> SELECT * FROM for_portion_of_test ORDER BY id, valid_at; @@ -1553,68 +1558,68 @@ 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: fpo_before_stmt: BEFORE UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: fpo_before_row: BEFORE UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) 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: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2018-01-01,2018-01-15) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one) -NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2019-01-01,2020-01-01) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) 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: fpo_after_update_stmt: AFTER UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) 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: fpo_before_stmt: BEFORE DELETE FOR PORTION OF valid_at ((,2018-01-21)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: fpo_before_row: BEFORE DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: [2018-01-01,2020-01-01) NOTICE: new: <NULL> -NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ((,2018-01-21)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: <NULL> NOTICE: new: [2018-01-21,2020-01-01) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ((,2018-01-21)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: <NULL> -NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: fpo_after_delete_stmt: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) STATEMENT: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: <NULL> ROLLBACK; @@ -1622,28 +1627,28 @@ 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: fpo_before_stmt: BEFORE UPDATE FOR PORTION OF valid_at ((,2018-01-02)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: fpo_before_row: BEFORE UPDATE FOR PORTION OF valid_at ((,2018-01-02)) 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: fpo_before_stmt: BEFORE INSERT FOR PORTION OF valid_at ((,2018-01-02)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: fpo_before_row: BEFORE INSERT FOR PORTION OF valid_at ((,2018-01-02)) ROW: NOTICE: old: <NULL> NOTICE: new: [2018-01-02,2020-01-01) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ((,2018-01-02)) ROW: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) -NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: fpo_after_insert_stmt: AFTER INSERT FOR PORTION OF valid_at ((,2018-01-02)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ((,2018-01-02)) 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: fpo_after_update_stmt: AFTER UPDATE FOR PORTION OF valid_at ((,2018-01-02)) 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; @@ -1674,26 +1679,26 @@ 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: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2018-01-01,2018-01-15) -NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: <NULL> NOTICE: new: [2019-01-01,2020-01-01) -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) 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: fpo_after_insert_row: AFTER INSERT FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: <NULL> NOTICE: new: [2018-01-21,2019-01-01) -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: [2018-01-15,2019-01-01) NOTICE: new: <NULL> -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: [2018-01-01,2018-01-15) NOTICE: new: <NULL> BEGIN; @@ -1708,6 +1713,66 @@ SELECT * FROM for_portion_of_test; [1,2) | [2018-01-21,2019-01-01) | 2018-01-15_to_2019-01-01 (2 rows) +-- test inserts fired 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'); +CREATE FUNCTION trg_fpo_insert() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + INSERT INTO for_portion_of_test + (id, valid_at, name) VALUES + (int4range(lower(NEW.id) + 1, upper(NEW.id) + 1), NEW.valid_at, NEW.name); + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; +CREATE TRIGGER fpo_01_dump_before + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_02_before_row + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_insert(); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET name = CONCAT(name, '^') + WHERE id = '[1,2)'; +NOTICE: fpo_01_dump_before: BEFORE UPDATE FOR PORTION OF valid_at ([2019-01-01,2019-06-01)) ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: [2019-01-01,2019-06-01) +NOTICE: fpo_01_dump_before: BEFORE INSERT ROW: +NOTICE: old: <NULL> +NOTICE: new: [2019-01-01,2019-06-01) +NOTICE: fpo_01_dump_before: BEFORE INSERT FOR PORTION OF valid_at ([2019-01-01,2019-06-01)) ROW: +NOTICE: old: <NULL> +NOTICE: new: [2018-01-01,2019-01-01) +NOTICE: fpo_01_dump_before: BEFORE INSERT ROW: +NOTICE: old: <NULL> +NOTICE: new: [2018-01-01,2019-01-01) +NOTICE: fpo_01_dump_before: BEFORE INSERT FOR PORTION OF valid_at ([2019-01-01,2019-06-01)) ROW: +NOTICE: old: <NULL> +NOTICE: new: [2019-06-01,2020-01-01) +NOTICE: fpo_01_dump_before: BEFORE INSERT ROW: +NOTICE: old: <NULL> +NOTICE: new: [2019-06-01,2020-01-01) +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2018-01-01,2019-01-01) | one + [1,2) | [2019-01-01,2019-06-01) | one^ + [1,2) | [2019-06-01,2020-01-01) | one + [2,3) | [2018-01-01,2019-01-01) | one + [2,3) | [2019-01-01,2019-06-01) | one^ + [2,3) | [2019-06-01,2020-01-01) | one +(6 rows) + -- test FOR PORTION OF from triggers during FOR PORTION OF: DROP TABLE for_portion_of_test; CREATE TABLE for_portion_of_test ( @@ -2610,10 +2675,10 @@ CREATE TRIGGER fpo_before_row2 UPDATE fpo_update_of_trigger FOR PORTION OF valid_at FROM 30 TO 70 SET id = 2; -NOTICE: fpo_before_row2: BEFORE UPDATE STATEMENT: +NOTICE: fpo_before_row2: BEFORE UPDATE FOR PORTION OF valid_at ([30,70)) STATEMENT: NOTICE: old: <NULL> NOTICE: new: <NULL> -NOTICE: fpo_before_row1: BEFORE UPDATE ROW: +NOTICE: fpo_before_row1: BEFORE UPDATE FOR PORTION OF valid_at ([30,70)) ROW: NOTICE: old: [10,100) NOTICE: new: [30,70) DROP TABLE fpo_update_of_trigger; diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql index 955cf666d66..09e93309483 100644 --- a/src/test/regress/sql/for_portion_of.sql +++ b/src/test/regress/sql/for_portion_of.sql @@ -893,8 +893,13 @@ CREATE FUNCTION dump_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN - RAISE NOTICE '%: % % %:', - TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + IF TG_PERIOD_NAME IS NOT NULL THEN + RAISE NOTICE '%: % % FOR PORTION OF % (%) %:', + TG_NAME, TG_WHEN, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL; + ELSE + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + END IF; IF TG_ARGV[0] THEN RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); @@ -1086,6 +1091,44 @@ COMMIT; SELECT * FROM for_portion_of_test; +-- test inserts fired 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'); + +CREATE FUNCTION trg_fpo_insert() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + INSERT INTO for_portion_of_test + (id, valid_at, name) VALUES + (int4range(lower(NEW.id) + 1, upper(NEW.id) + 1), NEW.valid_at, NEW.name); + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; + +CREATE TRIGGER fpo_01_dump_before + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_02_before_row + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_insert(); + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01' + SET name = CONCAT(name, '^') + WHERE id = '[1,2)'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + -- test FOR PORTION OF from triggers during FOR PORTION OF: DROP TABLE for_portion_of_test; -- 2.47.3
From d8317f4e967223b929fd4e327643dca0f8ad4bf8 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Fri, 13 Jun 2025 15:40:06 -0700 Subject: [PATCH v1 1/5] Add tg_temporal to TriggerData This needs to be passed to our RI triggers to implement temporal CASCADE/SET NULL/SET DEFAULT when the user command is an UPDATE/DELETE FOR PORTION OF. The triggers will use the FOR PORTION OF bounds to avoid over-applying the change to referencing records. Probably it is useful for user-defined triggers as well, for example auditing or trigger-based replication. We also set the field when inserting temporal leftovers, so that triggers can distinguish between a regular INSERT and an automatic INSERT just to preserve history. Author: Paul A. Jungwirth <[email protected]> --- doc/src/sgml/trigger.sgml | 67 +++++++++++++++--- src/backend/commands/trigger.c | 94 +++++++++++++++++++++++++- src/backend/executor/nodeModifyTable.c | 2 + src/backend/parser/analyze.c | 1 + src/include/commands/trigger.h | 1 + src/include/nodes/execnodes.h | 1 + src/include/nodes/primnodes.h | 1 + 7 files changed, 155 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 8a5e7278212..4cecdc2b9e7 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -566,17 +566,18 @@ CALLED_AS_TRIGGER(fcinfo) <programlisting> typedef struct TriggerData { - NodeTag type; - TriggerEvent tg_event; - Relation tg_relation; - HeapTuple tg_trigtuple; - HeapTuple tg_newtuple; - Trigger *tg_trigger; - TupleTableSlot *tg_trigslot; - TupleTableSlot *tg_newslot; - Tuplestorestate *tg_oldtable; - Tuplestorestate *tg_newtable; - const Bitmapset *tg_updatedcols; + NodeTag type; + TriggerEvent tg_event; + Relation tg_relation; + HeapTuple tg_trigtuple; + HeapTuple tg_newtuple; + Trigger *tg_trigger; + TupleTableSlot *tg_trigslot; + TupleTableSlot *tg_newslot; + Tuplestorestate *tg_oldtable; + Tuplestorestate *tg_newtable; + const Bitmapset *tg_updatedcols; + ForPortionOfState *tg_temporal; } TriggerData; </programlisting> @@ -844,6 +845,50 @@ typedef struct Trigger </para> </listitem> </varlistentry> + + <varlistentry> + <term><structfield>tg_temporal</structfield></term> + <listitem> + <para> + Set for <literal>UPDATE</literal> and <literal>DELETE</literal> queries + that use <literal>FOR PORTION OF</literal>, otherwise <symbol>NULL</symbol>. + Also set for the implicit <literal>INSERT</literal> statements to add + the <glossterm linkend="glossary-temporal-leftovers">temporal + leftovers</glossterm>. Contains a pointer to a structure of type + <structname>ForPortionOfState</structname>, defined in + <filename>nodes/execnodes.h</filename>: + +<programlisting> +typedef struct ForPortionOfState +{ + NodeTag type; + + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + int fp_rangeAttno; /* the attno of the range/multirange column */ + Datum fp_targetRange; /* the range/multirange from FOR PORTION OF */ + TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range/multirange */ + TupleTableSlot *fp_Existing; /* slot to store old tuple */ + TupleTableSlot *fp_Leftover; /* slot to store leftover */ + char *fp_rangeName; /* the column named in FOR PORTION OF */ +} ForPortionOfState; +</programlisting> + + where <structfield>fp_rangeType</structfield> is its range type, + <structfield>fp_rangeAttno</structfield> is its attribute number, + <structfield>fp_targetRange</structfield> is a rangetype value created + by evaluating the <literal>FOR PORTION OF</literal> bounds, + <structfield>fp_leftoverstypcache</structfield> is the type cache entry of the range column, + and <structfield>fp_rangeName</structfield> is the range + column named in the <literal>FOR PORTION OF</literal> clause. + The <structfield>fp_Existing</structfield> and + <structfield>fp_Leftover</structfield> tuple table slots are for the + executor's own use, and trigger functions should not rely on them: they + do not outlive the query that created them, so they are + <symbol>NULL</symbol> in an <literal>AFTER</literal> trigger, which may + run much later. + </para> + </listitem> + </varlistentry> </variablelist> </para> diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 79ffd2cada6..289fbf774e4 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -49,6 +49,7 @@ #include "storage/lmgr.h" #include "utils/acl.h" #include "utils/builtins.h" +#include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/guc_hooks.h" #include "utils/inval.h" @@ -2459,6 +2460,7 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -2515,6 +2517,7 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -2619,6 +2622,7 @@ ExecIRInsertTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_INSTEAD; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -2688,6 +2692,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_event = TRIGGER_EVENT_DELETE | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -2796,6 +2801,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { HeapTuple newtuple; @@ -2897,6 +2903,7 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_INSTEAD; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; ExecForceStoreHeapTuple(trigtuple, slot, false); @@ -2960,6 +2967,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo) TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; LocTriggerData.tg_updatedcols = updatedCols; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -3103,6 +3111,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; updatedCols = ExecGetAllUpdatedCols(relinfo, estate); LocTriggerData.tg_updatedcols = updatedCols; for (i = 0; i < trigdesc->numtriggers; i++) @@ -3265,6 +3274,7 @@ ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_INSTEAD; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; ExecForceStoreHeapTuple(trigtuple, oldslot, false); @@ -3736,6 +3746,7 @@ typedef struct AfterTriggerSharedData Oid ats_relid; /* the relation it's on */ Oid ats_rolid; /* role to execute the trigger */ CommandId ats_firing_id; /* ID for firing cycle */ + ForPortionOfState *ats_for_portion_of; /* the FOR PORTION OF clause */ struct AfterTriggersTableData *ats_table; /* transition table access */ Bitmapset *ats_modifiedcols; /* modified columns */ } AfterTriggerSharedData; @@ -4128,6 +4139,81 @@ afterTriggerCopyBitmap(Bitmapset *src) return dst; } +/* ---------- + * afterTriggerCopyForPortionOfState() + * + * Copies a ForPortionOfState into the afterTriggers memory context, so that + * it survives long enough for DEFERRED triggers. + * + * We copy only what a trigger can use: the range column's name, type and + * attribute number, and the bounds computed for the clause. The tuple table + * slots are left NULL, since they belong to the executor and are gone by the + * time a deferred trigger runs. The type cache entry is safe to share, + * because those live as long as the session. + */ +static ForPortionOfState * +afterTriggerCopyForPortionOfState(ForPortionOfState *src) +{ + ForPortionOfState *dst; + MemoryContext oldcxt; + bool typbyval; + int16 typlen; + + if (src == NULL) + return NULL; + + oldcxt = MemoryContextSwitchTo(afterTriggers.event_cxt); + + get_typlenbyval(src->fp_rangeType, &typlen, &typbyval); + + dst = makeNode(ForPortionOfState); + dst->fp_rangeName = pstrdup(src->fp_rangeName); + dst->fp_rangeType = src->fp_rangeType; + dst->fp_rangeAttno = src->fp_rangeAttno; + dst->fp_leftoverstypcache = src->fp_leftoverstypcache; + dst->fp_Existing = NULL; + dst->fp_Leftover = NULL; + dst->fp_targetRange = datumCopy(src->fp_targetRange, typbyval, typlen); + + MemoryContextSwitchTo(oldcxt); + + return dst; +} + +/* ---------- + * afterTriggerForPortionOfStatesEqual() + * + * Tells whether two ForPortionOfStates describe the same FOR PORTION OF + * clause, so that events can share one AfterTriggerSharedData. + * + * We must compare by value, not by pointer: what a shared record holds is a + * copy made by afterTriggerCopyForPortionOfState, so it never has the same + * address as the state the executor is handing us. This is the same reason + * we compare ats_modifiedcols with bms_equal. + */ +static bool +afterTriggerForPortionOfStatesEqual(ForPortionOfState *a, ForPortionOfState *b) +{ + bool typbyval; + int16 typlen; + + if (a == NULL || b == NULL) + return a == b; + + if (a->fp_rangeType != b->fp_rangeType || + a->fp_rangeAttno != b->fp_rangeAttno) + return false; + + /* + * The bounds are computed once per query, so the two datums are byte for + * byte identical whenever they came from the same clause, and + * datumIsEqual is enough. (They are freshly built range values, never + * toasted.) + */ + get_typlenbyval(a->fp_rangeType, &typlen, &typbyval); + return datumIsEqual(a->fp_targetRange, b->fp_targetRange, typbyval, typlen); +} + /* ---------- * afterTriggerAddEvent() * @@ -4232,7 +4318,9 @@ afterTriggerAddEvent(AfterTriggerEventList *events, newshared->ats_relid == evtshared->ats_relid && newshared->ats_rolid == evtshared->ats_rolid && bms_equal(newshared->ats_modifiedcols, - evtshared->ats_modifiedcols)) + evtshared->ats_modifiedcols) && + afterTriggerForPortionOfStatesEqual(newshared->ats_for_portion_of, + evtshared->ats_for_portion_of)) break; } if ((char *) newshared >= chunk->endptr) @@ -4241,6 +4329,8 @@ afterTriggerAddEvent(AfterTriggerEventList *events, *newshared = *evtshared; /* now we must make a suitably-long-lived copy of the bitmap */ newshared->ats_modifiedcols = afterTriggerCopyBitmap(evtshared->ats_modifiedcols); + /* Likewise we must make a copy of the ForPortionOfState */ + newshared->ats_for_portion_of = afterTriggerCopyForPortionOfState(evtshared->ats_for_portion_of); newshared->ats_firing_id = 0; /* just to be sure */ chunk->endfree = (char *) newshared; } @@ -4597,6 +4687,7 @@ AfterTriggerExecute(EState *estate, LocTriggerData.tg_event = evtshared->ats_event & (TRIGGER_EVENT_OPMASK | TRIGGER_EVENT_ROW); LocTriggerData.tg_relation = rel; + LocTriggerData.tg_temporal = evtshared->ats_for_portion_of; if (TRIGGER_FOR_UPDATE(LocTriggerData.tg_trigger->tgtype)) LocTriggerData.tg_updatedcols = evtshared->ats_modifiedcols; @@ -6695,6 +6786,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, else new_shared.ats_table = NULL; new_shared.ats_modifiedcols = modifiedCols; + new_shared.ats_for_portion_of = relinfo->ri_forPortionOf; afterTriggerAddEvent(&afterTriggers.query_stack[afterTriggers.query_depth].events, &new_event, &new_shared); diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 3056b850f73..b272c5a9733 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -5652,6 +5652,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) /* Create state for FOR PORTION OF operation */ fpoState = makeNode(ForPortionOfState); + fpoState->fp_rangeName = pstrdup(forPortionOf->range_name); fpoState->fp_rangeType = forPortionOf->rangeType; fpoState->fp_rangeAttno = forPortionOf->rangeVar->varattno; fpoState->fp_targetRange = targetRange; @@ -5938,6 +5939,7 @@ ExecInitForPortionOf(ModifyTableState *mtstate, EState *estate, leafState = makeNode(ForPortionOfState); + leafState->fp_rangeName = pstrdup(fpoState->fp_rangeName); leafState->fp_rangeType = fpoState->fp_rangeType; leafState->fp_targetRange = fpoState->fp_targetRange; map = ExecGetChildToRootMap(resultRelInfo); diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 263d1b6e1cc..0be92fb7013 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1608,6 +1608,7 @@ transformForPortionOfClause(ParseState *pstate, result->location = forPortionOf->location; result->targetLocation = forPortionOf->target_location; + result->range_name = forPortionOf->range_name; return result; } diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index fecdb785f35..97ce13e13f8 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -41,6 +41,7 @@ typedef struct TriggerData Tuplestorestate *tg_oldtable; Tuplestorestate *tg_newtable; const Bitmapset *tg_updatedcols; + ForPortionOfState *tg_temporal; } TriggerData; /* diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index f0cb21444b2..524f9beef50 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -483,6 +483,7 @@ typedef struct ForPortionOfState TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */ TupleTableSlot *fp_Existing; /* slot to store old tuple */ TupleTableSlot *fp_Leftover; /* slot to store leftover */ + char *fp_rangeName; /* the column named in FOR PORTION OF */ } ForPortionOfState; /* diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 5a636d1f179..e4d6e78e621 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -2453,6 +2453,7 @@ typedef struct ForPortionOfExpr 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 */ + char *range_name; /* Range name */ } ForPortionOfExpr; #endif /* PRIMNODES_H */ -- 2.47.3
