Follow-on cleanup for the transition table patch.

Commit 59702716 added transition table support to PL/pgsql so that
SQL queries in trigger functions could access those transient
tables.  In order to provide the same level of support for PL/perl,
PL/python and PL/tcl, refactor the relevant code into a new
function SPI_register_trigger_data.  Call the new function in the
trigger handler of all four PLs, and document it as a public SPI
function so that authors of out-of-tree PLs can do the same.

Also get rid of a second QueryEnvironment object that was
maintained by PL/pgsql.  That was previously used to deal with
cursors, but the same approach wasn't appropriate for PLs that are
less tangled up with core code.  Instead, have SPI_cursor_open
install the connection's current QueryEnvironment, as already
happens for SPI_execute_plan.

While in the docs, remove the note that transition tables were only
supported in C and PL/pgSQL triggers, and correct some ommissions.

Thomas Munro with some work by Kevin Grittner (mostly docs)

3 files changed, 68 insertions, 0 deletions

diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.out
index 5e3860ef97b..28011cd9f64 100644
--- a/src/pl/plperl/expected/plperl_trigger.out
+++ b/src/pl/plperl/expected/plperl_trigger.out
@@ -241,6 +241,35 @@ $$ LANGUAGE plperl;
 SELECT direct_trigger();
 ERROR:  trigger functions can only be called as triggers
 CONTEXT:  compilation of PL/Perl function "direct_trigger"
+-- check that SQL run in trigger code can see transition tables
+CREATE TABLE transition_table_test (id int, name text);
+INSERT INTO transition_table_test VALUES (1, 'a');
+CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plperl AS
+$$
+    my $cursor = spi_query("SELECT * FROM old_table");
+    my $row = spi_fetchrow($cursor);
+    defined($row) || die "expected a row";
+    elog(INFO, "old: " . $row->{id} . " -> " . $row->{name});
+    my $row = spi_fetchrow($cursor);
+    !defined($row) || die "expected no more rows";
+
+    my $cursor = spi_query("SELECT * FROM new_table");
+    my $row = spi_fetchrow($cursor);
+    defined($row) || die "expected a row";
+    elog(INFO, "new: " . $row->{id} . " -> " . $row->{name});
+    my $row = spi_fetchrow($cursor);
+    !defined($row) || die "expected no more rows";
+
+    return undef;
+$$;
+CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
+  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
+  FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f();
+UPDATE transition_table_test SET name = 'b';
+INFO:  old: 1 -> a
+INFO:  new: 1 -> b
+DROP TABLE transition_table_test;
+DROP FUNCTION transition_table_test_f();
 -- test plperl command triggers
 create or replace function perlsnitch() returns event_trigger language plperl as $$
   elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " ");
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 00979cba743..1de770b2505 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -2442,11 +2442,18 @@ plperl_trigger_handler(PG_FUNCTION_ARGS)
 	SV		   *svTD;
 	HV		   *hvTD;
 	ErrorContextCallback pl_error_context;
+	TriggerData *tdata;
+	int 		rc PG_USED_FOR_ASSERTS_ONLY;
 
 	/* Connect to SPI manager */
 	if (SPI_connect() != SPI_OK_CONNECT)
 		elog(ERROR, "could not connect to SPI manager");
 
+	/* Make transition tables visible to this SPI connection */
+	tdata = (TriggerData *) fcinfo->context;
+	rc = SPI_register_trigger_data(tdata);
+	Assert(rc >= 0);
+
 	/* Find or compile the function */
 	prodesc = compile_plperl_function(fcinfo->flinfo->fn_oid, true, false);
 	current_call_data->prodesc = prodesc;
diff --git a/src/pl/plperl/sql/plperl_trigger.sql b/src/pl/plperl/sql/plperl_trigger.sql
index a375b401ea2..624193b9d08 100644
--- a/src/pl/plperl/sql/plperl_trigger.sql
+++ b/src/pl/plperl/sql/plperl_trigger.sql
@@ -170,6 +170,38 @@ $$ LANGUAGE plperl;
 
 SELECT direct_trigger();
 
+-- check that SQL run in trigger code can see transition tables
+
+CREATE TABLE transition_table_test (id int, name text);
+INSERT INTO transition_table_test VALUES (1, 'a');
+
+CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plperl AS
+$$
+    my $cursor = spi_query("SELECT * FROM old_table");
+    my $row = spi_fetchrow($cursor);
+    defined($row) || die "expected a row";
+    elog(INFO, "old: " . $row->{id} . " -> " . $row->{name});
+    my $row = spi_fetchrow($cursor);
+    !defined($row) || die "expected no more rows";
+
+    my $cursor = spi_query("SELECT * FROM new_table");
+    my $row = spi_fetchrow($cursor);
+    defined($row) || die "expected a row";
+    elog(INFO, "new: " . $row->{id} . " -> " . $row->{name});
+    my $row = spi_fetchrow($cursor);
+    !defined($row) || die "expected no more rows";
+
+    return undef;
+$$;
+
+CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
+  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
+  FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f();
+UPDATE transition_table_test SET name = 'b';
+
+DROP TABLE transition_table_test;
+DROP FUNCTION transition_table_test_f();
+
 -- test plperl command triggers
 create or replace function perlsnitch() returns event_trigger language plperl as $$
   elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " ");