Почему в базе данных PostgreSQL сохраняются незакоммиченные изменения?

Question

[Phys_Math_Man]

cur.execute("DROP TABLE IF EXISTS users")
cur.execute("""CREATE TABLE IF NOT EXISTS users (
                id INTEGER )""")
cur.execute("INSERT INTO users VALUES (0);  COMMIT; INSERT INTO users VALUES (1)")
cur.execute("INSERT INTO users VALUES (2)")

В результате выполнения кода (здесь указан фрагмент, содержащий все сделанные запросы) в БД оказываются и те изменения, которые идут после COMMIT. Почему так?

Answer 1

[Владимир Коротенко]

BEGIN начинает блок транзакции, то есть обозначает, что все операторы после команды BEGIN и до явной команды COMMIT или ROLLBACK будут выполняться в одной транзакции. По умолчанию (без BEGIN) Postgres Pro выполняет транзакции в режиме «autocommit» (автофиксация), то есть каждый оператор выполняется в своей отдельной транзакции, которая неявно фиксируется в конце оператора (если оператор был выполнен успешно; в противном случае транзакция откатывается).

https://postgrespro.ru/docs/postgrespro/10/sql-begin

Comments

[Phys_Math_Man]

Дело в том, что автокоммит у меня, как раз, отключен.

[Владимир Коротенко]

Phys_Math_Man,

Приведите версию и то как вы автокоммит задесайблили.

[Phys_Math_Man]

Владимир Коротенко,
psycopg2-binary 2.9.7
postgreSQL 16.0
Python 3.11.3

import psycopg2
from config import host, user, password

try:
    con = psycopg2.connect(
        host = host,
        user = user,
        password = password,
        database = "postgres",
    )
    cur = con.cursor()

    print(con.autocommit)

    cur.execute("DROP TABLE IF EXISTS users")
    cur.execute("""CREATE TABLE IF NOT EXISTS users (
                id INTEGER )""")
    cur.execute("INSERT INTO users VALUES (0); INSERT INTO users VALUES (1)")
    cur.execute("INSERT INTO users VALUES (2)")

except BaseException as error:
    print(error.__class__, error)

finally:
    if not cur.closed:
        cur.close()
        con.close()
        print("Соединение с PostgreSQL закрыто")

[Владимир Коротенко]

Phys_Math_Man, ЯП обеспечивают транзакционность, только тогда вы пользуетесь средствами языка.

try catch это не обеспечивает.

Вам нужно в try начать транзакцию
в катче сбросить ее
а в финаллу завершить

[Phys_Math_Man]

Владимир Коротенко, Вы бы не могли объяснить, что происходит в этом коде? Где там стоят автокоммиты и автобегины и почему?

[Владимир Коротенко]

Phys_Math_Man,
любая такая конструкция это транзакция на уровне базы данных.
cur.execute("DROP TABLE IF EXISTS users")

НО
cur.execute("BEGIN")
произвольный блок комманд
cur.execute("COMMIT")

Это выполнится в 1 транзакции

[Phys_Math_Man]

Владимир Коротенко, если бы любой вызов execute был самостоятельной транзакцией, то вышенаписанный код сохранял бы изменения, которые в нем происходят, разве нет? Потому что в этом коде, как раз, идет последовательный execute с пишущими запросами 4 раза, то есть, по вашей версии, происходит четыре транзакции. Почему тогда выполнение кода никак не влияет на базу данных?

[Владимир Коротенко]

Phys_Math_Man, я таймингов не видел.
вы приводите РАЗНЫЙ код.
Опишите точно что у вас происходит

[Phys_Math_Man]

Владимир Коротенко, меня интересовала конкретно то, что содержится в первоначальном вопросе. Вот, например, синтаксис sqlite3 мне понятен. А в psycopg2 мне непонятно. Вот я в середине строки, между запросами, в методе execute прописал COMMIT (как тут один умник заметил, это весьма по-дурацки, и я с ним согласен, но это всего лишь в учебных целях). Почему после выполнения скрипта у меня в базу данных сохранились также и те изменения, которые идут после COMMIT? Сразу хочу сказать, что autocommit == False.

Answer 2

[Сергей Соловьев]

Абсолютно каждое выражение в оборачивается в транзакцию. Даже одиночное.

Каждый раз вызывая execute ты создаешь новую транзакцию - ты сейчас делаешь не одну транзакцию, а 4. Вручную все оберни в BEGIN...COMMIT

Comments

[Phys_Math_Man]

Ничего не понял, честно говоря. Если ты думаешь, что у меня включен автокоммит, то это не так. Вот этот код

cur.execute("DROP TABLE IF EXISTS users")
cur.execute("""CREATE TABLE IF NOT EXISTS users (
                id INTEGER )""")
cur.execute("INSERT INTO users VALUES (0); INSERT INTO users VALUES (1)")
cur.execute("INSERT INTO users VALUES (2)")

не сохраняет в базе данных никаких изменений. И показывает, что autocommit == False.

[Сергей Соловьев]

Очень сложно судить о логике, когда не видишь окружения. Это просто отрывок кода, я не вижу что вокруг него

[Phys_Math_Man]

Сергей Соловьев,

import psycopg2
from config import host, user, password

try:
    con = psycopg2.connect(
        host = host,
        user = user,
        password = password,
        database = "postgres",
    )
    cur = con.cursor()

    print(con.autocommit)

    cur.execute("DROP TABLE IF EXISTS users")
    cur.execute("""CREATE TABLE IF NOT EXISTS users (
                id INTEGER )""")
    cur.execute("INSERT INTO users VALUES (0); INSERT INTO users VALUES (1)")
    cur.execute("INSERT INTO users VALUES (2)")

except BaseException as error:
    print(error.__class__, error)

finally:
    if not cur.closed:
        cur.close()
        con.close()
        print("Соединение с PostgreSQL закрыто")

[mayton2019]

Возможно автор раньше работал на Ораклах. А там не было умолчательного авто-коммита.

Он мог быть реализоват только в JDBC-драйвере при условии что флажок autoCommit включен
либо в средах разработки если вы так настроили.

А консоль по дефолту ожидала явного commit/rollback. Это кстати одна из самых первых
ошибок что начианющие ораклисты делают. Забывают об изоляции.

[Phys_Math_Man]

mayton2019, Вы можете объяснить, что происходит в данном коде?

[mayton2019]

Phys_Math_Man, мне сложно комментировать код, который я хотел-бы переписать.
Я-бы предложил так.

connection.autocommit = false
....
здесь ты делаешь свои DML операции и в конце даешь явный коммит как бы фиксируя пачку.

connection.commit()

Комментировать то что ты написал я не хочу. Мне кажется что оно не совсем корректно.
Я-бы избегал таких миксов COMMIT внутри строки. Это полюбому не production use-case
и такое не проходит code-review.

А про авто-коммит тебе в самом начале уже написали. Я согласен. Вот так оно и работает.
Смирись с этим.

[mayton2019]

Phys_Math_Man, главное что он не проходит code review.

Приведи его в соответствие и задавай вопрос.

[Сергей Соловьев]

Phys_Math_Man, во-первых, не надо говорить подобным тоном (ты сам не знаешь, твой продакшн мне не интересен), надо проявлять уважение.
Во-вторых,

Я ведь про синтаксис спрашивал.

Нет, ты спрашивал почему сохраняются все записи

В-третьих, если бы ты почитал документацию, то сразу бы понял почему происходит автокоммит.

Answer 3

[Константин Цветков]

How psycopg2 autocommit works?
То есть когда con.autocommit = False транзакция начинается до cur.execute и закрывается после, а не внутри.

Comments

[Phys_Math_Man]

Почему тогда этот код ничего не коммитит?

import psycopg2
from config import host, user, password

try:
    con = psycopg2.connect(
        host = host,
        user = user,
        password = password,
        database = "postgres",
    )
    cur = con.cursor()

    print(con.autocommit)

    cur.execute("DROP TABLE IF EXISTS users")
    cur.execute("""CREATE TABLE IF NOT EXISTS users (
                id INTEGER )""")
    cur.execute("INSERT INTO users VALUES (0); INSERT INTO users VALUES (1)")
    cur.execute("INSERT INTO users VALUES (2)")

except BaseException as error:
    print(error.__class__, error)

finally:
    if not cur.closed:
        cur.close()
        con.close()
        print("Соединение с PostgreSQL закрыто")