Как в PostgreSQL 9.3.4 возможно появление дублирующихся записей в таблицах с уникальным ограничением?

Question

[Михаил Бесчётнов]

Прямо беда.
Было и на предыдущих версиях, но в данный момент последняя доступная из ветки 9.3.
Суть в следующем. Есть таблица (без триггеров, внешних ключей и правил). На таблицу наложено уникальное ограничение (по одному полю, но в других проблемных таблицах было по нескольким).
Время от времени (раз в несколько недель) в таблицах внезапно обнаруживаются дублирующиеся строки, которые не должны были там оказаться в силу наличия уникального ограничения.
Причём есть основания полагать, про происходит это именно при вставке записи, т.к. к одному из полей привязан sequence, и он в дубликатах "свежий", т.е. именно при вставке записи как будто пробивается ограничение.
Сталкивался ли кто с чем-то подобным?

DDL:

CREATE TABLE "public"."test" (
"remoteId" int8,
"title" text NOT NULL,
"localId" int4 DEFAULT nextval('"test_localId_seq"'::regclass) NOT NULL,
CONSTRAINT "uniqueRemoteId" UNIQUE ("remoteId")
)
WITH (OIDS=FALSE)
;

Дубликаты, соответственно, в стиле:

15,Название,8
15,Название,259

Comments

[Александр]

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

[Михаил Бесчётнов]

Дописал

Answer 1

[Михаил Бесчётнов]

Беда (наверное) в том, что репликация отсутствует.
Работа с этой таблицей происходит (на запись) следующим образом.
Раз в минут 10 в неё происходит запись нескольких тысяч значений. Практически все значения в ней уже на момент записи есть, поэтому уникальное ограничение работает каждый раз практически для каждой вставки. И вставок таких за раз происходит тысячи две.
Если бы у нас отваливались триггеры (по идее) дубликатов было бы по количеству вставок, т.к., мягко говоря, много. А их, скажем, 2 строчки в неделю :)
Попробую подождать еще вариантов, а пока сделаю фиктивную нерабочую таблицу с ограничением и буду круглосуточно туда пытаться фигачить дубликаты.

Comments

[Dimitriy]

покажите \d таблицы с реальными данными, ваши данные
insert into test(remote_id, title, local_id) select rand(1000), 'Название', i from generate_series(1,100000) as i;
ERROR: duplicate key value violates unique constraint "unique_remote_id"
DETAIL: Key (remote_id)=(78) already exists.
Да, и на сколько помню отключать идентификатор объекта не рекомндуется без превичных ключей.

[Михаил Бесчётнов]

Простите, не понял :)
То, что duplicate key при вставке - это как раз нормально. Ненормально, что раз в несколько дней/недель несмотря на наличие ограничения оно пробивается (по другому не сказать) и возникает дубликат.
OID отключены по умолчанию при создании объектов.

[Dimitriy]

Теперь я не понял, т.е пробиваетя? Вы втавляете данные в таблицу, колонка remote_id содержит ограничение, что индекс должен быть уникальным, индекс не составной, то значение индекса няпрямую зависит от встявляемого значения.

# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
remote_id | bigint |
.....
Indexes:
"unique_remote_id" UNIQUE CONSTRAINT, btree (remote_id)

в вашем случае, при наличии дубликатов должно быть:
Indexes:
"idx" btree (remote_id) INVALID

[Михаил Бесчётнов]

Вас понял. Конкретно сейчас дубликатов нет (не рабочем сервере стараемся оперативно их убрать) проведу эксперимент и дам данные как только вновь их заметим.

[Dimitriy]

По воводу OIDS www.postgresql.org/docs/9.1/static/sql-createtable.html
Tip: The use of OIDS=FALSE is not recommended for tables with no primary key, since without either an OID or a unique data key, it is difficult to identify specific rows.

[Михаил Бесчётнов]

Спасибо, учтём. Впрочем, дубликаты появлялись на таблицах как с primary key, так и без.

[pi314]

@TerminusMKB Честно говоря, если только INSERT не происходят в несколько потоков / конкурирующих транзакций, то непонятно, зачем городить огород с констрэйнтами. В 99% случаев происходит двойная работа (key violation, за которым, очевидно, следует UPDATE).
Проиндексируйте RemoteId (лучше всего, как ПК), делайте первым UPDATE по этому ключу и только если он затронул 0 записей (т.е. ключ реально "новый"), делайте INSERT.

[Михаил Бесчётнов]

Несколько потоков вполне возможны, но разве это причина для несрабатывания ограничения? :)
Собственно, из-за конкуренции так и было сделано (да, INSERT/UPDATE).
Мы, вероятно, перепишем (т.к. иначе непонятно как решить проблему), но всё-таки хочется понять, что вообще происходит, и что приводит к дубликатам. Это же теряется весь смысл уникальных ограничений, если такой треш возможен (если предположить, что это баг PG).

[pi314]

@TerminusMKB Да нет, конечно же, не причина :) То, что Вы описываете, вообще, какая-то жесть запредельня. Но, с другой стороны, PG настолько распространен в продакшн, что трудно поверить в наличие доселе невыявленного бага такого уровня. Просто в качестве идеи: не самосборная ли версия PG / ОС / каких-нибудь библиотек? (У меня давным-давно был похожий мозголомательный косяк с Oracle, допиленным под CentOS... полнотекстовый индекс выдавал "среднюю температуру по больнице". Там все дело оказалось в динамически слинкованном парсере PDF, на который на трезвую голову даже подумать было невозможно. )

[Михаил Бесчётнов]

Да нет, всё из пакетов. Debian 7, PG из репозитория. Попробую дождаться появления дубликатов и выложу psql \d

[chmv]

Некропостить нехорошо, но и поднимать отдельную тему, неуверен что нужно. Есть простая таблица вида:
CREATE TABLE public.values (
id BIGSERIAL,
value VARCHAR(255) NOT NULL,
CONSTRAINT values_value_key UNIQUE(value),
CONSTRAINT values_pkey PRIMARY KEY(id)
) ;
т.е. строковое уникальное значение и его id. Сейчас, на таблицу с около 92млн строк, есть около 2000 неуникальных значений, которые повторяются от 2х до 6ти раз.
Работа с таблицей производнится одним клиентом, без транзакций, перед INSERT идет SELECT, т.е. если SELECT ничего не вернул, то идет INSERT. Первое из попавшихся дублирующихся значений имеет ID записией не подряд, остальные не смотрел ещё.
Я сперва подумал, что это у меня в глазах глючит, и символы одинаковые, а кода разные, но нет, построить ещё один, точно такой же, уникальный индекс по полю value невозможно из за дублирующихся записей. А тот что есть, он есть.
Версия 12.3.

Я бы ещё смог как-то понять глюк, если бы это были просто последовательные INSERT, но перед INSERT всегда есть ещё SELECT. Т.е. получается, что при каких-то условиях, postgres видит не всю таблицу. Ладно, возможно, если бы условия были серьезнее, множество клиентов, репликации... но тут! Один клиент! Который долбит базу: "дай ID для значения ххх", и если вернулся пустой набор, то "вставь значение ххх и верни его ID", и при такой простейшей логике postgresql допустил появление дублирующихся записей. Неоднократно.

Answer 2

[Timosha]

нарывался пару раз
в одном случае возникло из-за некорректного завершения работы базы, переносил индексы из одного tablespace в другой, кончилось место в xlog, база скрэшилась, после не стартовала, пришлось делать pg_xlogreset (или как там эта утилитка называется), индексы побились немного и ограничение уникальности не всегда стало срабатывать
а в другом случае обнаружил случайно, списал на какие-то изменения в минорных версиях postgresql, либо битые индексы

Comments

[Михаил Бесчётнов]

Второй случай это интересно :)