Savepoints in plpgsql
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/plpgsql-transactions.html
Description:
https://www.postgresql.org/docs/current/plpgsql-transactions.html
In this section nothing mentioned that a savepoint (a subtransaction) will
not work inside plpgsql. Savepoint command is ended with "ERROR:
unsupported transaction command in PL/pgSQL". Also need to say, that "begin
end" block with "exception" handler will implicitly save and release
savepoints.
On Thursday, July 11, 2024, PG Doc comments form <noreply@postgresql.org>
wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/plpgsql-transactions.html
Description:https://www.postgresql.org/docs/current/plpgsql-transactions.html
In this section nothing mentioned that a savepoint (a subtransaction) will
not work inside plpgsql.
Correct, we prefer to just document how things work. 43.2 does mention
that blocks form subtransactions.
Also need to say, that "begin
end" block with "exception" handler will implicitly save and release
savepoints.
A savepoint is an SQL command giving you access to subtransactions.
Plpgsql doesn’t need or use it, it has a direct access to subtransactions
though in a limited/different way. In short, even if this worked this way
we wouldn’t document such an implementation detail.
I suppose we could draw analogies to savepoints, or maybe mention
subtransactions again in the procedure transactions chapter. Namely by
explaining why the last limitation on that page exists, which I presume is
related to subtransactions. But really that page is meant to show how
procedures can use commit/rollback to anffect the outer transaction unlike
all other plpgsql code which simply gets block-based subtransactions
management as previously described. It does this sufficiently and need not
be cluttered by subtransaction discussion in any meaningful detail.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Thursday, July 11, 2024, PG Doc comments form <noreply@postgresql.org>
wrote:In this section nothing mentioned that a savepoint (a subtransaction) will
not work inside plpgsql.
Correct, we prefer to just document how things work. 43.2 does mention
that blocks form subtransactions.
It probably would be reasonable to mention something about this
in 43.8, if only to provide an excuse to cross-reference 43.6.8.
I'm imagining more or less
PL/pgSQL does not support savepoints (SAVEPOINT/ROLLBACK TO
SAVEPOINT/RELEASE SAVEPOINT commands). Typical usage patterns
for savepoints can be replaced by blocks with exception handlers
(see 43.6.8).
I also notice the last para
A transaction cannot be ended inside a block with exception handlers.
which seems like it could be tied into that somehow. Maybe eliminate
that as a separate para, and write
PL/pgSQL does not support savepoints (SAVEPOINT/ROLLBACK TO
SAVEPOINT/RELEASE SAVEPOINT commands). Typical usage patterns
for savepoints can be replaced by blocks with exception handlers
(see 43.6.8). Under the hood, a block with exception handlers
forms a subtransaction, which means that transactions cannot
be ended inside such a block.
regards, tom lane
On 12.07.2024 16:42, Tom Lane wrote:
PL/pgSQL does not support savepoints (SAVEPOINT/ROLLBACK TO
SAVEPOINT/RELEASE SAVEPOINT commands). Typical usage patterns
for savepoints can be replaced by blocks with exception handlers
(see 43.6.8). Under the hood, a block with exception handlers
forms a subtransaction, which means that transactions cannot
be ended inside such a block.regards, tom lane
Yes, this will be fine.
--
Олег