Почему PostgreSQL автоматически выполняет ROLLBACK, когда я вызываю COMMIT после перехваченного исключения? (Также наблю ⇐ Python

[Anonymous]

Я разрабатываю приложение Python на PolarDB для PostgreSQL (совместимая с PostgreSQL база данных Alibaba Cloud, основанная на PostgreSQL 16 с архитектурой общего хранилища). Я столкнулся с запутанным поведением транзакций и подтвердил, что это также происходит в PostgreSQL 16 (сборка REL_16_STABLE), так что это, похоже, основная конструкция PostgreSQL.
Минимальный воспроизводимый пример:

Код: Выделить всё

import psycopg2

conn = psycopg2.connect("dbname=mydb")
conn.autocommit = False
cur = conn.cursor()

cur.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name TEXT NOT NULL)")
conn.commit()

# --- Start a new transaction ---
cur.execute("INSERT INTO users VALUES (1, 'Alice')")       # Step 1: OK

try:
cur.execute("INSERT INTO users VALUES (2, NULL)")       # Step 2: NOT NULL violation
except psycopg2.IntegrityError as e:
print(f"Caught: {e}")
# I caught it — shouldn't the program continue normally?

cur.execute("INSERT INTO users VALUES (3, 'Charlie')")     # Step 3: BOOM
conn.commit()

Чего я ожидал:
Я поймал исключение из шага 2 в Python try/Exception. Я ожидал, что шаг 3 завершится успешно, а функция commit() сохранит строки 1 и 3.
Что произошло на самом деле:

Код: Выделить всё

Caught: null value in column "name" of relation "users" violates not-null constraint

psycopg2.errors.InFailedSqlTransaction:
current transaction is aborted, commands ignored until end of transaction block

Шаг 3 не выполнен. Если я оберну его в другую попытку/исключение и вызову conn.commit(), PostgreSQL молча выполнит ROLLBACK — даже строка 1 («Алиса») будет потеряна. Вызов commit() не возвращает ошибок в некоторых драйверах, что позволяет легко пропустить тихую потерю данных.
Мои вопросы:

• Почему перехват исключения Python не «исправляет» транзакцию? Python try/Exception перехватывает объект IntegrityError, но транзакция на стороне сервера все еще находится в прерванном состоянии. Являются ли это двумя полностью независимыми конечными автоматами?
• Почему COMMIT автоматически становится ROLLBACK? Я явно попросил совершить фиксацию. PostgreSQL преобразует его в откат, не вызывая ошибки. Это похоже на источник скрытой потери данных — это задумано?
• Каков правильный шаблон для решения этой проблемы? Я обнаружил, что SAVEPOINT может помочь:

  Код: Выделить всё

  cur.execute("INSERT INTO users VALUES (1, 'Alice')")
  
  cur.execute("SAVEPOINT sp1")
  try:
  cur.execute("INSERT INTO users VALUES (2, NULL)")
  except psycopg2.IntegrityError:
  cur.execute("ROLLBACK TO SAVEPOINT sp1")
  
  cur.execute("INSERT INTO users VALUES (3, 'Charlie')")
  conn.commit()
  # Result: Alice and Charlie are saved!
  

Но это кажется многословным. Есть ли более чистый способ?
Дополнительный контекст — поведение PolarDB:
Я запускаю это на PolarDB для PostgreSQL, которая расширяет PostgreSQL архитектурой общего хранилища (один узел чтения-записи, несколько реплик только для чтения). Сначала я задавался вопросом, может ли репликация или общее хранилище PolarDB повлиять на это поведение, но тестирование подтвердило, что поведение идентично PostgreSQL. Это говорит мне о том, что это основной выбор дизайна PostgreSQL, а не что-то специфичное для PolarDB. Тем не менее, в режиме совместимости PolarDB с Oracle (polar_comp_stmt_level_tx) есть возможность включить откат на уровне инструкций, что полностью позволяет избежать этой проблемы, но сначала хотелось бы понять стандартное поведение PostgreSQL.
Среда: PolarDB для PostgreSQL (на основе PG 16), Python 3.11, psycopg2 2.9.9

Подробнее здесь: https://stackoverflow.com/questions/798 ... ht-excepti