Удаление дублей в Greenplum

Как-то я писал пост про удаление полных дублей из таблицы.
В обычных СУБД можно разделить полностью одинаковые строки по скрытому системному полю, например, по rowid (Oracle), ctid (PostgreSQL).

И кстати хочу сделать небольшое дополнение к этим полям, они вообще-то немного разные:
ROWID - это физический адрес строки в базе данных.
CTID - это физический адрес строки в конкретной таблице.

——-
Greenplum, как известно, это MPP система. И по сути, под капотом параллельно работаем несколько инстансов PostgreSQL.

И недавно у меня была задача по удалению дублей из Greenplum-a. И я сделал очевидную глупую ошибку. Решил рассказать об этом вам, чтоб вы не допустили 😊

Так вот, допустим есть таблица sandbox.test_duplicate с полями (sale_id, product_id, sale_date, amount и тд). В таблице лежат тысячи строк и среди них есть немного дублей. Надо просто удалить дублирующие строки и оставить только уникальные.
Набросали быстренько скрипт

delete from sandbox.test_duplicate -- удаляем дубли по системному id строки
--select t.*, ctid from sandbox.test_duplicate t -- можно сначала посмотреть что удаляем
where ctid in
(
select ctid
from (
select t.*, row_number() over (partition by sale_id order by sale_date) as rn, ctid -- нумеруем и выводит физ. адрес строки
from sandbox.test_duplicate t
where sale_id in
(select sale_id -- выбираем только строки с дублями
from sandbox.test_duplicate
group by sale_id
having count(sale_id) > 1)
) t
where rn > 1) -- выбираем дубли ctid. rn = 1 оставим, а остальное удалим
Удаляем строки и идем пить чай 🍷 Задача выполнена🙌

Но на самом деле нет💀 Выясняется, что удалены все строки. Например было два sale_id=1, sale_id=1. Должна остаться только одна строка, но пропали две.
Или что еще хуже удалены вообще рандомные другие строки. А может все и правильно отработало.
Я потестил на разном количестве - и это рандом. На маленьком количестве строк может все и правильно отработать. На большом количестве точно будут неверные удаления. Сейчас объясню.

Проблема в запросе в том, что используется ctid в Greenplum без указания gp_segment_id. В Greenplum ctid уникален только в пределах одного сегмента, а не всей таблицы. Это приводить к непредсказуемым результатам и удалению всех строк.

То есть ctid разных сегментов конечно же могут пересекаться

Правильный скрипт будет такой:


DELETE FROM sandbox.test_duplicate
WHERE (ctid, gp_segment_id) IN (
SELECT ctid, gp_segment_id
FROM (
SELECT ctid, gp_segment_id,
ROW_NUMBER() OVER (PARTITION BY sale_id ORDER BY sale_date) AS rn
FROM sandbox.test_duplicate
WHERE sale_id IN (
SELECT sale_id
FROM sandbox.test_duplicate
GROUP BY sale_id
HAVING COUNT(*) > 1
)
) t
WHERE rn > 1
);
COMMIT;

А вообще можно глянуть на какие сегменты размазана таблица:

-- Для вашей таблицы
SELECT
gp_segment_id,
COUNT(*) as row_count
FROM sandbox.test_duplicate
GROUP BY gp_segment_id
ORDER BY gp_segment_id;

-- Количество активных сегментов
SELECT COUNT(*) as total_segments
FROM gp_segment_configuration
WHERE role = 'p' AND content >= 0;
-- Фильтры в запросе WHERE role = 'p' AND content >= 0 отбирают только активные первичные сегменты Greenplum.


Вроде супер очевидно, но я на этом попался. И удалил лишнее. Важно это все заметить на тестовой среде.

Кстати вчера у дата инженеретта был пост про удаление дублей в ClickHouse. Тоже чекните, интересно 👍

Кто-нибудь еще знает подобные тонкости по работе с разными СУБД?
В комменты закину скрипт создания таблицы. Можете тоже потестить⬇️

it пингвин | data engineer 🐧