JDBC Savepoint behavior

[jcflack]

Per JDBC, a Savepoint still exists after being used in a rollback and can be used again, while only those Savepoints created after it are invalidated by the rollback.

That behavior should be familiar, as it is the same as PostgreSQL's own SQL SAVEPOINT behavior. It is also found in pgJDBC, which has test coverage to confirm it.

PL/Java has been doing it wrong for a long time, because the behavior of PostgreSQL's "internal subtransaction" API is different, and that's the API that has to be used inside of a function. Those things are used up after a rollback.

Getting the right behavior simply requires transparently establishing a new "internal subtransaction" as the last step of a JDBC Savepoint rollback. That will be a behavior change, but largely transparent to old code. The fact that a savepoint remains usable after the old code has forgotten about it should not get in the way, and if the code (now unnecessarily) creates a new savepoint on top of it, even with the same name, it doesn't hurt anything. (Names are informational only, not checked for uniqueness or needed for identification; what you need to do a rollback or releaseSavepoint is the Savepoint instance.)

However, at function exit, PL/Java does have to mop up these "internal subtransactions" if any are lying around, and in old code that has used one in a rollback and forgotten about it, there will be one lying around when there was not before fixing this issue.

Ordinarily, PL/Java logs a warning in that case, and will either release (i.e. commit) or roll back the savepoint according to the pljava.release_lingering_savepoints GUC, which defaults to a rollback. Neither the warning nor the rollback would be desired in this case.

Hence, to preserve compatibility with old code, a Savepoint should also remember whether it has been used at least once in a rollback, and a savepoint that has been, and is found unreaped at function exit, should be silently released (or silently rolled back, if the function is completing abruptly).

[jcflack]

Believed resolved in 1.5.3.