PostgreSQL off Auto-commit

1. Brief description

we know that the Sqlplus inside Oracle executes DML statements; commit commits are required; if it is wrong, you can roll back the rollback; however, the default is auto-commit in PostgreSQL, and submit it immediately after execution. Is there any way to avoid this risk?  Of course, in Psql, the default is to turn on auto-commit, we can turn off auto-commit.

2. Operation Verification

[email protected] ~]$ psql-u lottu-d XZFB psql (9.5.0) Type ' help ' for help.   xzfb=> CREATE TABLE lottu01 (id int); CREATE TABLE xzfb=> SELECT * from lottu01; ID ---- (0 rows)   xzfb=> INSERT INTO lottu01 values (1001); INSERT 0 1 xzfb=> rollback xzfb->; Warning:there is no transaction in progress ROLLBACK xzfb=> SELECT * from lottu01; ID ------ 1001 (1 row)

 from the above operation; PostgreSQL is automatically submitted; how to close it; perform \set autocommit off 

[email protected] ~]$ psql-u lottu-d XZFB psql (9.5.0) Type ' help ' for help.   xzfb=> \set autocommit off xzfb=> INSERT INTO lottu01 values (1002); INSERT 0 1 xzfb=> commit; --1002 submit here COMMIT xzfb=> INSERT INTO lottu01 values (1003); INSERT 0 1 xzfb=> rollback; --1003 rolled back. ROLLBACK xzfb=> SELECT * from lottu01; ID ------ 1001 1002 (2 rows)

 

3. Automatic closing of submissions

If you manually set \set autocommit off after each entry psql, you can set this sentence to the. psqlrc file:

[

email protected] ~]$ cat. PSQLRC \set autocommit off [[email protected] ~]$

4. Notes

         But this is set up; there are also places that are not used to it. 1. After the operation error, execution of the correct DML statement will be error "Error:current transaction is aborted, commands ignored until end of transaction block"; Avoid execution comm it; or rollback

[email protected] ~]$ psql-u lottu-d XZFB psql (9.5.0) Type ' help ' for help.   xzfb=> INSERT INTO lottu01 values (' 1523 '); INSERT 0 1 xzfb=> INSERT INTO lottu01 values (' a1523 '); --Inserts a string . error:invalid input syntax for integer: "a1523" Line 1:insert to lottu01 values (' a1523 ');                                     ^ xzfb=> INSERT INTO lottu01 values (1523); --Executing the correct DML statement will error error:current transaction is aborted, commands ignored until end of transaction block xzfb=> rollback; --Avoid continuing the error below ROLLBACK xzfb=> INSERT INTO lottu01 values (1523); INSERT 0 1

2. Execute the DDL statement; Commit is also required, and of course you can rollback

xzfb=> CREATE TABLE t (id int); CREATE TABLE xzfb=> select * from T; ID ---- (0 rows)   xzfb=> \q [email protected] ~]$ psql-u lottu-d XZFB psql (9.5.0) Type ' help ' for help.   xzfb=> select * from T; error:relation "T" does not exist Line 1:select * from T;                       ^

3. Above in addition to set off automatic submission; we can also do it by begin end; Slightly

   

Author: li0924
Date: 2016-01-22
This article is copyrighted by the author, welcome reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to the original link.

PostgreSQL off Auto-commit