Begin a transaction on a SAVEPOINT that is outside any transaction
От | Gurjeet Singh |
---|---|
Тема | Begin a transaction on a SAVEPOINT that is outside any transaction |
Дата | |
Msg-id | CABwTF4X6KL=OPY5EGJ3VZOoF+7Qo4NsQP_v2CoqiEppMzySjaA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Begin a transaction on a SAVEPOINT that is outside any transaction
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-hackers |
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/
В списке pgsql-hackers по дате отправления: