PostgreSQL Upsert различает вставленные и обновленные строки с использованием системных столбцов XMIN, XMAX и других

Отказ от ответственности: теоретический вопрос.

Было задано несколько вопросов о том, как отличать вставленные и обновленные строки в инструкции PostgreSQL upsert.

Вот простой пример:

[email protected]=# create table t(i int primary key, x int);
[email protected]=# insert into t values(1,1);
[email protected]=# insert into t values(1,11),(2,22)
  on conflict(i) do update set x = excluded.i*11
  returning *, xmin, xmax;
╔═══╤════╤══════╤══════╗
║ i │ x  │ xmin │ xmax ║
╠═══╪════╪══════╪══════╣
║ 1 │ 11 │ 7696 │ 7696 ║
║ 2 │ 22 │ 7696 │    0 ║
╚═══╧════╧══════╧══════╝

Итак, xmax > 0 (или xmax= xmin) - строка обновлена; xmax= 0 - строка была вставлена.

IMO Непонятно объяснять значения столбцов xmin и xmax здесь.

Можно ли основывать логику на этих столбцах? Есть ли более существенное объяснение системных столбцов (кроме исходного кода)?

И, наконец, моя догадка о обновленных/вставленных строках?

Ответ 1

Я думаю, что это интересный вопрос, заслуживающий углубленного ответа; пожалуйста, воздержитесь от меня, если он немного длинный.

Вкратце: ваша догадка правильная, и вы можете использовать следующее предложение RETURNING, чтобы определить, была ли строка вставлена и не обновлена:

RETURNING (xmax = 0) AS inserted

Теперь подробное объяснение:

Когда строка обновляется, PostgreSQL не изменяет данные, а создает новую версию строки; старая версия будет удалена autovacuum, когда она больше не понадобится. Версия строки называется кортежем, поэтому в PostgreSQL может быть более одного набора строк в строке.

xmax выполняет две разные цели:

Как указано в документации, это может быть идентификатор транзакции транзакции, которая удалила (или обновила) кортеж ( "кортеж" - это другое слово для "строки" ). Только транзакции с идентификатором транзакции между xmin и xmax могут видеть кортеж. Старый код может быть удален безопасно, если транзакция с идентификатором транзакции меньше xmax.
xmax также используется для хранения блокировок строк. В PostgreSQL блокировки строк не хранятся в таблице блокировок, а в кортеже, чтобы избежать переполнения таблицы блокировок.
Если только одна транзакция имеет блокировку в строке, xmax будет содержать идентификатор транзакции транзакции блокировки. Если более одной транзакции имеет блокировку в строке, xmax содержит номер так называемого multixact, который представляет собой структуру данных, которая, в свою очередь, содержит идентификаторы транзакций транзакций блокировки.

Документация xmax не является полной, поскольку точное значение этого поля рассматривается как деталь реализации и не может быть понято без знания t_infomask кортежа, который не сразу отображается через SQL.

Вы можете установить модуль contrib pageinspect для просмотра этого и других полей кортежа.

Я запустил ваш пример, и это то, что я вижу, когда я использую функцию heap_page_items для изучения деталей (идентификационные номера транзакций в моем случае, конечно, разные):

SELECT *, ctid, xmin, xmax FROM t;

┌───┬────┬───────┬────────┬────────┐
│ i │ x  │ ctid  │  xmin  │  xmax  │
├───┼────┼───────┼────────┼────────┤
│ 1 │ 11 │ (0,2) │ 102508 │ 102508 │
│ 2 │ 22 │ (0,3) │ 102508 │      0 │
└───┴────┴───────┴────────┴────────┘
(2 rows)

SELECT lp, lp_off, t_xmin, t_xmax, t_ctid,
       to_hex(t_infomask) AS t_infomask, to_hex(t_infomask2) AS t_infomask2
FROM heap_page_items(get_raw_page('laurenz.t', 0));

┌────┬────────┬────────┬────────┬────────┬────────────┬─────────────┐
│ lp │ lp_off │ t_xmin │ t_xmax │ t_ctid │ t_infomask │ t_infomask2 │
├────┼────────┼────────┼────────┼────────┼────────────┼─────────────┤
│  1 │   8160 │ 102507 │ 102508 │ (0,2)  │ 500        │ 4002        │
│  2 │   8128 │ 102508 │ 102508 │ (0,2)  │ 2190       │ 8002        │
│  3 │   8096 │ 102508 │      0 │ (0,3)  │ 900        │ 2           │
└────┴────────┴────────┴────────┴────────┴────────────┴─────────────┘
(3 rows)

Значения t_infomask и t_infomask2 можно найти в src/include/access/htup_details.h. lp_off - это смещение данных кортежа на странице, а t_ctid - текущий идентификатор кортежа, который состоит из номера страницы и номера кортежа на странице. Поскольку таблица была вновь создана, все данные находятся на странице 0.

Позвольте мне обсудить три строки, возвращенные heap_page_items.

При указателе строки (lp) 1 мы находим старый обновленный кортеж. Первоначально он имел ctid = (0,1), но был изменен, чтобы содержать идентификатор кортежа текущей версии во время обновления. Tuple был создан транзакцией 102507 и недействителен транзакцией 102508 (транзакция, которая выдала INSERT ... ON CONFLICT). Этот кортеж больше не виден, а будет удален во время VACUUM.

t_infomask показывает, что оба xmin и xmax принадлежат совершенным транзакциям и, следовательно, показывают, когда кортежи были созданы и удалены. t_infomask2 показывает, что кортеж был обновлен с обновлением HOT (только для кучи), что означает, что обновленный кортеж находится на той же странице, что и исходный кортеж, и никакой индексный столбец не был изменен (см. src/backend/access/heap/README.HOT).
В указателе строки 2 мы видим новый обновленный кортеж, созданный транзакцией INSERT ... ON CONFLICT (транзакция 102508).

t_infomask показывает, что этот кортеж является результатом обновления, xmin действителен, а xmax содержит блокировку строки KEY SHARE (которая уже не актуальна после завершения транзакции). Эта блокировка строки была сделана во время обработки INSERT ... ON CONFLICT. t_infomask2 показывает, что это HOT-кортеж.
При указателе строки 3 мы видим вновь вставленную строку.

t_infomask показывает, что xmin действителен и xmax является недопустимым. xmax устанавливается в 0, потому что это значение всегда используется для вновь вставленных кортежей.

Таким образом, ненулевая xmax обновленной строки является артефактом реализации, вызванным блокировкой строки. Вполне возможно, что INSERT ... ON CONFLICT переопределяется за один день, так что это поведение меняется, но я думаю, что это маловероятно.