Currently, you can't run SET TRANSACTION in PL/pgSQL. A normal SQL
command run inside PL/pgSQL acquires a snapshot, but SET
TRANSACTION does not work anymore if a snapshot is set. Here is a patch
to work around that by handling this command separately. I have coded
this here bypassing SPI entirely. But there is some overlap with the
no_snapshot option in the patch "PL/pgSQL nested CALL with
transactions", so maybe a better solution will arise. This will also
inform how to tackle this in other PLs.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 8b4d1ca1a90c18eb3a797b3938e804478022543a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 26 Feb 2018 11:59:06 -0500
Subject: [PATCH v1] PL/pgSQL: Add support for SET TRANSACTION
A normal SQL command run inside PL/pgSQL acquires a snapshot, but SET
TRANSACTION does not work anymore if a snapshot is set. So we have to
handle this separately.
---
.../plpgsql/src/expected/plpgsql_transaction.out | 20 +++++++++
src/pl/plpgsql/src/pl_exec.c | 49 ++++++++++++++++++++++
src/pl/plpgsql/src/pl_funcs.c | 23 ++++++++++
src/pl/plpgsql/src/pl_gram.y | 32 +++++++++++++-
src/pl/plpgsql/src/pl_scanner.c | 2 +
src/pl/plpgsql/src/plpgsql.h | 13 +++++-
src/pl/plpgsql/src/sql/plpgsql_transaction.sql | 19 +++++++++
7 files changed, 156 insertions(+), 2 deletions(-)
diff --git a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
index 8ec22c646c..5f569dc64a 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
@@ -236,6 +236,26 @@ SELECT * FROM test3;
1
(1 row)
+-- SET TRANSACTION
+DO LANGUAGE plpgsql $$
+BEGIN
+ PERFORM 1;
+ RAISE INFO '%', current_setting('transaction_isolation');
+ COMMIT;
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+ PERFORM 1;
+ RAISE INFO '%', current_setting('transaction_isolation');
+ COMMIT;
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+ RESET TRANSACTION ISOLATION LEVEL;
+ PERFORM 1;
+ RAISE INFO '%', current_setting('transaction_isolation');
+ COMMIT;
+END;
+$$;
+INFO: read committed
+INFO: repeatable read
+INFO: read committed
DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 4ff87e0879..9a25ee9ad9 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -32,6 +32,7 @@
#include "parser/scansup.h"
#include "storage/proc.h"
#include "tcop/tcopprot.h"
+#include "tcop/utility.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
@@ -299,6 +300,8 @@ static int exec_stmt_commit(PLpgSQL_execstate *estate,
PLpgSQL_stmt_commit *stmt);
static int exec_stmt_rollback(PLpgSQL_execstate *estate,
PLpgSQL_stmt_rollback *stmt);
+static int exec_stmt_set(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_set *stmt);
static void plpgsql_estate_setup(PLpgSQL_execstate *estate,
PLpgSQL_function *func,
@@ -1997,6 +2000,10 @@ exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
rc = exec_stmt_rollback(estate, (PLpgSQL_stmt_rollback
*) stmt);
break;
+ case PLPGSQL_STMT_SET:
+ rc = exec_stmt_set(estate, (PLpgSQL_stmt_set *) stmt);
+ break;
+
default:
estate->err_stmt = save_estmt;
elog(ERROR, "unrecognized cmd_type: %d",
stmt->cmd_type);
@@ -4566,6 +4573,48 @@ exec_stmt_rollback(PLpgSQL_execstate *estate,
PLpgSQL_stmt_rollback *stmt)
return PLPGSQL_RC_OK;
}
+/*
+ * exec_stmt_set
+ *
+ * Execute SET/RESET statement.
+ *
+ * We just parse and execute the statement normally, but we have to do it
+ * without going through the usual SPI functions, because we don't want to set
+ * a snapshot for things like SET TRANSACTION.
+ *
+ * XXX It might be nice to use SPI_execute(), but in order to not get a
+ * snapshot, we have to pass read_only = true, which in turn prevents SET
+ * commands.
+ */
+static int
+exec_stmt_set(PLpgSQL_execstate *estate, PLpgSQL_stmt_set *stmt)
+{
+ List *raw_parsetree_list;
+ RawStmt *parsetree;
+ List *stmt_list;
+ PlannedStmt *pstmt;
+
+ raw_parsetree_list = pg_parse_query(stmt->expr->query);
+ Assert(list_length(raw_parsetree_list) == 1);
+ parsetree = linitial_node(RawStmt, raw_parsetree_list);
+
+ stmt_list = pg_analyze_and_rewrite(parsetree, stmt->expr->query, NULL,
0, NULL);
+ stmt_list = pg_plan_queries(stmt_list, 0, NULL);
+ Assert(list_length(stmt_list) == 1);
+ pstmt = linitial_node(PlannedStmt, stmt_list);
+ Assert(pstmt->commandType == CMD_UTILITY);
+
+ ProcessUtility(pstmt,
+ stmt->expr->query,
+ PROCESS_UTILITY_QUERY,
+ NULL,
+ NULL,
+ NULL,
+ NULL);
+
+ return PLPGSQL_RC_OK;
+}
+
/* ----------
* exec_assign_expr Put an expression's result into a
variable.
* ----------
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index b986fc39b3..9acee818cc 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -288,6 +288,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
return "COMMIT";
case PLPGSQL_STMT_ROLLBACK:
return "ROLLBACK";
+ case PLPGSQL_STMT_SET:
+ return "SET";
}
return "unknown";
@@ -369,6 +371,7 @@ static void free_close(PLpgSQL_stmt_close *stmt);
static void free_perform(PLpgSQL_stmt_perform *stmt);
static void free_commit(PLpgSQL_stmt_commit *stmt);
static void free_rollback(PLpgSQL_stmt_rollback *stmt);
+static void free_set(PLpgSQL_stmt_set *stmt);
static void free_expr(PLpgSQL_expr *expr);
@@ -455,6 +458,9 @@ free_stmt(PLpgSQL_stmt *stmt)
case PLPGSQL_STMT_ROLLBACK:
free_rollback((PLpgSQL_stmt_rollback *) stmt);
break;
+ case PLPGSQL_STMT_SET:
+ free_set((PLpgSQL_stmt_set *) stmt);
+ break;
default:
elog(ERROR, "unrecognized cmd_type: %d",
stmt->cmd_type);
break;
@@ -612,6 +618,12 @@ free_rollback(PLpgSQL_stmt_rollback *stmt)
{
}
+static void
+free_set(PLpgSQL_stmt_set *stmt)
+{
+ free_expr(stmt->expr);
+}
+
static void
free_exit(PLpgSQL_stmt_exit *stmt)
{
@@ -807,6 +819,7 @@ static void dump_close(PLpgSQL_stmt_close *stmt);
static void dump_perform(PLpgSQL_stmt_perform *stmt);
static void dump_commit(PLpgSQL_stmt_commit *stmt);
static void dump_rollback(PLpgSQL_stmt_rollback *stmt);
+static void dump_set(PLpgSQL_stmt_set *stmt);
static void dump_expr(PLpgSQL_expr *expr);
@@ -903,6 +916,9 @@ dump_stmt(PLpgSQL_stmt *stmt)
case PLPGSQL_STMT_ROLLBACK:
dump_rollback((PLpgSQL_stmt_rollback *) stmt);
break;
+ case PLPGSQL_STMT_SET:
+ dump_set((PLpgSQL_stmt_set *) stmt);
+ break;
default:
elog(ERROR, "unrecognized cmd_type: %d",
stmt->cmd_type);
break;
@@ -1289,6 +1305,13 @@ dump_rollback(PLpgSQL_stmt_rollback *stmt)
printf("ROLLBACK\n");
}
+static void
+dump_set(PLpgSQL_stmt_set *stmt)
+{
+ dump_ind();
+ printf("%s\n", stmt->expr->query);
+}
+
static void
dump_exit(PLpgSQL_stmt_exit *stmt)
{
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 688fbd6531..4734c80766 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -198,7 +198,7 @@ static void
check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%type <stmt> stmt_return stmt_raise stmt_assert stmt_execsql
%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null
-%type <stmt> stmt_commit stmt_rollback
+%type <stmt> stmt_commit stmt_rollback stmt_set
%type <stmt> stmt_case stmt_foreach_a
%type <list> proc_exceptions
@@ -323,6 +323,7 @@ static void
check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_QUERY
%token <keyword> K_RAISE
%token <keyword> K_RELATIVE
+%token <keyword> K_RESET
%token <keyword> K_RESULT_OID
%token <keyword> K_RETURN
%token <keyword> K_RETURNED_SQLSTATE
@@ -333,6 +334,7 @@ static void
check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_SCHEMA
%token <keyword> K_SCHEMA_NAME
%token <keyword> K_SCROLL
+%token <keyword> K_SET
%token <keyword> K_SLICE
%token <keyword> K_SQLSTATE
%token <keyword> K_STACKED
@@ -887,6 +889,8 @@ proc_stmt : pl_block ';'
{ $$ = $1; }
| stmt_rollback
{ $$ = $1; }
+ | stmt_set
+ { $$ = $1; }
;
stmt_perform : K_PERFORM expr_until_semi
@@ -2167,6 +2171,30 @@ stmt_rollback : K_ROLLBACK ';'
}
;
+stmt_set : K_SET
+ {
+ PLpgSQL_stmt_set *new;
+
+ new =
palloc0(sizeof(PLpgSQL_stmt_set));
+ new->cmd_type =
PLPGSQL_STMT_SET;
+ new->lineno =
plpgsql_location_to_lineno(@1);
+ new->expr = read_sql_stmt("SET
");
+
+ $$ = (PLpgSQL_stmt *)new;
+ }
+ | K_RESET
+ {
+ PLpgSQL_stmt_set *new;
+
+ new =
palloc0(sizeof(PLpgSQL_stmt_set));
+ new->cmd_type =
PLPGSQL_STMT_SET;
+ new->lineno =
plpgsql_location_to_lineno(@1);
+ new->expr =
read_sql_stmt("RESET ");
+
+ $$ = (PLpgSQL_stmt *)new;
+ }
+ ;
+
cursor_variable : T_DATUM
{
@@ -2452,6 +2480,7 @@ unreserved_keyword :
| K_QUERY
| K_RAISE
| K_RELATIVE
+ | K_RESET
| K_RESULT_OID
| K_RETURN
| K_RETURNED_SQLSTATE
@@ -2462,6 +2491,7 @@ unreserved_keyword :
| K_SCHEMA
| K_SCHEMA_NAME
| K_SCROLL
+ | K_SET
| K_SLICE
| K_SQLSTATE
| K_STACKED
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index 12a3e6b818..ed8933f69a 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -155,6 +155,7 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
PG_KEYWORD("raise", K_RAISE, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("reset", K_RESET, UNRESERVED_KEYWORD)
PG_KEYWORD("result_oid", K_RESULT_OID, UNRESERVED_KEYWORD)
PG_KEYWORD("return", K_RETURN, UNRESERVED_KEYWORD)
PG_KEYWORD("returned_sqlstate", K_RETURNED_SQLSTATE, UNRESERVED_KEYWORD)
@@ -165,6 +166,7 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("schema", K_SCHEMA, UNRESERVED_KEYWORD)
PG_KEYWORD("schema_name", K_SCHEMA_NAME, UNRESERVED_KEYWORD)
PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD)
+ PG_KEYWORD("set", K_SET, UNRESERVED_KEYWORD)
PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD)
PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD)
PG_KEYWORD("stacked", K_STACKED, UNRESERVED_KEYWORD)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 26a7344e9a..bece089529 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -126,7 +126,8 @@ typedef enum PLpgSQL_stmt_type
PLPGSQL_STMT_CLOSE,
PLPGSQL_STMT_PERFORM,
PLPGSQL_STMT_COMMIT,
- PLPGSQL_STMT_ROLLBACK
+ PLPGSQL_STMT_ROLLBACK,
+ PLPGSQL_STMT_SET
} PLpgSQL_stmt_type;
/*
@@ -526,6 +527,16 @@ typedef struct PLpgSQL_stmt_rollback
int lineno;
} PLpgSQL_stmt_rollback;
+/*
+ * SET statement
+ */
+typedef struct PLpgSQL_stmt_set
+{
+ PLpgSQL_stmt_type cmd_type;
+ int lineno;
+ PLpgSQL_expr *expr;
+} PLpgSQL_stmt_set;
+
/*
* GET DIAGNOSTICS item
*/
diff --git a/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
b/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
index 02ee735079..d2a2efd661 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
@@ -210,6 +210,25 @@ CREATE TABLE test2 (x int);
SELECT * FROM test3;
+-- SET TRANSACTION
+DO LANGUAGE plpgsql $$
+BEGIN
+ PERFORM 1;
+ RAISE INFO '%', current_setting('transaction_isolation');
+ COMMIT;
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+ PERFORM 1;
+ RAISE INFO '%', current_setting('transaction_isolation');
+ COMMIT;
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+ RESET TRANSACTION ISOLATION LEVEL;
+ PERFORM 1;
+ RAISE INFO '%', current_setting('transaction_isolation');
+ COMMIT;
+END;
+$$;
+
+
DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;
base-commit: 51057feaa6bd24b51e6a4715c2090491ef037534
--
2.16.2
