事务在 PostgreSQL 9.5.2 上自动提交，没有更改选项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37029513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transactions are auto committed on PostgreSQL 9.5.2 with no option to change it?
提问by Eyal Roth
I've just set up a new PostgreSQL 9.5.2, and it seems that all my transactions are auto committed.
我刚刚设置了一个新的 PostgreSQL 9.5.2，似乎我所有的事务都是自动提交的。
Running the following SQL:
运行以下 SQL：
CREATE TABLE test (id NUMERIC PRIMARY KEY);
INSERT INTO test (id) VALUES (1);
ROLLBACK;
results in a warning:
导致警告：
WARNING: there is no transaction in progress
ROLLBACK
on a differenttransaction, the following query:
在不同的事务上，以下查询：
SELECT * FROM test;
actually returns the row with 1(as if the insert was committed).
实际上返回行1（就像插入已提交）。
I tried to set autocommitoff, but it seems that this feature no longer exists (I get the unrecognized configuration parametererror).
我试图autocommit出发，但似乎此功能不再存在（我收到unrecognized configuration parameter错误消息）。
What the hell is going on here?
这到底是怎么回事？
回答by a_horse_with_no_name
autocommit in Postgres is controlled by the SQL client, not on the server.
Postgres 中的自动提交由 SQL客户端控制，而不是在服务器上。
In psqlyou can do this using
在psql你可以使用这样做
\set AUTOCOMMIT off
Details are in the manual:
http://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-VARIABLES
详细信息在手册中：http:
//www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-VARIABLES
In that case everystatement you execute starts a transaction until you run commit(including selectstatements!)
在这种情况下，您执行的每个语句都会启动一个事务，直到您运行commit（包括select语句！）
Other SQL clients have other ways of enabling/disabling autocommit.
其他 SQL 客户端有其他启用/禁用自动提交的方法。
Alternatively you can use beginto start a transaction manually.
或者，您可以使用begin手动启动事务。
http://www.postgresql.org/docs/current/static/sql-begin.html
http://www.postgresql.org/docs/current/static/sql-begin.html
psql (9.5.1)
Type "help" for help.
postgres=> \set AUTCOMMIT on
postgres=> begin;
BEGIN
postgres=> create table test (id integer);
CREATE TABLE
postgres=> insert into test values (1);
INSERT 0 1
postgres=> rollback;
ROLLBACK
postgres=> select * from test;
ERROR: relation "test" does not exist
LINE 1: select * from test;
^
postgres=>
回答by Tal Benami
\set AUTCOMMIT 'off';
The off valueshould be in single quotes
本off value应该是单引号
回答by Femsy
This should work. \set AUTOCOMMIToff. See the example below.
这应该有效。\set AUTOCOMMIT离开。请参阅下面的示例。
account_dept=# \set AUTOCOMMIT off
account_dept=# update account set ACCT_BALANCE= acct_balance + 200 WHERE ACCT_NUM=1;
UPDATE 1
account_dept=# rollback;
