Thread: We still claim "cannot begin/end transactions in PL/pgSQL"

I notice there are still several places in pl_exec.c like this:

        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.")));
            break;

At best, the wording of these error messages is now obsolete.  I'm not
sure if we expect them to be reachable at all.  If they should be
can't-happen cases, I'd suggest just deleting them and letting control
fall to the generic default: cases in each switch.  If they are reachable,
the messages need work.

            regards, tom lane

On 5/25/18 12:16, Tom Lane wrote:
> I notice there are still several places in pl_exec.c like this:
> 
>         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.")));
>             break;
> 
> At best, the wording of these error messages is now obsolete.  I'm not
> sure if we expect them to be reachable at all.  If they should be
> can't-happen cases, I'd suggest just deleting them and letting control
> fall to the generic default: cases in each switch.  If they are reachable,
> the messages need work.

There are three occurrences:

The occurrence in exec_prepare_plan() could never be reached AFAICT,
because SPI_prepare_params() does not produce those SPI_prepare_params()
error values in the first place.  So remove them altogether.

The occurrence in exec_stmt_execsql() can be reached by running for
example SAVEPOINT, or any other TransactionStmt other than COMMIT and
ROLLBACK, which are intercepted by PL/pgSQL.  So we still need an error
message there.  Unfortunately, we don't know which TransactionStmt
caused the error, so the error has to be pretty generic.

The occurrence in exec_stmt_dynexecute() can be reached using something
like EXECUTE 'COMMIT', which is not supported/not implemented.  Hence a
tweaked error message there as well.

Possible patch attached.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 5/31/18 14:01, Peter Eisentraut wrote:
> There are three occurrences:
> 
> The occurrence in exec_prepare_plan() could never be reached AFAICT,
> because SPI_prepare_params() does not produce those SPI_prepare_params()
> error values in the first place.  So remove them altogether.
> 
> The occurrence in exec_stmt_execsql() can be reached by running for
> example SAVEPOINT, or any other TransactionStmt other than COMMIT and
> ROLLBACK, which are intercepted by PL/pgSQL.  So we still need an error
> message there.  Unfortunately, we don't know which TransactionStmt
> caused the error, so the error has to be pretty generic.
> 
> The occurrence in exec_stmt_dynexecute() can be reached using something
> like EXECUTE 'COMMIT', which is not supported/not implemented.  Hence a
> tweaked error message there as well.
> 
> Possible patch attached.

Committed.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services