Currently there's a test [1] in the regression suite that ensures that
a SAVEPOINT cannot be initialized outside a transaction.
Instead of throwing an error, if we allowed it such that a SAVEPOINT
outside a transaction implicitly started a transaction, and the
corresponding ROLLBACK TO or RELEASE command finished that
transaction, I believe it will provide a uniform behavior that will
let SAVEPOINT be used on its own, to the exclusion of BEGIN, and I
believe the users will find it very useful as well.
For example, I was looking at a SQL script that is self-contained
(creates objects that it later operates on), but did not leverage
Postgres' ability to perform DDL and other non-DML operations inside a
transaction. My first instinct was to enclose it in a BEGIN-COMMIT
pair. But doing that would not play well with the other SQL scripts
that include/wrap it (using, say, \include or \ir). So the next
thought that crossed my mind was to wrap the script in a
SAVEPOINT-RELEASE pair, but that would obviously fail when the script
is sourced on its own, because SAVEPOINT and RELEASE are not allowed
outside a transaction.
Another possibility is as follows, but clearly not acceptable because
of uncertainty of outcome.
BEGIN TRANSACTION; -- Cmd1. issues a WARNING if already in txn, not otherwise
SAVEPOINT AA;
-- Do work
RELEASE SAVEPOINT AA;
COMMIT; -- This will commit the transaction started before Cmd1, if any.
Is there any desire to implement the behavior described in $SUBJECT?
Arguably, Postgres would be straying slightly further away from the
SQL compatibility of this command, but not by much.
Here's a sample session describing what the behavior would look like.
SAVEPOINT AA ; -- currently an error if outside a transaction;
-- but starts a transaction after implementation
-- Do work with other SQL commands
COMMIT ; -- Commits transaction AA started with savepoint. Transaction started
-- before that, if any, is not affected until its corresponding COMMIT/ROLLBACK.
-- Other commands that end this transaction:
-- -- ROLLBACK TO AA (rolls back txn; usual behavior)
-- -- RELEASE SAVEPOINT AA (commit/rollback depending on state of txn;
usual behavior)
-- -- ROLLBACK (rolls back the top-level transaction AA)
Looking at this example, we will also get the "named transactions"
feature for free! I don't know what the use of a named transaction
would be, though; identify it and use it in WAL and related features
somehow?!!
[1]:
commit cc813fc2b8d9293bbd4d0e0d6a6f3b9cf02fe32f
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Jul 27 05:11:48 2004 +0000
Replace nested-BEGIN syntax for subtransactions with spec-compliant
SAVEPOINT/RELEASE/ROLLBACK-TO syntax. (Alvaro)
Cause COMMIT of a failed transaction to report ROLLBACK instead of
COMMIT in its command tag. (Tom)
Fix a few loose ends in the nested-transactions stuff.
....
-- only in a transaction block:
SAVEPOINT one;
ERROR: SAVEPOINT can only be used in transaction blocks
ROLLBACK TO SAVEPOINT one;
ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
RELEASE SAVEPOINT one;
ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/