Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5ebeb57

Browse files
committed
Follow-on cleanup for the transition table patch.
Commit5970271 added transition table support to PL/pgsql so thatSQL queries in trigger functions could access those transienttables. In order to provide the same level of support for PL/perl,PL/python and PL/tcl, refactor the relevant code into a newfunction SPI_register_trigger_data. Call the new function in thetrigger handler of all four PLs, and document it as a public SPIfunction so that authors of out-of-tree PLs can do the same.Also get rid of a second QueryEnvironment object that wasmaintained by PL/pgsql. That was previously used to deal withcursors, but the same approach wasn't appropriate for PLs that areless tangled up with core code. Instead, have SPI_cursor_openinstall the connection's current QueryEnvironment, as alreadyhappens for SPI_execute_plan.While in the docs, remove the note that transition tables were onlysupported in C and PL/pgSQL triggers, and correct some ommissions.Thomas Munro with some work by Kevin Grittner (mostly docs)
1 parent9a32150 commit5ebeb57

File tree

16 files changed

+398
-63
lines changed

16 files changed

+398
-63
lines changed

‎doc/src/sgml/ref/create_trigger.sgml

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,11 @@ PostgreSQL documentation
88
<primary>CREATE TRIGGER</primary>
99
</indexterm>
1010

11+
<indexterm>
12+
<primary>transition tables</primary>
13+
<seealso>ephemeral named relation</seealso>
14+
</indexterm>
15+
1116
<refmeta>
1217
<refentrytitle>CREATE TRIGGER</refentrytitle>
1318
<manvolnum>7</manvolnum>
@@ -322,11 +327,6 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
322327
<para>
323328
The (unqualified) name to be used within the trigger for this relation.
324329
</para>
325-
<note>
326-
<para>
327-
So far only triggers written in C or PL/pgSQL support this.
328-
</para>
329-
</note>
330330
</listitem>
331331
</varlistentry>
332332

‎doc/src/sgml/spi.sgml

Lines changed: 125 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2644,6 +2644,11 @@ SPIPlanPtr SPI_saveplan(SPIPlanPtr <parameter>plan</parameter>)
26442644
<refentry id="spi-spi-register-relation">
26452645
<indexterm><primary>SPI_register_relation</primary></indexterm>
26462646

2647+
<indexterm>
2648+
<primary>ephemeral named relation</primary>
2649+
<secondary>registering with SPI</secondary>
2650+
</indexterm>
2651+
26472652
<refmeta>
26482653
<refentrytitle>SPI_register_relation</refentrytitle>
26492654
<manvolnum>3</manvolnum>
@@ -2746,6 +2751,11 @@ int SPI_register_relation(EphemeralNamedRelation <parameter>enr</parameter>)
27462751
<refentry id="spi-spi-unregister-relation">
27472752
<indexterm><primary>SPI_unregister_relation</primary></indexterm>
27482753

2754+
<indexterm>
2755+
<primary>ephemeral named relation</primary>
2756+
<secondary>unregistering from SPI</secondary>
2757+
</indexterm>
2758+
27492759
<refmeta>
27502760
<refentrytitle>SPI_unregister_relation</refentrytitle>
27512761
<manvolnum>3</manvolnum>
@@ -2843,6 +2853,121 @@ int SPI_unregister_relation(const char * <parameter>name</parameter>)
28432853

28442854
<!-- *********************************************** -->
28452855

2856+
<refentry id="spi-spi-register-trigger-data">
2857+
<indexterm><primary>SPI_register_trigger_data</primary></indexterm>
2858+
2859+
<indexterm>
2860+
<primary>ephemeral named relation</primary>
2861+
<secondary>registering with SPI</secondary>
2862+
</indexterm>
2863+
2864+
<indexterm>
2865+
<primary>transition tables</primary>
2866+
<secondary>implementation in PLs</secondary>
2867+
</indexterm>
2868+
2869+
<refmeta>
2870+
<refentrytitle>SPI_register_trigger_data</refentrytitle>
2871+
<manvolnum>3</manvolnum>
2872+
</refmeta>
2873+
2874+
<refnamediv>
2875+
<refname>SPI_register_trigger_data</refname>
2876+
<refpurpose>make ephemeral trigger data available in SPI queries</refpurpose>
2877+
</refnamediv>
2878+
2879+
<refsynopsisdiv>
2880+
<synopsis>
2881+
int SPI_register_trigger_data(TriggerData *<parameter>tdata</parameter>)
2882+
</synopsis>
2883+
</refsynopsisdiv>
2884+
2885+
<refsect1>
2886+
<title>Description</title>
2887+
2888+
<para>
2889+
<function>SPI_register_trigger_data</function> makes any ephemeral
2890+
relations captured by a trigger available to queries planned and executed
2891+
through the current SPI connection. Currently, this means the transition
2892+
tables captured by an <literal>AFTER</literal> trigger defined with a
2893+
<literal>REFERENCING OLD/NEW TABLE AS</literal> ... clause. This function
2894+
should be called by a PL trigger handler function after connecting.
2895+
</para>
2896+
</refsect1>
2897+
2898+
<refsect1>
2899+
<title>Arguments</title>
2900+
2901+
<variablelist>
2902+
<varlistentry>
2903+
<term><literal>TriggerData *<parameter>tdata</parameter></literal></term>
2904+
<listitem>
2905+
<para>
2906+
the <structname>TriggerData</structname> object passed to a trigger
2907+
handler function as <literal>fcinfo->context</literal>
2908+
</para>
2909+
</listitem>
2910+
</varlistentry>
2911+
</variablelist>
2912+
</refsect1>
2913+
2914+
<refsect1>
2915+
<title>Return Value</title>
2916+
2917+
<para>
2918+
If the execution of the command was successful then the following
2919+
(nonnegative) value will be returned:
2920+
2921+
<variablelist>
2922+
<varlistentry>
2923+
<term><symbol>SPI_OK_TD_REGISTER</symbol></term>
2924+
<listitem>
2925+
<para>
2926+
if the captured trigger data (if any) has been successfully registered
2927+
</para>
2928+
</listitem>
2929+
</varlistentry>
2930+
</variablelist>
2931+
</para>
2932+
2933+
<para>
2934+
On error, one of the following negative values is returned:
2935+
2936+
<variablelist>
2937+
<varlistentry>
2938+
<term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
2939+
<listitem>
2940+
<para>
2941+
if <parameter>tdata</parameter> is <symbol>NULL</symbol>
2942+
</para>
2943+
</listitem>
2944+
</varlistentry>
2945+
2946+
<varlistentry>
2947+
<term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
2948+
<listitem>
2949+
<para>
2950+
if called from an unconnected procedure
2951+
</para>
2952+
</listitem>
2953+
</varlistentry>
2954+
2955+
<varlistentry>
2956+
<term><symbol>SPI_ERROR_REL_DUPLICATE</symbol></term>
2957+
<listitem>
2958+
<para>
2959+
if the name of any trigger data transient relation is already
2960+
registered for this connection
2961+
</para>
2962+
</listitem>
2963+
</varlistentry>
2964+
</variablelist>
2965+
</para>
2966+
</refsect1>
2967+
</refentry>
2968+
2969+
<!-- *********************************************** -->
2970+
28462971
</sect1>
28472972

28482973
<sect1 id="spi-interface-support">

‎doc/src/sgml/trigger.sgml

Lines changed: 46 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -395,6 +395,11 @@
395395
<secondary>in C</secondary>
396396
</indexterm>
397397

398+
<indexterm>
399+
<primary>transition tables</primary>
400+
<secondary>referencing from C trigger</secondary>
401+
</indexterm>
402+
398403
<para>
399404
This section describes the low-level details of the interface to a
400405
trigger function. This information is only needed when writing
@@ -438,14 +443,16 @@ CALLED_AS_TRIGGER(fcinfo)
438443
<programlisting>
439444
typedef struct TriggerData
440445
{
441-
NodeTag type;
442-
TriggerEvent tg_event;
443-
Relation tg_relation;
444-
HeapTuple tg_trigtuple;
445-
HeapTuple tg_newtuple;
446-
Trigger *tg_trigger;
447-
Buffer tg_trigtuplebuf;
448-
Buffer tg_newtuplebuf;
446+
NodeTag type;
447+
TriggerEvent tg_event;
448+
Relation tg_relation;
449+
HeapTuple tg_trigtuple;
450+
HeapTuple tg_newtuple;
451+
Trigger *tg_trigger;
452+
Buffer tg_trigtuplebuf;
453+
Buffer tg_newtuplebuf;
454+
Tuplestorestate *tg_oldtable;
455+
Tuplestorestate *tg_newtable;
449456
} TriggerData;
450457
</programlisting>
451458

@@ -629,6 +636,8 @@ typedef struct Trigger
629636
int16 *tgattr;
630637
char **tgargs;
631638
char *tgqual;
639+
char *tgoldtable;
640+
char *tgnewtable;
632641
} Trigger;
633642
</programlisting>
634643

@@ -662,9 +671,38 @@ typedef struct Trigger
662671
</listitem>
663672
</varlistentry>
664673

674+
<varlistentry>
675+
<term><structfield>tg_oldtable</></term>
676+
<listitem>
677+
<para>
678+
A pointer to a structure of type <structname>Tuplestorestate</structname>
679+
containing zero or more rows in the format specified by
680+
<structfield>tg_relation</structfield>, or a <symbol>NULL</> pointer
681+
if there is no <literal>OLD TABLE</literal> transition relation.
682+
</para>
683+
</listitem>
684+
</varlistentry>
685+
686+
<varlistentry>
687+
<term><structfield>tg_newtable</></term>
688+
<listitem>
689+
<para>
690+
A pointer to a structure of type <structname>Tuplestorestate</structname>
691+
containing zero or more rows in the format specified by
692+
<structfield>tg_relation</structfield>, or a <symbol>NULL</> pointer
693+
if there is no <literal>NEW TABLE</literal> transition relation.
694+
</para>
695+
</listitem>
696+
</varlistentry>
697+
665698
</variablelist>
666699
</para>
667700

701+
<para>
702+
To allow queries issued through SPI to reference transition tables, see
703+
<xref linkend="spi-spi-register-trigger-data">.
704+
</para>
705+
668706
<para>
669707
A trigger function must return either a
670708
<structname>HeapTuple</> pointer or a <symbol>NULL</> pointer

‎src/backend/executor/spi.c

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1257,6 +1257,9 @@ SPI_cursor_open_internal(const char *name, SPIPlanPtr plan,
12571257
errdetail("Scrollable cursors must be READ ONLY.")));
12581258
}
12591259

1260+
/* Make current query environment available to portal at execution time. */
1261+
portal->queryEnv=_SPI_current->queryEnv;
1262+
12601263
/*
12611264
* If told to be read-only, or in parallel mode, verify that this query is
12621265
* 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)
27162719

27172720
returnres;
27182721
}
2722+
2723+
/*
2724+
* Register the transient relations from 'tdata' using this SPI connection.
2725+
* This should be called by PL implementations' trigger handlers after
2726+
* connecting, in order to make transition tables visible to any queries run
2727+
* in this connection.
2728+
*/
2729+
int
2730+
SPI_register_trigger_data(TriggerData*tdata)
2731+
{
2732+
if (tdata==NULL)
2733+
returnSPI_ERROR_ARGUMENT;
2734+
2735+
if (tdata->tg_newtable)
2736+
{
2737+
EphemeralNamedRelationenr=
2738+
palloc(sizeof(EphemeralNamedRelationData));
2739+
intrc;
2740+
2741+
enr->md.name=tdata->tg_trigger->tgnewtable;
2742+
enr->md.reliddesc=tdata->tg_relation->rd_id;
2743+
enr->md.tupdesc=NULL;
2744+
enr->md.enrtype=ENR_NAMED_TUPLESTORE;
2745+
enr->md.enrtuples=tuplestore_tuple_count(tdata->tg_newtable);
2746+
enr->reldata=tdata->tg_newtable;
2747+
rc=SPI_register_relation(enr);
2748+
if (rc!=SPI_OK_REL_REGISTER)
2749+
returnrc;
2750+
}
2751+
2752+
if (tdata->tg_oldtable)
2753+
{
2754+
EphemeralNamedRelationenr=
2755+
palloc(sizeof(EphemeralNamedRelationData));
2756+
intrc;
2757+
2758+
enr->md.name=tdata->tg_trigger->tgoldtable;
2759+
enr->md.reliddesc=tdata->tg_relation->rd_id;
2760+
enr->md.tupdesc=NULL;
2761+
enr->md.enrtype=ENR_NAMED_TUPLESTORE;
2762+
enr->md.enrtuples=tuplestore_tuple_count(tdata->tg_oldtable);
2763+
enr->reldata=tdata->tg_oldtable;
2764+
rc=SPI_register_relation(enr);
2765+
if (rc!=SPI_OK_REL_REGISTER)
2766+
returnrc;
2767+
}
2768+
2769+
returnSPI_OK_TD_REGISTER;
2770+
}

‎src/include/executor/spi.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@
1313
#ifndefSPI_H
1414
#defineSPI_H
1515

16+
#include"commands/trigger.h"
1617
#include"lib/ilist.h"
1718
#include"nodes/parsenodes.h"
1819
#include"utils/portal.h"
@@ -62,6 +63,7 @@ typedef struct _SPI_plan *SPIPlanPtr;
6263
#defineSPI_OK_REWRITTEN14
6364
#defineSPI_OK_REL_REGISTER15
6465
#defineSPI_OK_REL_UNREGISTER16
66+
#defineSPI_OK_TD_REGISTER17
6567

6668
/* These used to be functions, now just no-ops for backwards compatibility */
6769
#defineSPI_push()((void) 0)
@@ -152,6 +154,7 @@ extern void SPI_cursor_close(Portal portal);
152154

153155
externintSPI_register_relation(EphemeralNamedRelationenr);
154156
externintSPI_unregister_relation(constchar*name);
157+
externintSPI_register_trigger_data(TriggerData*tdata);
155158

156159
externvoidAtEOXact_SPI(boolisCommit);
157160
externvoidAtEOSubXact_SPI(boolisCommit,SubTransactionIdmySubid);

‎src/pl/plperl/expected/plperl_trigger.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -241,6 +241,35 @@ $$ LANGUAGE plperl;
241241
SELECT direct_trigger();
242242
ERROR: trigger functions can only be called as triggers
243243
CONTEXT: compilation of PL/Perl function "direct_trigger"
244+
-- check that SQL run in trigger code can see transition tables
245+
CREATE TABLE transition_table_test (id int, name text);
246+
INSERT INTO transition_table_test VALUES (1, 'a');
247+
CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plperl AS
248+
$$
249+
my $cursor = spi_query("SELECT * FROM old_table");
250+
my $row = spi_fetchrow($cursor);
251+
defined($row) || die "expected a row";
252+
elog(INFO, "old: " . $row->{id} . " -> " . $row->{name});
253+
my $row = spi_fetchrow($cursor);
254+
!defined($row) || die "expected no more rows";
255+
256+
my $cursor = spi_query("SELECT * FROM new_table");
257+
my $row = spi_fetchrow($cursor);
258+
defined($row) || die "expected a row";
259+
elog(INFO, "new: " . $row->{id} . " -> " . $row->{name});
260+
my $row = spi_fetchrow($cursor);
261+
!defined($row) || die "expected no more rows";
262+
263+
return undef;
264+
$$;
265+
CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
266+
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
267+
FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f();
268+
UPDATE transition_table_test SET name = 'b';
269+
INFO: old: 1 -> a
270+
INFO: new: 1 -> b
271+
DROP TABLE transition_table_test;
272+
DROP FUNCTION transition_table_test_f();
244273
-- test plperl command triggers
245274
create or replace function perlsnitch() returns event_trigger language plperl as $$
246275
elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " ");

‎src/pl/plperl/plperl.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2442,11 +2442,18 @@ plperl_trigger_handler(PG_FUNCTION_ARGS)
24422442
SV*svTD;
24432443
HV*hvTD;
24442444
ErrorContextCallbackpl_error_context;
2445+
TriggerData*tdata;
2446+
intrcPG_USED_FOR_ASSERTS_ONLY;
24452447

24462448
/* Connect to SPI manager */
24472449
if (SPI_connect()!=SPI_OK_CONNECT)
24482450
elog(ERROR,"could not connect to SPI manager");
24492451

2452+
/* Make transition tables visible to this SPI connection */
2453+
tdata= (TriggerData*)fcinfo->context;
2454+
rc=SPI_register_trigger_data(tdata);
2455+
Assert(rc >=0);
2456+
24502457
/* Find or compile the function */
24512458
prodesc=compile_plperl_function(fcinfo->flinfo->fn_oid, true, false);
24522459
current_call_data->prodesc=prodesc;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp