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

Commitb981275

Browse files
committed
PL/pgSQL: Add support for SET TRANSACTION
A normal SQL command run inside PL/pgSQL acquires a snapshot, but SETTRANSACTION does not work anymore if a snapshot is set. So we have tohandle this separately.Reviewed-by: Alexander Korotkov <a.korotkov@postgrespro.ru>Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 parent530e69e commitb981275

File tree

7 files changed

+157
-2
lines changed

7 files changed

+157
-2
lines changed

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -389,6 +389,35 @@ SELECT * FROM test3;
389389
1
390390
(1 row)
391391

392+
-- SET TRANSACTION
393+
DO LANGUAGE plpgsql $$
394+
BEGIN
395+
PERFORM 1;
396+
RAISE INFO '%', current_setting('transaction_isolation');
397+
COMMIT;
398+
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
399+
PERFORM 1;
400+
RAISE INFO '%', current_setting('transaction_isolation');
401+
COMMIT;
402+
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
403+
RESET TRANSACTION ISOLATION LEVEL;
404+
PERFORM 1;
405+
RAISE INFO '%', current_setting('transaction_isolation');
406+
COMMIT;
407+
END;
408+
$$;
409+
INFO: read committed
410+
INFO: repeatable read
411+
INFO: read committed
412+
-- error case
413+
DO LANGUAGE plpgsql $$
414+
BEGIN
415+
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
416+
END;
417+
$$;
418+
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
419+
CONTEXT: SQL statement "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"
420+
PL/pgSQL function inline_code_block line 3 at SET
392421
DROP TABLE test1;
393422
DROP TABLE test2;
394423
DROP TABLE test3;

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -305,6 +305,8 @@ static int exec_stmt_commit(PLpgSQL_execstate *estate,
305305
PLpgSQL_stmt_commit*stmt);
306306
staticintexec_stmt_rollback(PLpgSQL_execstate*estate,
307307
PLpgSQL_stmt_rollback*stmt);
308+
staticintexec_stmt_set(PLpgSQL_execstate*estate,
309+
PLpgSQL_stmt_set*stmt);
308310

309311
staticvoidplpgsql_estate_setup(PLpgSQL_execstate*estate,
310312
PLpgSQL_function*func,
@@ -2005,6 +2007,10 @@ exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
20052007
rc=exec_stmt_rollback(estate, (PLpgSQL_stmt_rollback*)stmt);
20062008
break;
20072009

2010+
casePLPGSQL_STMT_SET:
2011+
rc=exec_stmt_set(estate, (PLpgSQL_stmt_set*)stmt);
2012+
break;
2013+
20082014
default:
20092015
estate->err_stmt=save_estmt;
20102016
elog(ERROR,"unrecognized cmd_type: %d",stmt->cmd_type);
@@ -4732,6 +4738,35 @@ exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt)
47324738
returnPLPGSQL_RC_OK;
47334739
}
47344740

4741+
/*
4742+
* exec_stmt_set
4743+
*
4744+
* Execute SET/RESET statement.
4745+
*
4746+
* We just parse and execute the statement normally, but we have to do it
4747+
* without setting a snapshot, for things like SET TRANSACTION.
4748+
*/
4749+
staticint
4750+
exec_stmt_set(PLpgSQL_execstate*estate,PLpgSQL_stmt_set*stmt)
4751+
{
4752+
PLpgSQL_expr*expr=stmt->expr;
4753+
intrc;
4754+
4755+
if (expr->plan==NULL)
4756+
{
4757+
exec_prepare_plan(estate,expr,0, true);
4758+
expr->plan->no_snapshots= true;
4759+
}
4760+
4761+
rc=SPI_execute_plan(expr->plan,NULL,NULL,estate->readonly_func,0);
4762+
4763+
if (rc!=SPI_OK_UTILITY)
4764+
elog(ERROR,"SPI_execute_plan failed executing query \"%s\": %s",
4765+
expr->query,SPI_result_code_string(rc));
4766+
4767+
returnPLPGSQL_RC_OK;
4768+
}
4769+
47354770
/* ----------
47364771
* exec_assign_exprPut an expression's result into a variable.
47374772
* ----------

‎src/pl/plpgsql/src/pl_funcs.c

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -290,6 +290,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
290290
return"COMMIT";
291291
casePLPGSQL_STMT_ROLLBACK:
292292
return"ROLLBACK";
293+
casePLPGSQL_STMT_SET:
294+
return"SET";
293295
}
294296

295297
return"unknown";
@@ -372,6 +374,7 @@ static void free_perform(PLpgSQL_stmt_perform *stmt);
372374
staticvoidfree_call(PLpgSQL_stmt_call*stmt);
373375
staticvoidfree_commit(PLpgSQL_stmt_commit*stmt);
374376
staticvoidfree_rollback(PLpgSQL_stmt_rollback*stmt);
377+
staticvoidfree_set(PLpgSQL_stmt_set*stmt);
375378
staticvoidfree_expr(PLpgSQL_expr*expr);
376379

377380

@@ -461,6 +464,9 @@ free_stmt(PLpgSQL_stmt *stmt)
461464
casePLPGSQL_STMT_ROLLBACK:
462465
free_rollback((PLpgSQL_stmt_rollback*)stmt);
463466
break;
467+
casePLPGSQL_STMT_SET:
468+
free_set((PLpgSQL_stmt_set*)stmt);
469+
break;
464470
default:
465471
elog(ERROR,"unrecognized cmd_type: %d",stmt->cmd_type);
466472
break;
@@ -624,6 +630,12 @@ free_rollback(PLpgSQL_stmt_rollback *stmt)
624630
{
625631
}
626632

633+
staticvoid
634+
free_set(PLpgSQL_stmt_set*stmt)
635+
{
636+
free_expr(stmt->expr);
637+
}
638+
627639
staticvoid
628640
free_exit(PLpgSQL_stmt_exit*stmt)
629641
{
@@ -820,6 +832,7 @@ static void dump_perform(PLpgSQL_stmt_perform *stmt);
820832
staticvoiddump_call(PLpgSQL_stmt_call*stmt);
821833
staticvoiddump_commit(PLpgSQL_stmt_commit*stmt);
822834
staticvoiddump_rollback(PLpgSQL_stmt_rollback*stmt);
835+
staticvoiddump_set(PLpgSQL_stmt_set*stmt);
823836
staticvoiddump_expr(PLpgSQL_expr*expr);
824837

825838

@@ -919,6 +932,9 @@ dump_stmt(PLpgSQL_stmt *stmt)
919932
casePLPGSQL_STMT_ROLLBACK:
920933
dump_rollback((PLpgSQL_stmt_rollback*)stmt);
921934
break;
935+
casePLPGSQL_STMT_SET:
936+
dump_set((PLpgSQL_stmt_set*)stmt);
937+
break;
922938
default:
923939
elog(ERROR,"unrecognized cmd_type: %d",stmt->cmd_type);
924940
break;
@@ -1314,6 +1330,13 @@ dump_rollback(PLpgSQL_stmt_rollback *stmt)
13141330
printf("ROLLBACK\n");
13151331
}
13161332

1333+
staticvoid
1334+
dump_set(PLpgSQL_stmt_set*stmt)
1335+
{
1336+
dump_ind();
1337+
printf("%s\n",stmt->expr->query);
1338+
}
1339+
13171340
staticvoid
13181341
dump_exit(PLpgSQL_stmt_exit*stmt)
13191342
{

‎src/pl/plpgsql/src/pl_gram.y

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -199,7 +199,7 @@ staticvoidcheck_raise_parameters(PLpgSQL_stmt_raise *stmt);
199199
%type<stmt>stmt_returnstmt_raisestmt_assertstmt_execsql
200200
%type<stmt>stmt_dynexecutestmt_forstmt_performstmt_callstmt_getdiag
201201
%type<stmt>stmt_openstmt_fetchstmt_movestmt_closestmt_null
202-
%type<stmt>stmt_commitstmt_rollback
202+
%type<stmt>stmt_commitstmt_rollbackstmt_set
203203
%type<stmt>stmt_casestmt_foreach_a
204204

205205
%type<list>proc_exceptions
@@ -327,6 +327,7 @@ staticvoidcheck_raise_parameters(PLpgSQL_stmt_raise *stmt);
327327
%token<keyword>K_QUERY
328328
%token<keyword>K_RAISE
329329
%token<keyword>K_RELATIVE
330+
%token<keyword>K_RESET
330331
%token<keyword>K_RESULT_OID
331332
%token<keyword>K_RETURN
332333
%token<keyword>K_RETURNED_SQLSTATE
@@ -337,6 +338,7 @@ staticvoidcheck_raise_parameters(PLpgSQL_stmt_raise *stmt);
337338
%token<keyword>K_SCHEMA
338339
%token<keyword>K_SCHEMA_NAME
339340
%token<keyword>K_SCROLL
341+
%token<keyword>K_SET
340342
%token<keyword>K_SLICE
341343
%token<keyword>K_SQLSTATE
342344
%token<keyword>K_STACKED
@@ -893,6 +895,8 @@ proc_stmt: pl_block ';'
893895
{ $$ = $1; }
894896
| stmt_rollback
895897
{ $$ = $1; }
898+
| stmt_set
899+
{ $$ = $1; }
896900
;
897901

898902
stmt_perform: K_PERFORM expr_until_semi
@@ -2206,6 +2210,30 @@ stmt_rollback: K_ROLLBACK ';'
22062210
}
22072211
;
22082212

2213+
stmt_set: K_SET
2214+
{
2215+
PLpgSQL_stmt_set *new;
2216+
2217+
new =palloc0(sizeof(PLpgSQL_stmt_set));
2218+
new->cmd_type = PLPGSQL_STMT_SET;
2219+
new->lineno =plpgsql_location_to_lineno(@1);
2220+
new->expr =read_sql_stmt("SET");
2221+
2222+
$$ = (PLpgSQL_stmt *)new;
2223+
}
2224+
| K_RESET
2225+
{
2226+
PLpgSQL_stmt_set *new;
2227+
2228+
new =palloc0(sizeof(PLpgSQL_stmt_set));
2229+
new->cmd_type = PLPGSQL_STMT_SET;
2230+
new->lineno =plpgsql_location_to_lineno(@1);
2231+
new->expr =read_sql_stmt("RESET");
2232+
2233+
$$ = (PLpgSQL_stmt *)new;
2234+
}
2235+
;
2236+
22092237

22102238
cursor_variable: T_DATUM
22112239
{
@@ -2494,6 +2522,7 @@ unreserved_keyword:
24942522
| K_QUERY
24952523
| K_RAISE
24962524
| K_RELATIVE
2525+
| K_RESET
24972526
| K_RESULT_OID
24982527
| K_RETURN
24992528
| K_RETURNED_SQLSTATE
@@ -2504,6 +2533,7 @@ unreserved_keyword:
25042533
| K_SCHEMA
25052534
| K_SCHEMA_NAME
25062535
| K_SCROLL
2536+
| K_SET
25072537
| K_SLICE
25082538
| K_SQLSTATE
25092539
| K_STACKED

‎src/pl/plpgsql/src/pl_scanner.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,7 @@ static const ScanKeyword unreserved_keywords[] = {
158158
PG_KEYWORD("query",K_QUERY,UNRESERVED_KEYWORD)
159159
PG_KEYWORD("raise",K_RAISE,UNRESERVED_KEYWORD)
160160
PG_KEYWORD("relative",K_RELATIVE,UNRESERVED_KEYWORD)
161+
PG_KEYWORD("reset",K_RESET,UNRESERVED_KEYWORD)
161162
PG_KEYWORD("result_oid",K_RESULT_OID,UNRESERVED_KEYWORD)
162163
PG_KEYWORD("return",K_RETURN,UNRESERVED_KEYWORD)
163164
PG_KEYWORD("returned_sqlstate",K_RETURNED_SQLSTATE,UNRESERVED_KEYWORD)
@@ -168,6 +169,7 @@ static const ScanKeyword unreserved_keywords[] = {
168169
PG_KEYWORD("schema",K_SCHEMA,UNRESERVED_KEYWORD)
169170
PG_KEYWORD("schema_name",K_SCHEMA_NAME,UNRESERVED_KEYWORD)
170171
PG_KEYWORD("scroll",K_SCROLL,UNRESERVED_KEYWORD)
172+
PG_KEYWORD("set",K_SET,UNRESERVED_KEYWORD)
171173
PG_KEYWORD("slice",K_SLICE,UNRESERVED_KEYWORD)
172174
PG_KEYWORD("sqlstate",K_SQLSTATE,UNRESERVED_KEYWORD)
173175
PG_KEYWORD("stacked",K_STACKED,UNRESERVED_KEYWORD)

‎src/pl/plpgsql/src/plpgsql.h

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -127,7 +127,8 @@ typedef enum PLpgSQL_stmt_type
127127
PLPGSQL_STMT_PERFORM,
128128
PLPGSQL_STMT_CALL,
129129
PLPGSQL_STMT_COMMIT,
130-
PLPGSQL_STMT_ROLLBACK
130+
PLPGSQL_STMT_ROLLBACK,
131+
PLPGSQL_STMT_SET
131132
}PLpgSQL_stmt_type;
132133

133134
/*
@@ -539,6 +540,16 @@ typedef struct PLpgSQL_stmt_rollback
539540
intlineno;
540541
}PLpgSQL_stmt_rollback;
541542

543+
/*
544+
* SET statement
545+
*/
546+
typedefstructPLpgSQL_stmt_set
547+
{
548+
PLpgSQL_stmt_typecmd_type;
549+
intlineno;
550+
PLpgSQL_expr*expr;
551+
}PLpgSQL_stmt_set;
552+
542553
/*
543554
* GET DIAGNOSTICS item
544555
*/

‎src/pl/plpgsql/src/sql/plpgsql_transaction.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -317,6 +317,31 @@ $$;
317317
SELECT*FROM test3;
318318

319319

320+
-- SET TRANSACTION
321+
DO LANGUAGE plpgsql $$
322+
BEGIN
323+
PERFORM1;
324+
RAISE INFO'%', current_setting('transaction_isolation');
325+
COMMIT;
326+
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
327+
PERFORM1;
328+
RAISE INFO'%', current_setting('transaction_isolation');
329+
COMMIT;
330+
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
331+
RESET TRANSACTION ISOLATION LEVEL;
332+
PERFORM1;
333+
RAISE INFO'%', current_setting('transaction_isolation');
334+
COMMIT;
335+
END;
336+
$$;
337+
338+
-- error case
339+
DO LANGUAGE plpgsql $$
340+
BEGIN
341+
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
342+
END;
343+
$$;
344+
320345
DROPTABLE test1;
321346
DROPTABLE test2;
322347
DROPTABLE test3;

0 commit comments

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