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

Question

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

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

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

nd@postgres=# create table t(i int primary key, x int);
nd@postgres=# insert into t values(1,1);
nd@postgres=# 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 - строка была вставлена.

ИМО Не слишком понятно объяснил смысл xmin а также xmax колонны здесь.

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

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

Answer 1

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

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

RETURNING (xmax = 0) AS inserted

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

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

xmax служит двум различным целям:

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

2. xmax также используется для хранения блокировок строк. В PostgreSQL блокировки строк хранятся не в таблице блокировок, а в кортеже, чтобы избежать переполнения таблицы блокировок.
   Если только одна транзакция имеет блокировку строки, xmax будет содержать идентификатор транзакции блокировки. Если несколько строк имеют блокировку строки, xmax содержит номер так называемого мультиакта, который представляет собой структуру данных, которая, в свою очередь, содержит идентификаторы транзакций блокировки транзакций.

Документация 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,

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

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

2. В указателе строки 2 мы видим новый, обновленный кортеж, который был создан транзакцией INSERT ... ON CONFLICT (транзакция 102508).

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

3. В строке указатель 3 мы видим недавно вставленную строку.

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

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