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

Commitd92bc83

Browse files
committed
PL/pgSQL: Nested CALL with transactions
So far, a nested CALL or DO in PL/pgSQL would not establish a contextwhere transaction control statements were allowed. This fixes that byhandling CALL and DO specially in PL/pgSQL, passing the atomic/nonatomicexecution context through and doing the required management aroundtransaction boundaries.Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 parentc2d4eb1 commitd92bc83

File tree

13 files changed

+235
-59
lines changed

13 files changed

+235
-59
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3463,9 +3463,9 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
34633463
<title>Transaction Management</title>
34643464

34653465
<para>
3466-
In procedures invoked by the <command>CALL</command> command from the top
3467-
levelas well as in anonymous code blocks (<command>DO</command> command)
3468-
called from the top level,it is possible to end transactions using the
3466+
In procedures invoked by the <command>CALL</command> command
3467+
as well as in anonymous code blocks (<command>DO</command> command),
3468+
it is possible to end transactions using the
34693469
commands <command>COMMIT</command> and <command>ROLLBACK</command>. A new
34703470
transaction is started automatically after a transaction is ended using
34713471
these commands, so there is no separate <command>START
@@ -3495,6 +3495,20 @@ CALL transaction_test1();
34953495
</programlisting>
34963496
</para>
34973497

3498+
<para>
3499+
Transaction control is only possible in <command>CALL</command> or
3500+
<command>DO</command> invocations from the top level or nested
3501+
<command>CALL</command> or <command>DO</command> invocations without any
3502+
other intervening command. For example, if the call stack is
3503+
<command>CALL proc1()</command> &rarr; <command>CALL proc2()</command>
3504+
&rarr; <command>CALL proc3()</command>, then the second and third
3505+
procedures can perform transaction control actions. But if the call stack
3506+
is <command>CALL proc1()</command> &rarr; <command>SELECT
3507+
func2()</command> &rarr; <command>CALL proc3()</command>, then the last
3508+
procedure cannot do transaction control, because of the
3509+
<command>SELECT</command> in between.
3510+
</para>
3511+
34983512
<para>
34993513
A transaction cannot be ended inside a loop over a query result, nor
35003514
inside a block with exception handlers.

‎src/backend/executor/spi.c

Lines changed: 22 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -2041,8 +2041,11 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
20412041
*
20422042
* In the first two cases, we can just push the snap onto the stack once
20432043
* for the whole plan list.
2044+
*
2045+
* But if the plan has no_snapshots set to true, then don't manage
2046+
* snapshots at all. The caller should then take care of that.
20442047
*/
2045-
if (snapshot!=InvalidSnapshot)
2048+
if (snapshot!=InvalidSnapshot&& !plan->no_snapshots)
20462049
{
20472050
if (read_only)
20482051
{
@@ -2121,7 +2124,7 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
21212124
* In the default non-read-only case, get a new snapshot, replacing
21222125
* any that we pushed in a previous cycle.
21232126
*/
2124-
if (snapshot==InvalidSnapshot&& !read_only)
2127+
if (snapshot==InvalidSnapshot&& !read_only&& !plan->no_snapshots)
21252128
{
21262129
if (pushed_active_snap)
21272130
PopActiveSnapshot();
@@ -2172,7 +2175,7 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
21722175
* If not read-only mode, advance the command counter before each
21732176
* command and update the snapshot.
21742177
*/
2175-
if (!read_only)
2178+
if (!read_only&& !plan->no_snapshots)
21762179
{
21772180
CommandCounterIncrement();
21782181
UpdateActiveSnapshotCommandId();
@@ -2203,10 +2206,23 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
22032206
else
22042207
{
22052208
charcompletionTag[COMPLETION_TAG_BUFSIZE];
2209+
ProcessUtilityContextcontext;
2210+
2211+
/*
2212+
* If the SPI context is atomic, or we are asked to manage
2213+
* snapshots, then we are in an atomic execution context.
2214+
* Conversely, to propagate a nonatomic execution context, the
2215+
* caller must be in a nonatomic SPI context and manage
2216+
* snapshots itself.
2217+
*/
2218+
if (_SPI_current->atomic|| !plan->no_snapshots)
2219+
context=PROCESS_UTILITY_QUERY;
2220+
else
2221+
context=PROCESS_UTILITY_QUERY_NONATOMIC;
22062222

22072223
ProcessUtility(stmt,
22082224
plansource->query_string,
2209-
PROCESS_UTILITY_QUERY,
2225+
context,
22102226
paramLI,
22112227
_SPI_current->queryEnv,
22122228
dest,
@@ -2638,11 +2654,8 @@ _SPI_make_plan_non_temp(SPIPlanPtr plan)
26382654
oldcxt=MemoryContextSwitchTo(plancxt);
26392655

26402656
/* Copy the SPI_plan struct and subsidiary data into the new context */
2641-
newplan= (SPIPlanPtr)palloc(sizeof(_SPI_plan));
2657+
newplan= (SPIPlanPtr)palloc0(sizeof(_SPI_plan));
26422658
newplan->magic=_SPI_PLAN_MAGIC;
2643-
newplan->saved= false;
2644-
newplan->oneshot= false;
2645-
newplan->plancache_list=NIL;
26462659
newplan->plancxt=plancxt;
26472660
newplan->cursor_options=plan->cursor_options;
26482661
newplan->nargs=plan->nargs;
@@ -2705,11 +2718,8 @@ _SPI_save_plan(SPIPlanPtr plan)
27052718
oldcxt=MemoryContextSwitchTo(plancxt);
27062719

27072720
/* Copy the SPI plan into its own context */
2708-
newplan= (SPIPlanPtr)palloc(sizeof(_SPI_plan));
2721+
newplan= (SPIPlanPtr)palloc0(sizeof(_SPI_plan));
27092722
newplan->magic=_SPI_PLAN_MAGIC;
2710-
newplan->saved= false;
2711-
newplan->oneshot= false;
2712-
newplan->plancache_list=NIL;
27132723
newplan->plancxt=plancxt;
27142724
newplan->cursor_options=plan->cursor_options;
27152725
newplan->nargs=plan->nargs;

‎src/backend/tcop/utility.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -382,7 +382,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
382382
{
383383
Node*parsetree=pstmt->utilityStmt;
384384
boolisTopLevel= (context==PROCESS_UTILITY_TOPLEVEL);
385-
boolisAtomicContext= (context!=PROCESS_UTILITY_TOPLEVEL||IsTransactionBlock());
385+
boolisAtomicContext= (!(context==PROCESS_UTILITY_TOPLEVEL||context==PROCESS_UTILITY_QUERY_NONATOMIC)||IsTransactionBlock());
386386
ParseState*pstate;
387387

388388
check_xact_readonly(parsetree);

‎src/include/executor/spi_priv.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,7 @@ typedef struct _SPI_plan
8686
intmagic;/* should equal _SPI_PLAN_MAGIC */
8787
boolsaved;/* saved or unsaved plan? */
8888
booloneshot;/* one-shot plan? */
89+
boolno_snapshots;/* let the caller handle the snapshots */
8990
List*plancache_list;/* one CachedPlanSource per parsetree */
9091
MemoryContextplancxt;/* Context containing _SPI_plan and data */
9192
intcursor_options;/* Cursor options used for planning */

‎src/include/tcop/utility.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@ typedef enum
2020
{
2121
PROCESS_UTILITY_TOPLEVEL,/* toplevel interactive command */
2222
PROCESS_UTILITY_QUERY,/* a complete query, but not toplevel */
23+
PROCESS_UTILITY_QUERY_NONATOMIC,/* a complete query, nonatomic execution context */
2324
PROCESS_UTILITY_SUBCOMMAND/* a portion of a query */
2425
}ProcessUtilityContext;
2526

‎src/pl/plpgsql/src/expected/plpgsql_transaction.out

Lines changed: 67 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,10 @@
11
CREATE TABLE test1 (a int, b text);
2-
CREATE PROCEDURE transaction_test1()
2+
CREATE PROCEDURE transaction_test1(x int, y text)
33
LANGUAGE plpgsql
44
AS $$
55
BEGIN
6-
FOR i IN 0..9 LOOP
7-
INSERT INTO test1 (a) VALUES (i);
6+
FOR i IN 0..x LOOP
7+
INSERT INTO test1 (a, b) VALUES (i, y);
88
IF i % 2 = 0 THEN
99
COMMIT;
1010
ELSE
@@ -13,15 +13,15 @@ BEGIN
1313
END LOOP;
1414
END
1515
$$;
16-
CALL transaction_test1();
16+
CALL transaction_test1(9, 'foo');
1717
SELECT * FROM test1;
18-
a |b
19-
---+---
20-
0 |
21-
2 |
22-
4 |
23-
6 |
24-
8 |
18+
a | b
19+
---+-----
20+
0 |foo
21+
2 |foo
22+
4 |foo
23+
6 |foo
24+
8 |foo
2525
(5 rows)
2626

2727
TRUNCATE test1;
@@ -51,9 +51,9 @@ SELECT * FROM test1;
5151

5252
-- transaction commands not allowed when called in transaction block
5353
START TRANSACTION;
54-
CALL transaction_test1();
54+
CALL transaction_test1(9, 'error');
5555
ERROR: invalid transaction termination
56-
CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT
56+
CONTEXT: PL/pgSQL function transaction_test1(integer,text) line 6 at COMMIT
5757
COMMIT;
5858
START TRANSACTION;
5959
DO LANGUAGE plpgsql $$ BEGIN COMMIT; END $$;
@@ -90,14 +90,14 @@ CREATE FUNCTION transaction_test3() RETURNS int
9090
LANGUAGE plpgsql
9191
AS $$
9292
BEGIN
93-
CALL transaction_test1();
93+
CALL transaction_test1(9, 'error');
9494
RETURN 1;
9595
END;
9696
$$;
9797
SELECT transaction_test3();
9898
ERROR: invalid transaction termination
99-
CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT
100-
SQL statement "CALL transaction_test1()"
99+
CONTEXT: PL/pgSQL function transaction_test1(integer,text) line 6 at COMMIT
100+
SQL statement "CALL transaction_test1(9, 'error')"
101101
PL/pgSQL function transaction_test3() line 3 at CALL
102102
SELECT * FROM test1;
103103
a | b
@@ -130,6 +130,57 @@ $$;
130130
CALL transaction_test5();
131131
ERROR: invalid transaction termination
132132
CONTEXT: PL/pgSQL function transaction_test5() line 3 at COMMIT
133+
TRUNCATE test1;
134+
-- nested procedure calls
135+
CREATE PROCEDURE transaction_test6(c text)
136+
LANGUAGE plpgsql
137+
AS $$
138+
BEGIN
139+
CALL transaction_test1(9, c);
140+
END;
141+
$$;
142+
CALL transaction_test6('bar');
143+
SELECT * FROM test1;
144+
a | b
145+
---+-----
146+
0 | bar
147+
2 | bar
148+
4 | bar
149+
6 | bar
150+
8 | bar
151+
(5 rows)
152+
153+
TRUNCATE test1;
154+
CREATE PROCEDURE transaction_test7()
155+
LANGUAGE plpgsql
156+
AS $$
157+
BEGIN
158+
DO 'BEGIN CALL transaction_test1(9, $x$baz$x$); END;';
159+
END;
160+
$$;
161+
CALL transaction_test7();
162+
SELECT * FROM test1;
163+
a | b
164+
---+-----
165+
0 | baz
166+
2 | baz
167+
4 | baz
168+
6 | baz
169+
8 | baz
170+
(5 rows)
171+
172+
CREATE PROCEDURE transaction_test8()
173+
LANGUAGE plpgsql
174+
AS $$
175+
BEGIN
176+
EXECUTE 'CALL transaction_test1(10, $x$baz$x$)';
177+
END;
178+
$$;
179+
CALL transaction_test8();
180+
ERROR: invalid transaction termination
181+
CONTEXT: PL/pgSQL function transaction_test1(integer,text) line 6 at COMMIT
182+
SQL statement "CALL transaction_test1(10, $x$baz$x$)"
183+
PL/pgSQL function transaction_test8() line 3 at EXECUTE
133184
-- commit inside cursor loop
134185
CREATE TABLE test2 (x int);
135186
INSERT INTO test2 VALUES (0), (1), (2), (3), (4);

0 commit comments

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