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

Commitb58fd4a

Browse files
committed
Add a "subtransaction" command to PL/Tcl.
This allows rolling back the effects of some SPI commands withouthaving to fail the entire PL/Tcl function.Victor Wagner, reviewed by Pavel StehuleDiscussion:https://postgr.es/m/20170108205750.2dab04a1@wagner.wagner.home
1 parentf9dfa5c commitb58fd4a

File tree

5 files changed

+398
-7
lines changed

5 files changed

+398
-7
lines changed

‎doc/src/sgml/pltcl.sgml

Lines changed: 106 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -476,6 +476,20 @@ $$ LANGUAGE pltcl;
476476
</listitem>
477477
</varlistentry>
478478

479+
<varlistentry>
480+
<term><function>subtransaction</function> <replaceable>command</replaceable></term>
481+
<listitem>
482+
<para>
483+
The Tcl script contained in <replaceable>command</replaceable> is
484+
executed within a SQL subtransaction. If the script returns an
485+
error, that entire subtransaction is rolled back before returning the
486+
error out to the surrounding Tcl code.
487+
See <xref linkend="pltcl-subtransactions"> for more details and an
488+
example.
489+
</para>
490+
</listitem>
491+
</varlistentry>
492+
479493
<varlistentry>
480494
<term><function>quote</> <replaceable>string</replaceable></term>
481495
<listitem>
@@ -844,18 +858,22 @@ CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start EXECUTE PROCEDURE tclsnit
844858
either by executing some invalid operation or by generating an error
845859
using the Tcl <function>error</function> command or
846860
PL/Tcl's <function>elog</function> command. Such errors can be caught
847-
within Tcl using the Tcl <function>catch</function> command. If they
848-
are not caught but are allowed to propagate out to the top level of
849-
execution of the PL/Tcl function, they turn into database errors.
861+
within Tcl using the Tcl <function>catch</function> command. If an
862+
error is not caught but is allowed to propagate out to the top level of
863+
execution of the PL/Tcl function, it is reported as a SQL error in the
864+
function's calling query.
850865
</para>
851866

852867
<para>
853-
Conversely,database errors that occur within PL/Tcl's
868+
Conversely,SQL errors that occur within PL/Tcl's
854869
<function>spi_exec</function>, <function>spi_prepare</function>,
855870
and <function>spi_execp</function> commands are reported as Tcl errors,
856871
so they are catchable by Tcl's <function>catch</function> command.
857-
Again, if they propagate out to the top level without being caught,
858-
they turn back into database errors.
872+
(Each of these PL/Tcl commands runs its SQL operation in a
873+
subtransaction, which is rolled back on error, so that any
874+
partially-completed operation is automatically cleaned up.)
875+
Again, if an error propagates out to the top level without being caught,
876+
it turns back into a SQL error.
859877
</para>
860878

861879
<para>
@@ -902,6 +920,88 @@ if {[catch { spi_exec $sql_command }]} {
902920
</para>
903921
</sect1>
904922

923+
<sect1 id="pltcl-subtransactions">
924+
<title>Explicit Subtransactions in PL/Tcl</title>
925+
926+
<indexterm>
927+
<primary>subtransactions</primary>
928+
<secondary>in PL/Tcl</secondary>
929+
</indexterm>
930+
931+
<para>
932+
Recovering from errors caused by database access as described in
933+
<xref linkend="pltcl-error-handling"> can lead to an undesirable
934+
situation where some operations succeed before one of them fails,
935+
and after recovering from that error the data is left in an
936+
inconsistent state. PL/Tcl offers a solution to this problem in
937+
the form of explicit subtransactions.
938+
</para>
939+
940+
<para>
941+
Consider a function that implements a transfer between two accounts:
942+
<programlisting>
943+
CREATE FUNCTION transfer_funds() RETURNS void AS $$
944+
if [catch {
945+
spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
946+
spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
947+
} errormsg] {
948+
set result [format "error transferring funds: %s" $errormsg]
949+
} else {
950+
set result "funds transferred successfully"
951+
}
952+
spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
953+
$$ LANGUAGE pltcl;
954+
</programlisting>
955+
If the second <command>UPDATE</command> statement results in an
956+
exception being raised, this function will log the failure, but
957+
the result of the first <command>UPDATE</command> will
958+
nevertheless be committed. In other words, the funds will be
959+
withdrawn from Joe's account, but will not be transferred to
960+
Mary's account. This happens because each <function>spi_exec</function>
961+
is a separate subtransaction, and only one of those subtransactions
962+
got rolled back.
963+
</para>
964+
965+
<para>
966+
To handle such cases, you can wrap multiple database operations in an
967+
explicit subtransaction, which will succeed or roll back as a whole.
968+
PL/Tcl provides a <function>subtransaction</function> command to manage
969+
this. We can rewrite our function as:
970+
<programlisting>
971+
CREATE FUNCTION transfer_funds2() RETURNS void AS $$
972+
if [catch {
973+
subtransaction {
974+
spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
975+
spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
976+
}
977+
} errormsg] {
978+
set result [format "error transferring funds: %s" $errormsg]
979+
} else {
980+
set result "funds transferred successfully"
981+
}
982+
spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
983+
$$ LANGUAGE pltcl;
984+
</programlisting>
985+
Note that use of <function>catch</function> is still required for this
986+
purpose. Otherwise the error would propagate to the top level of the
987+
function, preventing the desired insertion into
988+
the <structname>operations</structname> table.
989+
The <function>subtransaction</function> command does not trap errors, it
990+
only assures that all database operations executed inside its scope will
991+
be rolled back together when an error is reported.
992+
</para>
993+
994+
<para>
995+
A rollback of an explicit subtransaction occurs on any error reported
996+
by the contained Tcl code, not only errors originating from database
997+
access. Thus a regular Tcl exception raised inside
998+
a <function>subtransaction</function> command will also cause the
999+
subtransaction to be rolled back. However, non-error exits out of the
1000+
contained Tcl code (for instance, due to <function>return</function>) do
1001+
not cause a rollback.
1002+
</para>
1003+
</sect1>
1004+
9051005
<sect1 id="pltcl-config">
9061006
<title>PL/Tcl Configuration</title>
9071007

‎src/pl/tcl/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ DATA = pltcl.control pltcl--1.0.sql pltcl--unpackaged--1.0.sql \
2828
pltclu.control pltclu--1.0.sql pltclu--unpackaged--1.0.sql
2929

3030
REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-extension=pltcl
31-
REGRESS = pltcl_setup pltcl_queries pltcl_start_proc pltcl_unicode
31+
REGRESS = pltcl_setup pltcl_queries pltcl_start_procpltcl_subxactpltcl_unicode
3232

3333
# Tcl on win32 ships with import libraries only for Microsoft Visual C++,
3434
# which are not compatible with mingw gcc. Therefore we need to build a

‎src/pl/tcl/expected/pltcl_subxact.out

Lines changed: 143 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,143 @@
1+
--
2+
-- Test explicit subtransactions
3+
--
4+
CREATE TABLE subtransaction_tbl (
5+
i integer
6+
);
7+
--
8+
-- We use this wrapper to catch errors and return errormsg only,
9+
-- because values of $::errorinfo variable contain procedure name which
10+
-- includes OID, so it's not stable
11+
--
12+
CREATE FUNCTION pltcl_wrapper(statement text) RETURNS text
13+
AS $$
14+
if [catch {spi_exec $1} msg] {
15+
return "ERROR: $msg"
16+
} else {
17+
return "SUCCESS: $msg"
18+
}
19+
$$ LANGUAGE pltcl;
20+
-- Test subtransaction successfully committed
21+
CREATE FUNCTION subtransaction_ctx_success() RETURNS void
22+
AS $$
23+
spi_exec "INSERT INTO subtransaction_tbl VALUES(1)"
24+
subtransaction {
25+
spi_exec "INSERT INTO subtransaction_tbl VALUES(2)"
26+
}
27+
$$ LANGUAGE pltcl;
28+
BEGIN;
29+
INSERT INTO subtransaction_tbl VALUES(0);
30+
SELECT subtransaction_ctx_success();
31+
subtransaction_ctx_success
32+
----------------------------
33+
34+
(1 row)
35+
36+
COMMIT;
37+
SELECT * FROM subtransaction_tbl;
38+
i
39+
---
40+
0
41+
1
42+
2
43+
(3 rows)
44+
45+
TRUNCATE subtransaction_tbl;
46+
-- Test subtransaction rollback
47+
CREATE FUNCTION subtransaction_ctx_test(what_error text = NULL) RETURNS void
48+
AS $$
49+
spi_exec "INSERT INTO subtransaction_tbl VALUES (1)"
50+
subtransaction {
51+
spi_exec "INSERT INTO subtransaction_tbl VALUES (2)"
52+
if {$1 == "SPI"} {
53+
spi_exec "INSERT INTO subtransaction_tbl VALUES ('oops')"
54+
} elseif { $1 == "Tcl"} {
55+
elog ERROR "Tcl error"
56+
}
57+
}
58+
$$ LANGUAGE pltcl;
59+
SELECT pltcl_wrapper('SELECT subtransaction_ctx_test()');
60+
pltcl_wrapper
61+
---------------
62+
SUCCESS: 1
63+
(1 row)
64+
65+
SELECT * FROM subtransaction_tbl;
66+
i
67+
---
68+
1
69+
2
70+
(2 rows)
71+
72+
TRUNCATE subtransaction_tbl;
73+
SELECT pltcl_wrapper('SELECT subtransaction_ctx_test(''SPI'')');
74+
pltcl_wrapper
75+
-------------------------------------------------
76+
ERROR: invalid input syntax for integer: "oops"
77+
(1 row)
78+
79+
SELECT * FROM subtransaction_tbl;
80+
i
81+
---
82+
(0 rows)
83+
84+
TRUNCATE subtransaction_tbl;
85+
SELECT pltcl_wrapper('SELECT subtransaction_ctx_test(''Tcl'')');
86+
pltcl_wrapper
87+
------------------
88+
ERROR: Tcl error
89+
(1 row)
90+
91+
SELECT * FROM subtransaction_tbl;
92+
i
93+
---
94+
(0 rows)
95+
96+
TRUNCATE subtransaction_tbl;
97+
-- Nested subtransactions
98+
CREATE FUNCTION subtransaction_nested_test(swallow boolean = 'f') RETURNS text
99+
AS $$
100+
spi_exec "INSERT INTO subtransaction_tbl VALUES (1)"
101+
subtransaction {
102+
spi_exec "INSERT INTO subtransaction_tbl VALUES (2)"
103+
if [catch {
104+
subtransaction {
105+
spi_exec "INSERT INTO subtransaction_tbl VALUES (3)"
106+
spi_exec "error"
107+
}
108+
} errormsg] {
109+
if {$1 != "t"} {
110+
error $errormsg $::errorInfo $::errorCode
111+
}
112+
elog NOTICE "Swallowed $errormsg"
113+
}
114+
}
115+
return "ok"
116+
$$ LANGUAGE pltcl;
117+
SELECT pltcl_wrapper('SELECT subtransaction_nested_test()');
118+
pltcl_wrapper
119+
----------------------------------------
120+
ERROR: syntax error at or near "error"
121+
(1 row)
122+
123+
SELECT * FROM subtransaction_tbl;
124+
i
125+
---
126+
(0 rows)
127+
128+
TRUNCATE subtransaction_tbl;
129+
SELECT pltcl_wrapper('SELECT subtransaction_nested_test(''t'')');
130+
NOTICE: Swallowed syntax error at or near "error"
131+
pltcl_wrapper
132+
---------------
133+
SUCCESS: 1
134+
(1 row)
135+
136+
SELECT * FROM subtransaction_tbl;
137+
i
138+
---
139+
1
140+
2
141+
(2 rows)
142+
143+
TRUNCATE subtransaction_tbl;

‎src/pl/tcl/pltcl.c

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -306,6 +306,8 @@ static int pltcl_SPI_execute_plan(ClientData cdata, Tcl_Interp *interp,
306306
intobjc,Tcl_Obj*constobjv[]);
307307
staticintpltcl_SPI_lastoid(ClientDatacdata,Tcl_Interp*interp,
308308
intobjc,Tcl_Obj*constobjv[]);
309+
staticintpltcl_subtransaction(ClientDatacdata,Tcl_Interp*interp,
310+
intobjc,Tcl_Obj*constobjv[]);
309311

310312
staticvoidpltcl_subtrans_begin(MemoryContextoldcontext,
311313
ResourceOwneroldowner);
@@ -516,6 +518,8 @@ pltcl_init_interp(pltcl_interp_desc *interp_desc, Oid prolang, bool pltrusted)
516518
pltcl_SPI_execute_plan,NULL,NULL);
517519
Tcl_CreateObjCommand(interp,"spi_lastoid",
518520
pltcl_SPI_lastoid,NULL,NULL);
521+
Tcl_CreateObjCommand(interp,"subtransaction",
522+
pltcl_subtransaction,NULL,NULL);
519523

520524
/************************************************************
521525
* Call the appropriate start_proc, if there is one.
@@ -2850,6 +2854,55 @@ pltcl_SPI_lastoid(ClientData cdata, Tcl_Interp *interp,
28502854
}
28512855

28522856

2857+
/**********************************************************************
2858+
* pltcl_subtransaction()- Execute some Tcl code in a subtransaction
2859+
*
2860+
* The subtransaction is aborted if the Tcl code fragment returns TCL_ERROR,
2861+
* otherwise it's subcommitted.
2862+
**********************************************************************/
2863+
staticint
2864+
pltcl_subtransaction(ClientDatacdata,Tcl_Interp*interp,
2865+
intobjc,Tcl_Obj*constobjv[])
2866+
{
2867+
MemoryContextoldcontext=CurrentMemoryContext;
2868+
ResourceOwneroldowner=CurrentResourceOwner;
2869+
intretcode;
2870+
2871+
if (objc!=2)
2872+
{
2873+
Tcl_WrongNumArgs(interp,1,objv,"command");
2874+
returnTCL_ERROR;
2875+
}
2876+
2877+
/*
2878+
* Note: we don't use pltcl_subtrans_begin and friends here because we
2879+
* don't want the error handling in pltcl_subtrans_abort. But otherwise
2880+
* the processing should be about the same as in those functions.
2881+
*/
2882+
BeginInternalSubTransaction(NULL);
2883+
MemoryContextSwitchTo(oldcontext);
2884+
2885+
retcode=Tcl_EvalObjEx(interp,objv[1],0);
2886+
2887+
if (retcode==TCL_ERROR)
2888+
{
2889+
/* Rollback the subtransaction */
2890+
RollbackAndReleaseCurrentSubTransaction();
2891+
}
2892+
else
2893+
{
2894+
/* Commit the subtransaction */
2895+
ReleaseCurrentSubTransaction();
2896+
}
2897+
2898+
/* In either case, restore previous memory context and resource owner */
2899+
MemoryContextSwitchTo(oldcontext);
2900+
CurrentResourceOwner=oldowner;
2901+
2902+
returnretcode;
2903+
}
2904+
2905+
28532906
/**********************************************************************
28542907
* pltcl_set_tuple_values() - Set variables for all attributes
28552908
* of a given tuple

0 commit comments

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