Error message with COMMIT/ROLLBACK within PL/pgSQL
This is not very user-friendly:
postgres=# do $$
begin
COMMIT;
end;
$$;
ERROR: SPI_execute_plan_with_paramlist failed executing query "COMMIT":
SPI_ERROR_TRANSACTION
CONTEXT: PL/pgSQL function "inline_code_block" line 2 at SQL statement
Clearly we don't support that, but seems like it would deserve a better
error message.
Curiously, we *do* give a better error message if you try that with EXECUTE:
postgres=# do $$
begin
EXECUTE 'COMMIT';
end;
$$;
ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function "inline_code_block" line 2 at EXECUTE statement
Barring objections, I'll add the same error message to the non-execute
codepath.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Attachments:
commit-in-plpgsql-errmsg-1.patchtext/x-diff; name=commit-in-plpgsql-errmsg-1.patchDownload
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 9929e04..a21ea53 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2889,6 +2889,17 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
exec_set_found(estate, false);
break;
+ /* Some SPI errors deserve specific error messages */
+ case SPI_ERROR_COPY:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot COPY to/from client in PL/pgSQL")));
+ case SPI_ERROR_TRANSACTION:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot begin/end transactions in PL/pgSQL"),
+ errhint("Use a BEGIN block with an EXCEPTION clause instead.")));
+
default:
elog(ERROR, "SPI_execute_plan_with_paramlist failed executing query \"%s\": %s",
expr->query, SPI_result_code_string(rc));