为什么没有自动提交功能意味着所有查询都在PostgreSQL的事务中执行？ [英] Why does having no autocommit facility mean that all queries execute within a transaction in PostgreSQL?

问题描述

来自 https://wiki.postgresql.org/wiki/Psycopg2_Tutorial

PostgreSQL没有自动提交功能，这意味着所有
查询都将在事务内执行。

在事务内执行是一件好事，它可以确保数据
的完整性并允许适当的错误处理。但是，有
个查询无法在交易中运行。以下面的
为例。

Execution within a transaction is a very good thing, it ensures data integrity and allows for appropriate error handling. However there are queries that can not be run from within a transaction. Take the following example.

#/usr/bin/python2.4
#
#

import psycopg2

# Try to connect

try:
    conn=psycopg2.connect("dbname='template1' user='dbuser' password='mypass'")
except:
    print "I am unable to connect to the database."

cur = conn.cursor()
try:
    cur.execute("""DROP DATABASE foo_test""")
except:
    print "I can't drop our test database!"

此代码实际上会失败，并显示我不能
放下我们的代码测试数据库！ PostgreSQL无法在
事务中删除数据库，这是一个全有或全无的命令。 如果要删除
数据库，则需要更改数据库的隔离级别
，该操作可使用以下步骤完成。

This code would actually fail with the printed message of "I can't drop our test database!" PostgreSQL can not drop databases within a transaction, it is an all or nothing command. If you want to drop the database you would need to change the isolation level of the database this is done using the following.

conn.set_isolation_level(0)

您将上面的内容放在执行DROP DATABASE
的紧前面。

You would place the above immediately preceding the DROP DATABASE cursor execution.

我想知道为什么

• PostgreSQL没有自动提交功能，这意味着所有查询都将在事务内执行。

• "PostgreSQL does not have an autocommit facility which means that all queries will execute within a transaction."

PostgreSQL无法删除事务中的数据库

"PostgreSQL can not drop databases within a transaction"

如果要删除数据库，则需要更改隔离级别数据库

"If you want to drop the database you would need to change the isolation level of the database"

谢谢。

更新：
什么自动提交在postgresql和psycopg2中意味着什么？ ans我的问题吗？

Update: What does autocommit mean in postgresql and psycopg2? answer my question

推荐答案

这3个都与Python及其数据库连接器库有关，而与PostgreSQL本身无关：

All the 3 are related to Python and its DB connector library, not the PostgreSQL itself:

1. PostgreSQL具有自动提交，并且默认情况下处于活动状态-这意味着每个SQL语句都立即被执行。启动事务块时，将禁用自动提交模式，直到完成事务为止（通过COMMIT或ROLLBACK）


2. 销毁数据库的操作无法实现从事务块内部运行它。还请记住，与大多数其他数据库不同，PostgreSQL允许几乎所有DDL语句（显然不是DROP DATABASE语句）都可以在事务内执行。


3. 实际上，如果有人，则不能删除数据库（包括您）当前已连接到该数据库-隔离级别无关紧要，您仍然必须连接到另一个数据库（例如 postgres ）

1. PostgreSQL has an autocommit and it is active by default - which means that every SQL statement is immediately executed. When you start a transaction block, this autocommit mode is disabled until you finish the transaction (either by COMMIT or ROLLBACK)
2. The operation of destroying a database is implemented in a way where you can not run it from inside a transaction block. Also keep in mind that unlike most other databases PostgreSQL allows almost all DDL statements (obviously not the DROP DATABASE one) to be executed inside a transaction.
3. Actually you can not drop a database if anyone (including you) is currently connected to this database - so it does not matter what is your isolation level, you still have to connect to another database (e.g. postgres)

这篇关于为什么没有自动提交功能意味着所有查询都在PostgreSQL的事务中执行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！