DBA: вычищаем клон-записи из таблицы без PK
Случаются ситуации, когда в таблицу без первичного ключа или какого-то другого уникального индекса по недосмотру попадают полные клоны уже существующих записей.
Например, пишутся в PostgreSQL COPY-потоком значения хронологической метрики, а потом внезапный сбой, и часть полностью идентичных данных приходит повторно.
Как избавить базу от ненужных клонов?
Когда PK не помощник
Самый простой способ — вообще не допустить возникновения такой ситуации. Например, накатить-таки PRIMARY KEY. Но это возможно не всегда без увеличения объема хранимых данных.
Например, если точность исходной системы выше, чем точность поля в БД:
metric | ts | data -------------------------------------------------- cpu.busy | 2019-12-20 00:00:00 | cpu.busy | 2019-12-20 00:00:01 | cpu.busy | 2019-12-20 00:00:01 | cpu.busy | 2019-12-20 00:00:03 |
Заметили? Отсчет вместо 00:00:02 записался в базу с ts на секунду раньше, но остался вполне валидным с прикладной точки зрения (ведь значения data — разные!).
Конечно, можно сделать PK(metric, ts) — но тогда мы будем получать конфликты вставки для валидных данных.
Можно сделать PK(metric, ts, data) — но это сильно увеличит его объем, которым мы и пользоваться-то не будем.
Поэтому самый правильный вариант — сделать обычный неуникальный индекс (metric, ts) и разбираться с проблемами постфактум, если они все-таки возникнут.
«Война клоническая началась»
Случилась какая-то авария, и теперь нам предстоит уничтожить клон-записи из таблицы.
Давайте смоделируем исходные данные:
CREATE TABLE tbl(k text, v integer); INSERT INTO tbl VALUES ('a', 1) , ('a', 3) , ('b', 2) , ('b', 2) -- oops! , ('c', 3) , ('c', 3) -- oops!! , ('c', 3) -- oops!! , ('d', 4) , ('e', 5) ;Тут у нас трижды дрогнула рука, залип Ctrl+V, и вот…
Сначала давайте поймем, что таблица у нас может быть очень немаленькой, поэтому после того, как мы найдем все клоны, нам желательно буквально «тыкать пальцем», чтобы удалять конкретные записи без повторного их поиска.
И такой способ есть — это адресация по ctid, физическому идентификатору конкретной записи.
То есть, прежде всего, нам надо собрать ctid записей в разрезе полного контента строки таблицы. Самый простой вариант — скастовать всю строку в text:
SELECT T::text , array_agg(ctid) ctids FROM tbl T GROUP BY 1; t | ctids --------------------------------- (e,5) | (d,4) | (c,3) | (b,2) | (a,3) | (a,1) |
А можно ли не кастовать?
В принципе — можно в большинстве случаев. Пока вы не начнете использовать в этой таблице поля типов без оператора равенства:
CREATE TABLE tbl(k text, v integer, x point); SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T; -- ERROR: could not identify an equality operator for type tbl Ага, сразу видим, что если в массиве оказалось больше одной записи — это все и есть клоны. Давайте оставим только их:
SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T;unnest ------ (0,6) (0,7) (0,4)Любителям писать покороче
Можно написать и вот так:
SELECT unnest((array_agg(ctid))[2:]) FROM tbl T GROUP BY T::text;Поскольку само значение сериализованной строки нам неинтересно, то мы его просто выкинули из возвращаемых столбцов подзапроса.
Осталось всего немного — заставить DELETE использовать полученный нами набор:
DELETE FROM tbl WHERE ctid = ANY(ARRAY( SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T )::tid[]);
[посмотреть на explain.tensor.ru]
Да, все правильно: наши 3 записи отобрались за единственный Seq Scan всей таблицы, а Delete-узел использовал для поиска данных однократный проход с помощью Tid Scan:
-> Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1) TID Cond: (ctid = ANY ($0))Проверим для таблицы побольше и с большим количеством дублей:
TRUNCATE TABLE tbl; INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z , (random() * 100)::integer v -- 0..99 FROM generate_series(1, 10000) i;
[посмотреть на explain.tensor.ru]
Итак, способ успешно работает, но применять надо с известной осторожностью. Потому что на каждую удаляемую запись приходится одно чтение страницы данных в Tid Scan, и одно — в Delete.
- базы данных
- dba
- explain
- explain.tensor.ru
- sql antipatterns
- sql tips and tricks
- чистка дублей
- Блог компании Тензор
- PostgreSQL
- SQL
- Администрирование баз данных
PostgreSQL Antipatterns: уникальные идентификаторы
Достаточно часто у разработчика возникает потребность формировать для записей таблицы PostgreSQL некие уникальные идентификаторы — как при вставке записей, так и при их чтении.
Таблица счетчиков
Казалось бы — чего проще? Заводим отдельную табличку, в ней — запись со счетчиком. Надо получить новый идентификатор — читаем оттуда, чтобы записать новое значение — делаем UPDATE …
Так делать не надо! Потому что завтра же вам придется решать проблемы:
- постоянных пересекающихся блокировок при UPDATE
см. PostgreSQL Antipatterns: сражаемся с ордами «мертвецов» - постепенной деградации скорости доступа к данным таблицы счетчиков
см. PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой - … и необходимости ее зачистки при активных транзакциях, которые будут вам мешать
см. DBA: когда пасует VACUUM — чистим таблицу вручную
Объект SEQUENCE
Для таких задач в PostgreSQL предусмотрена отдельная сущность — SEQUENCE . Она нетранзакционна, то есть не вызывает блокировок, но две «параллельные» транзакции заведомо получат разные значения.
Чтобы получить следующий ID из последовательности, достаточно воспользоваться функцией nextval :
SELECT nextval('seq_name'::regclass);Иногда необходимо получить сразу несколько ID — для потоковой записи через COPY, например. Использовать для этого setval(currval() + N) — в корне неправильно! По той простой причине, что между вызовами «внутренней» ( currval ) и «внешней» ( setval ) функций конкурирующая транзакция могла изменить текущее значение последовательности. Корректный способ — вызвать nextval нужное количество раз:
SELECT nextval('seq_name'::regclass) FROM generate_series(1, N);Псевдотип serial
В «ручном» режиме с последовательностями работать не очень удобно. Но ведь типовая задача у нас — обеспечить вставку новой записи с новым sequence-ID! Специально для этой цели в PostgreSQL придуман псевдотип serial , который при генерации таблицы «разворачивается» во что-то типа id integer NOT NULL DEFAULT nextval(‘tbl_id_seq’) .
Запоминать имя автоматически сгенерированной и привязанной к полю последовательности — не надо, для этого есть функция pg_get_serial_sequence(table_name, column_name) . Эту же функцию можно использовать в собственных DEFAULT -подстановках — например, если есть необходимость сделать общую последовательность на несколько таблиц сразу.
Однако, поскольку работа с последовательностью нетранзакционна, если идентификатор из нее получала rollback’нувшаяся транзакция, то в сохраненных записях таблицы последовательность ID окажется «дырявой».
GENERATED-столбцы
Начиная с PostgreSQL 10, появилась возможность объявления идентифицирующего столбца ( GENERATED AS IDENTITY ), соответствующего стандарту SQL:2003. В варианте GENERATED BY DEFAULT поведение эквивалентно serial , а вот с GENERATED ALWAYS все интереснее:
CREATE TABLE tbl( id integer GENERATED ALWAYS AS IDENTITY );INSERT INTO tbl(id) VALUES(DEFAULT); -- Запрос успешно выполнен: одна строка изменена за 10 мс. INSERT INTO tbl(id) VALUES(1); -- ERROR: cannot insert into column "id" -- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS. -- HINT: Use OVERRIDING SYSTEM VALUE to override.Да, чтобы вставить конкретное значение «поперек» такого столбца, придется приложить дополнительные усилия с помощью OVERRIDING SYSTEM VALUE :
INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1); -- Запрос успешно выполнен: одна строка изменена за 11 мс.Заметьте, что теперь у нас в таблице два одинаковых значения id = 1 — то есть GENERATED не накладывает дополнительных UNIQUE-условий и индексов, а является исключительно декларацией, равно как и serial .
В общем случае, на современных версиях PostgreSQL использование serial не рекомендуется с предпочтительной заменой его на GENERATED . Кроме, разве что, ситуации поддержки кросс-версионных приложений, работающих с PG ниже 10.
Генерируемый UUID
Все хорошо, пока вы работаете в рамках одного экземпляра БД. Но когда их несколько, адекватного способа синхронизации последовательностей не существует (впрочем, это не мешает «неадекватно» их синхронизировать, если очень хочется). Тут на помощь приходит тип UUID и функции генерации значений для него. Я обычно использую uuid_generate_v4() как наиболее «случайную».
Скрытые системные поля
tableoid/ctid
Иногда при выборке записей из таблицы требуется как-то адресоваться к конкретной «физической» записи, или узнать, из какой конкретной секции была получена та или иная запись при обращении к «родительской» таблице при использовании наследования.
В этом случае нам помогут скрытые системные поля, присутствующие в каждой записи:
- tableoid хранит oid -идентификатор таблицы — то есть tableoid::regclass::text дает имя конкретной таблицы-секции
- ctid — «физический» адрес записи в формате (,)
oid
Вплоть до PostgreSQL 11 существовала возможность объявить при создании таблицы атрибут WITH OIDS :
CREATE TABLE tbl(id serial) WITH OIDS;Каждая запись этой таблицы получала дополнительное скрытое поле oid с глобально-уникальным значением в рамках БД — как это организовано для системных таблиц вроде pg_class , pg_namespace ,…
При вставке записи в такую таблицу генерируемое значение возвращается сразу с результатом запроса:
INSERT INTO tbl(id) VALUES(DEFAULT);Запрос успешно выполнен: строка с OID 16400 добавлена за 11 мс. Такое поле невидимо при «обычном» запросе таблицы:
SELECT * FROM tbl;id -- 1Его, как и остальные системные поля надо запрашивать в явном виде:
SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;tableoid | ctid | xmin | xmax | cmin | cmax | oid | id --------------------------------------------------------- 16596 | (0,1) | 572 | 0 | 0 | 0 | 16400 | 1Правда, значение oid имеет всего 32 бита, поэтому весьма несложно получить переполнение, после которого даже создать никакую таблицу (ей нужен новый oid !) не удастся. Поэтому, начиная с PostgreSQL 12, WITH OIDS более не поддерживается.
«Честное» время clock_timestamp
Иногда при длительном выполнении запроса или процедуры хочется привязать к записи «текущее» время. Неудача ждет того, кто попытается для этого использовать функцию now() — она возвратит одно и то же значение в рамках всей транзакции.
Чтобы получить «вот прямо текущее» время, существует функция clock_timestamp() (и еще пучок ее собратьев). Чем отличается поведение этих функций можно увидеть на примере простого запроса:
SELECT now() , clock_timestamp() FROM generate_series(1, 4); now | clock_timestamp -------------------------------+------------------------------- 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03
- Блог компании Тензор
- PostgreSQL
- Программирование
- SQL
- Администрирование баз данных
Ctid postgresql что это
В каждой таблице есть несколько системных столбцов, неявно определённых системой. Как следствие, их имена нельзя использовать в качестве имён пользовательских столбцов. (Заметьте, что это не зависит от того, является ли имя ключевым словом или нет; заключение имени в кавычки не поможет избежать этого ограничения.) Эти столбцы не должны вас беспокоить, вам лишь достаточно знать об их существовании.
Идентификатор объекта (object ID) для строки. Этот столбец присутствует, только если таблица была создана с указанием WITH OIDS , или если в момент её создания была установлена переменная конфигурации default_with_oids. Этот столбец имеет тип oid (с тем же именем, что и сам столбец); подробнее об этом типе см. Раздел 8.18. tableoid
Идентификатор объекта для таблицы, содержащей строку. Этот столбец особенно полезен для запросов, имеющих дело с иерархией наследования (см. Раздел 5.9), так как без него сложно определить, из какой таблицы выбрана строка. Связав tableoid со столбцом oid в таблице pg_class , можно будет получить имя таблицы. xmin
Идентификатор (код) транзакции, добавившей строку этой версии. (Версия строки — это её индивидуальное состояние; при каждом изменении создаётся новая версия одной и той же логической строки.) cmin
Номер команды (начиная с нуля) внутри транзакции, добавившей строку. xmax
Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этого столбца может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована, или удаление было отменено. cmax
Номер команды в удаляющей транзакции или ноль. ctid
Физическое расположение данной версии строки в таблице. Заметьте, что хотя по ctid можно очень быстро найти версию строки, значение ctid изменится при выполнении VACUUM FULL . Таким образом, ctid нельзя применять в качестве долгосрочного идентификатора строки. Для идентификации логических строк лучше использовать OID или даже дополнительный последовательный номер.
Коды OID представляют собой 32-битные значения и выбираются из единого для всей СУБД счётчика. В больших или долгоживущих базах данных этот счётчик может пойти по кругу. Таким образом, не рекомендуется рассчитывать на уникальность OID, если только вы не обеспечите её дополнительно. Если вам нужно идентифицировать строки таблицы, настоятельно рекомендуется использовать последовательности. Однако можно использовать и коды OID, при выполнении следующих условий:
Когда для идентификации строк таблиц применяется OID, в каждой такой таблице должно создаваться ограничение уникальности для столбца OID. Когда такое ограничение уникальности (или уникальный индекс) существует, система позаботится о том, чтобы OID новой строки не совпал с уже существующими. (Конечно, это возможно, только если в таблице меньше 2 32 (4 миллиардов) строк, а на практике таблицы должны быть гораздо меньше, иначе может пострадать производительность системы.)
Никогда не следует рассчитывать, что OID будут уникальны среди всех таблиц; в качестве глобального идентификатора в рамках базы данных используйте комбинацию tableoid и OID строки.
Идентификаторы транзакций также являются 32-битными. В долгоживущей базе данных они могут пойти по кругу. Это не критично при правильном обслуживании БД; подробнее об этом см. Главу 23. Однако полагаться на уникальность кодов транзакций в течение длительного времени (при более чем миллиарде транзакций) не следует.
Идентификаторы команд также 32-битные. Это создаёт жёсткий лимит на 2 32 (4 миллиарда) команд SQL в одной транзакции. На практике это не проблема — заметьте, что это лимит числа команд SQL , а не количества обрабатываемых строк. Кроме того, идентификатор получают только те команды, которые фактически изменяют содержимое базы данных.
| Пред. | Наверх | След. |
| 5.3. Ограничения | Начало | 5.5. Изменение таблиц |
PostgreSQL — Системные столбцы
Каждая таблица имеет несколько системных столбцов , которые неявно определяются системой. Поэтому эти имена нельзя использовать в качестве имен пользовательских столбцов. (Обратите внимание, что эти ограничения не зависят от того, является ли имя ключевым словом или нет; заключение имени в кавычки не позволит вам обойти эти ограничения.) На самом деле вам не нужно беспокоиться об этих столбцах; просто знать, что они существуют.
- tableoid OID таблицы, содержащей эту строку. Этот столбец особенно удобен для запросов, которые выбирают из секционированных таблиц или иерархий наследования, поскольку без него трудно сказать, из какой отдельной таблицы взята строка. Их tableoid можно объединить со oid столбцом pg_class для получения имени таблицы.
- xmin Идентификатор (идентификатор транзакции) транзакции вставки для этой версии строки. (Версия строки — это отдельное состояние строки; каждое обновление строки создает новую версию строки для той же логической строки.)
- cmin Идентификатор команды (начиная с нуля) в транзакции вставки.
- xmax Идентификатор (идентификатор транзакции) удаляющей транзакции или ноль для восстановленной версии строки. Этот столбец может быть ненулевым в версии с видимой строкой. Обычно это указывает на то, что транзакция удаления еще не зафиксирована или попытка удаления была отменена.
- cmax Идентификатор команды в удаляющей транзакции или ноль.
- ctid Физическое расположение версии строки в ее таблице. Обратите внимание, что, хотя ctid можно использовать для очень быстрого поиска версии строки, строка ctid изменится, если она будет обновлена или перемещена с помощью VACUUM FULL . Поэтому ctid бесполезен в качестве долгосрочного идентификатора строки. Для идентификации логических строк следует использовать первичный ключ.
Идентификаторы транзакций также являются 32-битными величинами. В долгоживущих базах данных идентификаторы транзакций могут повторяться. Это не является фатальной проблемой при соответствующих процедурах технического обслуживания. Однако неразумно полагаться на уникальность идентификаторов транзакций в долгосрочной перспективе (более одного миллиарда транзакций).
Идентификаторы команд также являются 32-битными величинами. Это создает жесткое ограничение в 2 32 (4 миллиарда) SQL -команд в рамках одной транзакции. На практике это ограничение не является проблемой — обратите внимание, что ограничение на количество команд SQL , а не на количество обрабатываемых строк. Кроме того, только команды, которые фактически изменяют содержимое базы данных, будут использовать идентификатор команды.