From efcaf1e868d8399d932e68b8b248bcbd089b2d6b Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 1 Aug 2004 17:32:22 +0000 Subject: Some mop-up work for savepoints (nested transactions). Store a small number of active subtransaction XIDs in each backend's PGPROC entry, and use this to avoid expensive probes into pg_subtrans during TransactionIdIsInProgress. Extend EOXactCallback API to allow add-on modules to get control at subxact start/end. (This is deliberately not compatible with the former API, since any uses of that API probably need manual review anyway.) Add basic reference documentation for SAVEPOINT and related commands. Minor other cleanups to check off some of the open issues for subtransactions. Alvaro Herrera and Tom Lane. --- doc/src/sgml/advanced.sgml | 60 +++++++++++- doc/src/sgml/ref/allfiles.sgml | 5 +- doc/src/sgml/ref/begin.sgml | 5 +- doc/src/sgml/ref/release.sgml | 143 ++++++++++++++++++++++++++++ doc/src/sgml/ref/rollback.sgml | 3 +- doc/src/sgml/ref/rollback_to.sgml | 163 ++++++++++++++++++++++++++++++++ doc/src/sgml/ref/savepoint.sgml | 152 +++++++++++++++++++++++++++++ doc/src/sgml/ref/start_transaction.sgml | 3 +- doc/src/sgml/reference.sgml | 5 +- 9 files changed, 532 insertions(+), 7 deletions(-) create mode 100644 doc/src/sgml/ref/release.sgml create mode 100644 doc/src/sgml/ref/rollback_to.sgml create mode 100644 doc/src/sgml/ref/savepoint.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 6980dc4775..475ba910a1 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -1,5 +1,5 @@ @@ -257,6 +257,64 @@ COMMIT; you are using. + + + It's possible to control the statements in a transaction in a more + granular fashion through the use of savepoints. Savepoints + allow you to selectively discard parts of the transaction, while + committing the rest. After defining a savepoint with + SAVEPOINT, you can if needed roll back to the savepoint + with ROLLBACK TO. All the transaction's database changes + between defining the savepoint and rolling back to it are discarded, but + changes earlier than the savepoint are kept. + + + + After rolling back to a savepoint, it continues to be defined, so you can + roll back to it several times. Conversely, if you are sure you won't need + to roll back to a particular savepoint again, it can be released, so the + system can free some resources. Keep in mind that either releasing or + rolling back to a savepoint + will automatically release all savepoints that were defined after it. + + + + All this is happening within the transaction block, so none of it + is visible to other database sessions. When and if you commit the + transaction block, the committed actions become visible as a unit + to other sessions, while the rolled-back actions never become visible + at all. + + + + Remembering the bank database, suppose we debit $100.00 from Alice's + account, and credit Bob's account, only to find later that we should + have credited Wally's account. We could do it using savepoints like + + +BEGIN; +UPDATE accounts SET balance = balance - 100.00 + WHERE name = 'Alice'; +SAVEPOINT my_savepoint; +UPDATE accounts SET balance = balance + 100.00 + WHERE name = 'Bob'; +-- oops ... forget that and use Wally's account +ROLLBACK TO my_savepoint; +UPDATE accounts SET balance = balance + 100.00 + WHERE name = 'Wally'; +COMMIT; + + + + + This example is, of course, oversimplified, but there's a lot of control + to be had over a transaction block through the use of savepoints. + Moreover, ROLLBACK TO is the only way to regain control of a + transaction block that was put in aborted state by the + system due to an error, short of rolling it back completely and starting + again. + + diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index f02edd4cff..2ab20c9c69 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -88,9 +88,12 @@ Complete list of usable sgml source files in this directory. + + + diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml index d8ddf81ee0..d40cb416bc 100644 --- a/doc/src/sgml/ref/begin.sgml +++ b/doc/src/sgml/ref/begin.sgml @@ -1,5 +1,5 @@ @@ -31,7 +31,7 @@ BEGIN [ WORK | TRANSACTION ] BEGIN initiates a transaction block, that is, - all statements after BEGIN command will be + all statements after a BEGIN command will be executed in a single transaction until an explicit or is given. @@ -145,6 +145,7 @@ BEGIN; + diff --git a/doc/src/sgml/ref/release.sgml b/doc/src/sgml/ref/release.sgml new file mode 100644 index 0000000000..17ef14ee3c --- /dev/null +++ b/doc/src/sgml/ref/release.sgml @@ -0,0 +1,143 @@ + + + + + RELEASE + SQL - Language Statements + + + + RELEASE + destroy a previously defined savepoint + + + + RELEASE + + + + savepoints + releasing + + + + +RELEASE savepoint_name + + + + + Description + + + RELEASE destroys a savepoint previously defined + in the current transaction. + + + + Destroying a savepoint makes it unavailable as a rollback point, + but it has no other user visible behavior. It does not undo the + effects of commands executed after the savepoint was established. + (To do that, see .) Destroying a savepoint when + it is no longer needed may allow the system to reclaim some resources + earlier than transaction end. + + + + RELEASE also destroys all savepoints that were + established after the named savepoint was established. + + + + + Parameters + + + + savepoint_name + + + The name of the savepoint to destroy. + + + + + + + + Notes + + + Specifying a savepoint name that was not previously defined is an error. + + + + It is not possible to release a savepoint when the transaction is in + aborted state. + + + + If multiple savepoints have the same name, only the one that was most + recently defined is released. + + + + + + Examples + + + To establish and later destroy a savepoint: + +BEGIN; + INSERT INTO table VALUES (3); + SAVEPOINT my_savepoint; + INSERT INTO table VALUES (4); + RELEASE my_savepoint; +COMMIT; + + The above transaction will insert both 3 and 4. + + + + + Compatibility + + + RELEASE is fully conforming to the SQL standard. + + + + + See Also + + + + + + + + + + + + diff --git a/doc/src/sgml/ref/rollback.sgml b/doc/src/sgml/ref/rollback.sgml index f7e5e9fa28..53b7af3dd1 100644 --- a/doc/src/sgml/ref/rollback.sgml +++ b/doc/src/sgml/ref/rollback.sgml @@ -1,5 +1,5 @@ @@ -90,6 +90,7 @@ ROLLBACK; + diff --git a/doc/src/sgml/ref/rollback_to.sgml b/doc/src/sgml/ref/rollback_to.sgml new file mode 100644 index 0000000000..e38c4f4c4f --- /dev/null +++ b/doc/src/sgml/ref/rollback_to.sgml @@ -0,0 +1,163 @@ + + + + + ROLLBACK TO + SQL - Language Statements + + + + ROLLBACK TO + roll back to a savepoint + + + + ROLLBACK TO + + + + savepoints + rolling back + + + + +ROLLBACK TO savepoint_name + + + + + Description + + + Roll back all commands that were executed after the savepoint was + established. The savepoint remains valid and can be rolled back to + again later, if needed. + + + + ROLLBACK TO implicitly destroys all savepoints that + were established after the named savepoint. + + + + + Parameters + + + + savepoint_name + + + The savepoint to roll back to. + + + + + + + + Notes + + + Use to + destroy a savepoint without discarding the effects of commands executed + after it was established. + + + + Specifying a savepoint name that has not been established is an error. + + + + Cursors have somewhat non-transactional behavior with respect to + savepoints. Any cursor that is opened inside the savepoint is not closed + when the savepoint is rolled back. If a cursor is affected by a + FETCH command inside a savepoint that is later rolled + back, the cursor position remains at the position that FETCH + left it pointing to (that is, FETCH is not rolled back). + A cursor whose execution causes a transaction to abort is put in a + can't-execute state, so while the transaction can be restored using + ROLLBACK TO, the cursor can no longer be used. + + + + + Examples + + + To undo the effects of the commands executed after my_savepoint + was established: + +ROLLBACK TO my_savepoint; + + + + + Cursor positions are not affected by savepoint rollback: + +BEGIN; + +DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2; + +SAVEPOINT foo; + +FETCH 1 FROM foo; + ?column? +---------- + 1 + +ROLLBACK TO foo; + +FETCH 1 FROM foo; + ?column? +---------- + 2 + +COMMIT; + + + + + + + + Compatibility + + + This command is fully SQL standard conforming. + + + + + See Also + + + + + + + + + + + + diff --git a/doc/src/sgml/ref/savepoint.sgml b/doc/src/sgml/ref/savepoint.sgml new file mode 100644 index 0000000000..b881191c43 --- /dev/null +++ b/doc/src/sgml/ref/savepoint.sgml @@ -0,0 +1,152 @@ + + + + + SAVEPOINT + SQL - Language Statements + + + + SAVEPOINT + define a new savepoint within the current transaction + + + + SAVEPOINT + + + + savepoints + defining + + + + +SAVEPOINT savepoint_name + + + + + Description + + + SAVEPOINT establishes a new savepoint within + the current transaction. + + + + A savepoint is a special mark inside a transaction that allows all commands + that are executed after it was established to be rolled back, restoring + the transaction state to what it was at the time of the savepoint. + + + + + Parameters + + + + savepoint_name + + + The name to give to the new savepoint. + + + + + + + + Notes + + + Use to + rollback to a savepoint. Use to destroy a savepoint, keeping + the effects of commands executed after it was established. + + + + Savepoints can only be established when inside a transaction block. + There can be multiple savepoints defined within a transaction. + + + + + Examples + + + To establish a savepoint and later undo the effects of all commands executed + after it was established: + +BEGIN; + INSERT INTO table VALUES (1); + SAVEPOINT my_savepoint; + INSERT INTO table VALUES (2); + ROLLBACK TO my_savepoint; + INSERT INTO table VALUES (3); +COMMIT; + + The above transaction will insert the values 1 and 3, but not 2. + + + + To establish and later destroy a savepoint: + +BEGIN; + INSERT INTO table VALUES (3); + SAVEPOINT my_savepoint; + INSERT INTO table VALUES (4); + RELEASE my_savepoint; +COMMIT; + + The above transaction will insert both 3 and 4. + + + + + Compatibility + + + SQL requires a savepoint to be destroyed automatically when another + savepoint with the same name is established. In + PostgreSQL, the old savepoint is kept, though only the more + recent one will be used when rolling back or releasing. (Releasing the + newer savepoint will cause the older one to again become accessible to + ROLLBACK TO and RELEASE.) + Other than that, SAVEPOINT is fully SQL conforming. + + + + + See Also + + + + + + + + + + + + diff --git a/doc/src/sgml/ref/start_transaction.sgml b/doc/src/sgml/ref/start_transaction.sgml index 5cecbf7556..1a7bc363ce 100644 --- a/doc/src/sgml/ref/start_transaction.sgml +++ b/doc/src/sgml/ref/start_transaction.sgml @@ -1,5 +1,5 @@ @@ -66,6 +66,7 @@ START TRANSACTION + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 5230e07119..23164a57c7 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,5 +1,5 @@ @@ -120,9 +120,12 @@ PostgreSQL Reference Manual ¬ify; &prepare; &reindex; + &releaseSavepoint; &reset; &revoke; &rollback; + &rollbackTo; + &savepoint; &select; &selectInto; &set; -- cgit v1.2.1