Комментарии 38
Всё началось с того, что PostgreSQL изначально писался на С/C++ и всегда тянуло к расширению стандартного SQL набора возможностей.
И звучит как-то кривовато, и совершенно непонятно, причем тут C/С++? Если бы писали на чем-то другом, то не тянуло бы?
Всё это хорошо (без иронии), но тут возникает философский вопрос. Вроде как с такими трудами уходили с MSSQL, Oracle, и тут привязываемся опять к конкретной бд. Да, вроде как на горизонте не видно альтернативы, но я бы аккуратно подходил к использованию нестандартных возможностей.
Как подсчитать, сколько строк вставилось, а сколько обновилось
А как будет выглядеть аналогичный фортель для MERGE?
xmax — особый системный столбец PostgreSQL. Если строка вставлена без предшествующего удаления, то xmax равен 0. При обновлении старый вариант строки удаляется (с новым xmin) и xmax старой версии получает непустое значение.
Если у вас была строка, у нее было xmin=100, xmax=0, вы ее обновили, у этой строки стало xmin=100, xmax=101, добавилась строка с xmin=101, xmax=0
Если вы просто выставили строку, то у вас в таблице появилась строка xmin=101, xmax=0
Ваша чудо-магия работает (если работает - я не проверял) на том что в случае обновления вам возвращают старую строку, только в ней xmax может быть !=0, а в таком случае и значения колонок в возвращаемой строке будут до обновления, вы уверены что все к этому готовы? В вашем объяснении я не нашел указания на это.
Если у вас была строка, у нее было xmin=100, xmax=0, вы ее обновили, у этой строки стало xmin=100, xmax=101, добавилась строка с xmin=101, xmax=0
Это невозможно. Постгресс не позволит в одном запросе обработать запись дважды, даже если источник данных этого требует.
Что вы имеете ввиду под "обработать дважды"?
Postgres работает как MVCC, у него внутри при редактировании всегда происходит закрытие старой строки простановкой xmax=номер_текущей_транзакции, и добавление новой строки с актуальными значениями и xmin=номер_текущей_транзакции, xmax=0.
Отсюда вывод, если вы ожидаете в ответе по своему запросу строку у которой xmin !=0, то это должна быть старая версия строки, до редактирования.
Где-то в моих рассуждениях ошибка?
Как я понял, вы говорите о случае, когда источник данных требует дважды обновить одну и ту же запись (только в этом случае ваши выкладки имеют смысл). То есть в нём присутствует две разные записи, которые вызывают конфликт с одной и той же записью изменяемой таблицы. Типа
WITH new_data AS (
SELECT * FROM (VALUES
(1, 'Ivan', 1000),
(2, 'Alisa', 800),
(1, 'Ivan', 1234) -- повторный дубликат по "id"
) AS t(id, name, salary)
)
INSERT INTO employees (id, name, salary)
SELECT id, name, salary FROM new_data
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
salary = EXCLUDED.salary
RETURNING id, name, salary, (xmax = 0) AS inserted;Постгресс в этом случае генерирует ошибку. Именно по причине MVCC. Кстати, с моей точки зрения это неправильно... ну или по крайней мере некорректно.
Ок, давайте по порядку:
1) я запустил ваш код из примера (поменял название колонки с name на first_name) и получил:
у меня пустая таблица, но мне сказали что я не вставляю а обновляю - кажется что-то уже идёт не так.
2) Если вы понимаете как работает MVCC подскажите пожалуйста, если у меня в таблице в Postgresql было 2 строки, я обновил одну из них (поменял значение в одной из колонок на другое допустимое), сколько после этого строчек в таблице?
у меня пустая таблица, но мне сказали что я не вставляю а обновляю - кажется что-то уже идёт не так.
Жаль, вы не указали, в каком именно месте вам это сказали и кто именно. Если обратиться к статье, то там предполагается наличие записи с id=1 в таблице employees до запуска запроса - как того, что в статье, так и соответственно показанного мной.
у меня в таблице в Postgresql было 2 строки, я обновил одну из них (поменял значение в одной из колонок на другое допустимое), сколько после этого строчек в таблице?
Точно так же, две. Или вы спрашиваете про физическое хранилище данных? там до вакуума - три.
Или вы спрашиваете про физическое хранилище данных? там до вакуума - три.
Именно, их там 3(не обязательно до ваккуума - зависит от горизонта транзакций и возможности внутристраничной очистки), и они выглядят там так:
неизменявшаяся строчка, xmin=1, xmax=0
старая версия строчки, xmin=1, xmax=2
новая версия строчки, xmin=2, xmax=0
Теперь смотрим на колонку в returning: xmax=0 as inserted. Какие из трех строчек дадут true в эту колонку? Кажется только старая версия строчки. Если так то это значит что остальные значения в колонках у записи имеющей inserted=true будут старые, до обновления (новые лежат в новой версии). Я где-то не прав?
Это возможно, потому что это буквально то как работает UPDATE (либо INSERT ON CONFLICT DO UPDATE) в Postgres.
А вот PostgreSQL с вами не согласен и генерирует ошибку
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
Вы сейчас спорите с голосами в своей голове. Здесь всё ещё обсуждается тот запрос, который написан в статье, а не тот, который вы придумали.
Смотрите, всё работает: https://dbfiddle.uk/vbVlF7m5
SELECTDISTINCTON
Спасибо!
Недавно полчаса на собесе потратил на вывод первого из группы, а оно вона как! И ведь знал когда-то, но забыл
Если знать оконные функции, эти все костыли с DISTINCT ON не нужны.
А оконные функции знать имхо явно полезнее, ибо они есть везде. А это чисто постгресовая фича
Угу... и именно что костыль - ибо сортировка финального набора далеко не всегда соответствует сортировке для отбора одной записи группы. И начинается заворачивание в подзапрос... забавно, что подавляющее большинство в этом случае не выносит подзапрос в CTE, по крайней мере поначалу.
Насколько мне известно, DISTINCT ON работает в разы быстрее, чем использование ROW_NUMBER. Поэтому важно учитывать возможности конкретной базы данных.
Для такой задачи знаю три диалектонезависимых способа для выбора последней записи:
Через подзапрос
SELECT field1, field2, history_field -- поле, где хранится историчность FROM table1 t1 WHERE t1.history_field = (SELECT max(history_field) FROM table1 WHERE field1 = t1.field1) AND field1 = 'some_value';Через оконные функции
WITH cte AS (SELECT field1, field2, history_field, RANK() OVER (PARTITION BY field1 ORDER BY history_field DESC) AS rnk FROM table1 WHERE 1=1 AND field1 = 'some_value') SELECT field1, field2, history_field FROM cte WHERE rnk = 1;Через левый джойн к самой себе
SELECT t1.field1,
t1.field2,
t1.history_field
FROM table1 t1
LEFT JOIN table1 t2 ON t1.field1 = t2.field1
AND t1.history_field > t2.history_field
WHERE t2.field1 IS NULL
AND t1.field1 = 'some_value'Теперь еще один знаю, но проприетарный для Postgres.
Все эти способы в моем понимании тождественны, но на разных базах (похоже, в зависмиости от объема таблиц и индексов) показываают разные результаты по быстродействию - иногда быстрее один, иногда другой. Гуру SQL разъясните, какой способ считается каноничным и наиболее предпочтителен к использованию?
Вот чисто из общих соображений.
Поскольку вы выбираете одну запись, у которой field1 = 'some_value', а не записи для всех значений группирующего поля, то первый запрос предпочтителен. Сортировка потребуется всем запросам. Но у второго - оверхед вычисления дополнительного поля, а третий очевидно потратит ещё больше ресурсов на ненужное связывание.
Если на каком-то массиве данных второй или третий запросы показывают значимо лучшую производительность, то это скорее всего следствие ошибки планировщика при построении плана первого запроса, вследствие устаревшей статистики, странных настроек цен или неудачного набора индексов.
А если нужно выбрать все группы по field1, то предпочтительнее будет аналог первого запроса, но с выносом подзапроса в CTE.
Это из общих соображений, в предположении, что данные достаточно равномерные, а индексация - подходящая. На реальных структурах и массивах данных может быть и по-другому.
эти три запроса не эквивалентны, если у нас
history_fieldне уникальнато первый и третий запросы не смогут вернуть одну запись, второй сможет, надо только RANK на row_number заменить
по перформансу, если на
history_fieldнавесить индекс, первый запрос можно неплохо ускорить (особенно с фишкой замены max на order by с limit 1), третий будет nested loop (без сортировки) , но который скорее всего даже медленне сортировки изза условияt1.history_field> t2.history_field которое индексом не ускорить
если у нас
history_fieldне уникальна
Ну вообще-то чисто из логики это либо порядковый номер, либо штамп времени события, так что уникальность в пределах группы должна обеспечиваться ограничением UNIQUE (field1, history_field).
хорошая теория, жаль с реальной жизнью не совпадает.
В реальности полно случаев когда бывают полные дубликаты и по порядковым номерам, и по штампам времени, и по чему угодно. И я даже могу сказать из-за чего. Из-за того что где раньше по потоку данных, тоже стоит такой алгоритм дедупликации - и вместо гарантированной одной записи, выдает теоретическую одну, а на практике когда две, а когда и больше.
В реальности полно случаев когда бывают полные дубликаты и по порядковым номерам, и по штампам времени, и по чему угодно.
Ну так это говорит скорее об ошибке проектирования. Логика-то требует уникальности, а ограничения нет.
Пошел тест PostgreSQL Advanced проходить. Бац, а там вопросы как из одного docker перенести файл в другой docker. Я не против таких вопросов, но блин, причем тут PostgreSQL??? Тогда б уж про COPY спрашивали. Файлик мне мой девопс перетащит. Самое интересное, что тест я прошел :) Как бы половина вопросов вообще не про PG, а я их прошел имея опыт ну самый начальный в девопс делах.
xmax в проде??? Вы разве не понимаете, что системные поля это предмет для не контролируемых изменений!?
Пять возможностей PostgreSQL, о которых редко вспоминают