SET TRANSACTION in PL/pgSQL
Started by Peter Eisentrautalmost 8 years ago5 messages
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
Attachments:
v1-0001-PL-pgSQL-Add-support-for-SET-TRANSACTION.patchtext/plain; charset=UTF-8; name=v1-0001-PL-pgSQL-Add-support-for-SET-TRANSACTION.patch; x-mac-creator=0; x-mac-type=0Download
From 8b4d1ca1a90c18eb3a797b3938e804478022543a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@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
Re: SET TRANSACTION in PL/pgSQL
Hi!
On Wed, Feb 28, 2018 at 11:45 PM, Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
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.
I didn't dig deeply into this subject. But should we rather teach SPI to
execute
utility statements without taking snapshot when not necessary. That seems
like what executor do for client provided queries. And that seems a bit
unlogical
that SPI behaves differently.
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Re: SET TRANSACTION in PL/pgSQL
On 3/15/18 17:49, Alexander Korotkov wrote:
I didn't dig deeply into this subject. But should we rather teach SPI
to execute
utility statements without taking snapshot when not necessary. That seems
like what executor do for client provided queries. And that seems a bit
unlogical
that SPI behaves differently.
Here is the same patch rewritten using SPI, using the new no_snapshots
facility recently introduced.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
v2-0001-PL-pgSQL-Add-support-for-SET-TRANSACTION.patchtext/plain; charset=UTF-8; name=v2-0001-PL-pgSQL-Add-support-for-SET-TRANSACTION.patch; x-mac-creator=0; x-mac-type=0Download
From 02dfce5edb9dd63005e1be81c8bef3d01bff2dd6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 29 Mar 2018 12:00:51 -0400
Subject: [PATCH v2] 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 | 29 ++++++++++++++++++
src/pl/plpgsql/src/pl_exec.c | 35 ++++++++++++++++++++++
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 | 25 ++++++++++++++++
7 files changed, 157 insertions(+), 2 deletions(-)
diff --git a/src/pl/plpgsql/src/expected/plpgsql_transaction.out b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
index b7f77101c3..2d0e3fa85e 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
@@ -389,6 +389,35 @@ 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
+-- error case
+DO LANGUAGE plpgsql $$
+BEGIN
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+END;
+$$;
+ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
+CONTEXT: SQL statement "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"
+PL/pgSQL function inline_code_block line 3 at SET
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 f574aa77f0..91a8cf0950 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -305,6 +305,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,
@@ -2005,6 +2007,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);
@@ -4707,6 +4713,35 @@ 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 setting a snapshot, for things like SET TRANSACTION.
+ */
+static int
+exec_stmt_set(PLpgSQL_execstate *estate, PLpgSQL_stmt_set *stmt)
+{
+ PLpgSQL_expr *expr = stmt->expr;
+ int rc;
+
+ if (expr->plan == NULL)
+ {
+ exec_prepare_plan(estate, expr, 0, true);
+ expr->plan->no_snapshots = true;
+ }
+
+ rc = SPI_execute_plan(expr->plan, NULL, NULL, estate->readonly_func, 0);
+
+ if (rc != SPI_OK_UTILITY)
+ elog(ERROR, "SPI_execute_plan failed executing query \"%s\": %s",
+ expr->query, SPI_result_code_string(rc));
+
+ 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 fc96fb5f4d..b93f866223 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -290,6 +290,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
return "COMMIT";
case PLPGSQL_STMT_ROLLBACK:
return "ROLLBACK";
+ case PLPGSQL_STMT_SET:
+ return "SET";
}
return "unknown";
@@ -372,6 +374,7 @@ static void free_perform(PLpgSQL_stmt_perform *stmt);
static void free_call(PLpgSQL_stmt_call *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);
@@ -461,6 +464,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;
@@ -624,6 +630,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)
{
@@ -820,6 +832,7 @@ static void dump_perform(PLpgSQL_stmt_perform *stmt);
static void dump_call(PLpgSQL_stmt_call *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);
@@ -919,6 +932,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;
@@ -1314,6 +1330,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 b8562ca8b4..b59869a534 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -199,7 +199,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_call 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
@@ -326,6 +326,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
@@ -336,6 +337,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
@@ -892,6 +894,8 @@ proc_stmt : pl_block ';'
{ $$ = $1; }
| stmt_rollback
{ $$ = $1; }
+ | stmt_set
+ { $$ = $1; }
;
stmt_perform : K_PERFORM expr_until_semi
@@ -2201,6 +2205,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
{
@@ -2488,6 +2516,7 @@ unreserved_keyword :
| K_QUERY
| K_RAISE
| K_RELATIVE
+ | K_RESET
| K_RESULT_OID
| K_RETURN
| K_RETURNED_SQLSTATE
@@ -2498,6 +2527,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 08614a89a8..fc4ba3054a 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -157,6 +157,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)
@@ -167,6 +168,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 dc90fe532f..fe617791df 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -127,7 +127,8 @@ typedef enum PLpgSQL_stmt_type
PLPGSQL_STMT_PERFORM,
PLPGSQL_STMT_CALL,
PLPGSQL_STMT_COMMIT,
- PLPGSQL_STMT_ROLLBACK
+ PLPGSQL_STMT_ROLLBACK,
+ PLPGSQL_STMT_SET
} PLpgSQL_stmt_type;
/*
@@ -539,6 +540,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 b7b6f81128..373d89864a 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
@@ -317,6 +317,31 @@ 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;
+$$;
+
+-- error case
+DO LANGUAGE plpgsql $$
+BEGIN
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+END;
+$$;
+
DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;
base-commit: c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc
--
2.16.3
Re: SET TRANSACTION in PL/pgSQL
On 03/29/2018 06:30 PM, Peter Eisentraut wrote:
On 3/15/18 17:49, Alexander Korotkov wrote:
I didn't dig deeply into this subject. But should we rather teach SPI
to execute
utility statements without taking snapshot when not necessary. That seems
like what executor do for client provided queries. And that seems a bit
unlogical
that SPI behaves differently.Here is the same patch rewritten using SPI, using the new no_snapshots
facility recently introduced.
Yeah, doing that using SPI seems much cleaner and more like the rest of
the commands. Most of the patch is boilerplate to support the grammar,
and the one interesting piece exec_stmt_set seems fine to me.
Barring any objections, I'll mark it as RFC tomorrow morning.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: SET TRANSACTION in PL/pgSQL
On 4/4/18 13:53, Tomas Vondra wrote:
Here is the same patch rewritten using SPI, using the new no_snapshots
facility recently introduced.Yeah, doing that using SPI seems much cleaner and more like the rest of
the commands. Most of the patch is boilerplate to support the grammar,
and the one interesting piece exec_stmt_set seems fine to me.Barring any objections, I'll mark it as RFC tomorrow morning.
You apparently didn't, but I committed it anyway. ;-)
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services