From 5ebeb579b9b281dba5f8415b2fbda86fdae7b366 Mon Sep 17 00:00:00 2001 From: Kevin Grittner Date: Tue, 4 Apr 2017 18:36:39 -0500 Subject: [PATCH] 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) --- doc/src/sgml/ref/create_trigger.sgml | 10 +- doc/src/sgml/spi.sgml | 125 ++++++++++++++++++ doc/src/sgml/trigger.sgml | 54 ++++++-- src/backend/executor/spi.c | 52 ++++++++ src/include/executor/spi.h | 3 + src/pl/plperl/expected/plperl_trigger.out | 29 ++++ src/pl/plperl/plperl.c | 7 + src/pl/plperl/sql/plperl_trigger.sql | 32 +++++ src/pl/plpgsql/src/pl_exec.c | 49 +------ src/pl/plpgsql/src/plpgsql.h | 4 - src/pl/plpython/expected/plpython_trigger.out | 21 +++ src/pl/plpython/plpy_exec.c | 5 + src/pl/plpython/sql/plpython_trigger.sql | 24 ++++ src/pl/tcl/expected/pltcl_queries.out | 21 +++ src/pl/tcl/pltcl.c | 5 + src/pl/tcl/sql/pltcl_queries.sql | 20 +++ 16 files changed, 398 insertions(+), 63 deletions(-) diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index e22e42e7dc..24195b3849 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -8,6 +8,11 @@ PostgreSQL documentation CREATE TRIGGER + + transition tables + ephemeral named relation + + CREATE TRIGGER 7 @@ -322,11 +327,6 @@ UPDATE OF column_name1 [, column_name2 The (unqualified) name to be used within the trigger for this relation. - - - So far only triggers written in C or PL/pgSQL support this. - - diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index af7eada2e1..86be87c0fd 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -2644,6 +2644,11 @@ SPIPlanPtr SPI_saveplan(SPIPlanPtr plan) SPI_register_relation + + ephemeral named relation + registering with SPI + + SPI_register_relation 3 @@ -2746,6 +2751,11 @@ int SPI_register_relation(EphemeralNamedRelation enr) SPI_unregister_relation + + ephemeral named relation + unregistering from SPI + + SPI_unregister_relation 3 @@ -2843,6 +2853,121 @@ int SPI_unregister_relation(const char * name) + + SPI_register_trigger_data + + + ephemeral named relation + registering with SPI + + + + transition tables + implementation in PLs + + + + SPI_register_trigger_data + 3 + + + + SPI_register_trigger_data + make ephemeral trigger data available in SPI queries + + + + +int SPI_register_trigger_data(TriggerData *tdata) + + + + + Description + + + SPI_register_trigger_data makes any ephemeral + relations captured by a trigger available to queries planned and executed + through the current SPI connection. Currently, this means the transition + tables captured by an AFTER trigger defined with a + REFERENCING OLD/NEW TABLE AS ... clause. This function + should be called by a PL trigger handler function after connecting. + + + + + Arguments + + + + TriggerData *tdata + + + the TriggerData object passed to a trigger + handler function as fcinfo->context + + + + + + + + Return Value + + + If the execution of the command was successful then the following + (nonnegative) value will be returned: + + + + SPI_OK_TD_REGISTER + + + if the captured trigger data (if any) has been successfully registered + + + + + + + + On error, one of the following negative values is returned: + + + + SPI_ERROR_ARGUMENT + + + if tdata is NULL + + + + + + SPI_ERROR_UNCONNECTED + + + if called from an unconnected procedure + + + + + + SPI_ERROR_REL_DUPLICATE + + + if the name of any trigger data transient relation is already + registered for this connection + + + + + + + + + + diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 8f724c8664..2a718d7f47 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -395,6 +395,11 @@ in C + + transition tables + referencing from C trigger + + This section describes the low-level details of the interface to a trigger function. This information is only needed when writing @@ -438,14 +443,16 @@ CALLED_AS_TRIGGER(fcinfo) typedef struct TriggerData { - NodeTag type; - TriggerEvent tg_event; - Relation tg_relation; - HeapTuple tg_trigtuple; - HeapTuple tg_newtuple; - Trigger *tg_trigger; - Buffer tg_trigtuplebuf; - Buffer tg_newtuplebuf; + NodeTag type; + TriggerEvent tg_event; + Relation tg_relation; + HeapTuple tg_trigtuple; + HeapTuple tg_newtuple; + Trigger *tg_trigger; + Buffer tg_trigtuplebuf; + Buffer tg_newtuplebuf; + Tuplestorestate *tg_oldtable; + Tuplestorestate *tg_newtable; } TriggerData; @@ -629,6 +636,8 @@ typedef struct Trigger int16 *tgattr; char **tgargs; char *tgqual; + char *tgoldtable; + char *tgnewtable; } Trigger; @@ -662,9 +671,38 @@ typedef struct Trigger + + tg_oldtable + + + A pointer to a structure of type Tuplestorestate + containing zero or more rows in the format specified by + tg_relation, or a NULL pointer + if there is no OLD TABLE transition relation. + + + + + + tg_newtable + + + A pointer to a structure of type Tuplestorestate + containing zero or more rows in the format specified by + tg_relation, or a NULL pointer + if there is no NEW TABLE transition relation. + + + + + + To allow queries issued through SPI to reference transition tables, see + . + + A trigger function must return either a HeapTuple pointer or a NULL pointer diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index 2f07a444b4..3a89ccd913 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -1257,6 +1257,9 @@ SPI_cursor_open_internal(const char *name, SPIPlanPtr plan, errdetail("Scrollable cursors must be READ ONLY."))); } + /* Make current query environment available to portal at execution time. */ + portal->queryEnv = _SPI_current->queryEnv; + /* * If told to be read-only, or in parallel mode, verify that this query is * in fact read-only. This can't be done earlier because we need to look @@ -2716,3 +2719,52 @@ SPI_unregister_relation(const char *name) return res; } + +/* + * Register the transient relations from 'tdata' using this SPI connection. + * This should be called by PL implementations' trigger handlers after + * connecting, in order to make transition tables visible to any queries run + * in this connection. + */ +int +SPI_register_trigger_data(TriggerData *tdata) +{ + if (tdata == NULL) + return SPI_ERROR_ARGUMENT; + + if (tdata->tg_newtable) + { + EphemeralNamedRelation enr = + palloc(sizeof(EphemeralNamedRelationData)); + int rc; + + enr->md.name = tdata->tg_trigger->tgnewtable; + enr->md.reliddesc = tdata->tg_relation->rd_id; + enr->md.tupdesc = NULL; + enr->md.enrtype = ENR_NAMED_TUPLESTORE; + enr->md.enrtuples = tuplestore_tuple_count(tdata->tg_newtable); + enr->reldata = tdata->tg_newtable; + rc = SPI_register_relation(enr); + if (rc != SPI_OK_REL_REGISTER) + return rc; + } + + if (tdata->tg_oldtable) + { + EphemeralNamedRelation enr = + palloc(sizeof(EphemeralNamedRelationData)); + int rc; + + enr->md.name = tdata->tg_trigger->tgoldtable; + enr->md.reliddesc = tdata->tg_relation->rd_id; + enr->md.tupdesc = NULL; + enr->md.enrtype = ENR_NAMED_TUPLESTORE; + enr->md.enrtuples = tuplestore_tuple_count(tdata->tg_oldtable); + enr->reldata = tdata->tg_oldtable; + rc = SPI_register_relation(enr); + if (rc != SPI_OK_REL_REGISTER) + return rc; + } + + return SPI_OK_TD_REGISTER; +} diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h index e2e8bb9553..94a805d477 100644 --- a/src/include/executor/spi.h +++ b/src/include/executor/spi.h @@ -13,6 +13,7 @@ #ifndef SPI_H #define SPI_H +#include "commands/trigger.h" #include "lib/ilist.h" #include "nodes/parsenodes.h" #include "utils/portal.h" @@ -62,6 +63,7 @@ typedef struct _SPI_plan *SPIPlanPtr; #define SPI_OK_REWRITTEN 14 #define SPI_OK_REL_REGISTER 15 #define SPI_OK_REL_UNREGISTER 16 +#define SPI_OK_TD_REGISTER 17 /* These used to be functions, now just no-ops for backwards compatibility */ #define SPI_push() ((void) 0) @@ -152,6 +154,7 @@ extern void SPI_cursor_close(Portal portal); extern int SPI_register_relation(EphemeralNamedRelation enr); extern int SPI_unregister_relation(const char *name); +extern int SPI_register_trigger_data(TriggerData *tdata); extern void AtEOXact_SPI(bool isCommit); extern void AtEOSubXact_SPI(bool isCommit, SubTransactionId mySubid); diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.out index 5e3860ef97..28011cd9f6 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 00979cba74..1de770b250 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 a375b401ea..624193b9d0 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} . " "); diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 43da986fc0..8d7c7caa1c 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -689,46 +689,9 @@ plpgsql_exec_trigger(PLpgSQL_function *func, else elog(ERROR, "unrecognized trigger action: not INSERT, DELETE, or UPDATE"); - /* - * Capture the NEW and OLD transition TABLE tuplestores (if specified for - * this trigger). - */ - if (trigdata->tg_newtable || trigdata->tg_oldtable) - { - estate.queryEnv = create_queryEnv(); - if (trigdata->tg_newtable) - { - EphemeralNamedRelation enr = - palloc(sizeof(EphemeralNamedRelationData)); - int rc PG_USED_FOR_ASSERTS_ONLY; - - enr->md.name = trigdata->tg_trigger->tgnewtable; - enr->md.reliddesc = RelationGetRelid(trigdata->tg_relation); - enr->md.tupdesc = NULL; - enr->md.enrtype = ENR_NAMED_TUPLESTORE; - enr->md.enrtuples = tuplestore_tuple_count(trigdata->tg_newtable); - enr->reldata = trigdata->tg_newtable; - register_ENR(estate.queryEnv, enr); - rc = SPI_register_relation(enr); - Assert(rc >= 0); - } - if (trigdata->tg_oldtable) - { - EphemeralNamedRelation enr = - palloc(sizeof(EphemeralNamedRelationData)); - int rc PG_USED_FOR_ASSERTS_ONLY; - - enr->md.name = trigdata->tg_trigger->tgoldtable; - enr->md.reliddesc = RelationGetRelid(trigdata->tg_relation); - enr->md.tupdesc = NULL; - enr->md.enrtype = ENR_NAMED_TUPLESTORE; - enr->md.enrtuples = tuplestore_tuple_count(trigdata->tg_oldtable); - enr->reldata = trigdata->tg_oldtable; - register_ENR(estate.queryEnv, enr); - rc = SPI_register_relation(enr); - Assert(rc >= 0); - } - } + /* Make transition tables visible to this SPI connection */ + rc = SPI_register_trigger_data(trigdata); + Assert(rc >= 0); /* * Assign the special tg_ variables @@ -3483,9 +3446,6 @@ plpgsql_estate_setup(PLpgSQL_execstate *estate, estate->paramLI->paramMask = NULL; estate->params_dirty = false; - /* default tuplestore cache to "none" */ - estate->queryEnv = NULL; - /* set up for use of appropriate simple-expression EState and cast hash */ if (simple_eval_estate) { @@ -7373,9 +7333,6 @@ exec_dynquery_with_params(PLpgSQL_execstate *estate, /* Release transient data */ MemoryContextReset(stmt_mcontext); - /* Make sure the portal knows about any named tuplestores. */ - portal->queryEnv = estate->queryEnv; - return portal; } diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 43a62ef34e..abb19035b6 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -20,7 +20,6 @@ #include "commands/event_trigger.h" #include "commands/trigger.h" #include "executor/spi.h" -#include "utils/queryenvironment.h" /********************************************************************** * Definitions @@ -911,9 +910,6 @@ typedef struct PLpgSQL_execstate ParamListInfo paramLI; bool params_dirty; /* T if any resettable datum has been passed */ - /* custom environment for parsing/execution of query for this context */ - QueryEnvironment *queryEnv; - /* EState to use for "simple" expression evaluation */ EState *simple_eval_estate; diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out index 4148963f3a..d7ab8ac6b8 100644 --- a/src/pl/plpython/expected/plpython_trigger.out +++ b/src/pl/plpython/expected/plpython_trigger.out @@ -503,3 +503,24 @@ SELECT * FROM b; 1234 (1 row) +-- 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 plpythonu AS +$$ + rv = plpy.execute("SELECT * FROM old_table") + assert(rv.nrows() == 1) + plpy.info("old: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + rv = plpy.execute("SELECT * FROM new_table") + assert(rv.nrows() == 1) + plpy.info("new: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + return None +$$; +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(); diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.c index 697a0e1cc0..7ccd2c8235 100644 --- a/src/pl/plpython/plpy_exec.c +++ b/src/pl/plpython/plpy_exec.c @@ -345,6 +345,11 @@ PLy_exec_trigger(FunctionCallInfo fcinfo, PLyProcedure *proc) PG_TRY(); { + int rc PG_USED_FOR_ASSERTS_ONLY; + + rc = SPI_register_trigger_data(tdata); + Assert(rc >= 0); + plargs = PLy_trigger_build_args(fcinfo, proc, &rv); plrv = PLy_procedure_call(proc, "TD", plargs); diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql index a054fe729b..79c24b714b 100644 --- a/src/pl/plpython/sql/plpython_trigger.sql +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -406,3 +406,27 @@ SELECT * FROM a; DROP TABLE a; INSERT INTO b DEFAULT VALUES; SELECT * FROM b; + +-- 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 plpythonu AS +$$ + rv = plpy.execute("SELECT * FROM old_table") + assert(rv.nrows() == 1) + plpy.info("old: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + rv = plpy.execute("SELECT * FROM new_table") + assert(rv.nrows() == 1) + plpy.info("new: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + return None +$$; + +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(); diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.out index 7300b315d6..5f50f46887 100644 --- a/src/pl/tcl/expected/pltcl_queries.out +++ b/src/pl/tcl/expected/pltcl_queries.out @@ -660,3 +660,24 @@ select tcl_eval($$ (1 row) +-- test transition table visibility +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 pltcl as +$$ + spi_exec -array C "SELECT id, name FROM old_table" { + elog INFO "old: $C(id) -> $C(name)" + } + spi_exec -array C "SELECT id, name FROM new_table" { + elog INFO "new: $C(id) -> $C(name)" + } + return OK +$$; +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(); diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c index b8fcf0673d..5a48e5d175 100644 --- a/src/pl/tcl/pltcl.c +++ b/src/pl/tcl/pltcl.c @@ -1039,6 +1039,7 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state, const char *result; int result_Objc; Tcl_Obj **result_Objv; + int rc PG_USED_FOR_ASSERTS_ONLY; call_state->trigdata = trigdata; @@ -1046,6 +1047,10 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state, if (SPI_connect() != SPI_OK_CONNECT) elog(ERROR, "could not connect to SPI manager"); + /* Make transition tables visible to this SPI connection */ + rc = SPI_register_trigger_data(trigdata); + Assert(rc >= 0); + /* Find or compile the function */ prodesc = compile_pltcl_function(fcinfo->flinfo->fn_oid, RelationGetRelid(trigdata->tg_relation), diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sql index 29ed616cd0..dabd8cd35f 100644 --- a/src/pl/tcl/sql/pltcl_queries.sql +++ b/src/pl/tcl/sql/pltcl_queries.sql @@ -216,3 +216,23 @@ select tcl_eval($$ after 100 {set ::tcl_vwait 1} vwait ::tcl_vwait unset -nocomplain ::tcl_vwait$$); + +-- test transition table visibility +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 pltcl as +$$ + spi_exec -array C "SELECT id, name FROM old_table" { + elog INFO "old: $C(id) -> $C(name)" + } + spi_exec -array C "SELECT id, name FROM new_table" { + elog INFO "new: $C(id) -> $C(name)" + } + return OK +$$; +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(); -- 2.40.0