Блог

Главная - Вопросы по программированию - ОШИБКА: в Postgres найдено более одной принадлежащей последовательности

ОШИБКА: в Postgres найдено более одной принадлежащей последовательности

#postgresql #identity-column #database-sequence

#postgresql #идентификатор-столбец #database-sequence

Вопрос:

Я настраиваю столбец идентификаторов для своих существующих столбцов для Patient таблицы.
Здесь я хотел бы использовать СГЕНЕРИРОВАННЫЙ ВСЕГДА КАК ИДЕНТИФИКАТОР.

Поэтому я настраиваю столбец identity, используя следующий оператор (ранее это было serial ):

 ALTER TABLE Patient ALTER PatientId
   ADD GENERATED ALWAYS AS IDENTITY (START WITH 1);

Для существующей таблицы пациентов у меня всего 5 записей. ( patientId от 1 до 5)
Когда я вставляю новую запись после настройки идентификатора, она выдает ошибку типа:

 more than one owned sequence found

Даже после сброса столбца identity я все равно получаю ту же ошибку.

 ALTER TABLE Patient ALTER COLUMN PatientId RESTART WITH 6;

Дайте мне знать, если у вас есть какие-либо решения.

Комментарии:

1. Был ли определен patientid как serial до того, как вы его изменили?

2. да, ранее он был определен как последовательный

Ответ №1:

Обновление: эта ошибка была исправлена в PostgreSQL v12 с фиксацией 19781729f78.
Остальная часть ответа актуальна для более старых версий.

serial Столбец имеет последовательность, которая принадлежит столбцу, и DEFAULT значение, которое получает значение чистой последовательности.

Если вы попытаетесь изменить этот столбец на столбец идентификаторов, вы получите сообщение об ошибке, что для столбца уже есть значение по умолчанию.

Теперь вы, должно быть, удалили значение по умолчанию, но не последовательность, которая принадлежит serial столбцу. Затем, когда вы преобразовали столбец в столбец идентификаторов, была создана вторая последовательность, принадлежащая столбцу.

Теперь, когда вы пытаетесь вставить строку, PostgreSQL пытается найти и использовать последовательность, принадлежащую столбцу, но их две, отсюда и сообщение об ошибке.

Я бы сказал, что это ошибка в PostgreSQL: на мой взгляд, он должен либо изменить существующую последовательность для столбца identity, либо выдать вам сообщение об ошибке, что уже существует последовательность, принадлежащая столбцу, и вы должны ее удалить. Я постараюсь исправить эту ошибку.

Между тем, вы должны вручную удалить оставшуюся последовательность из serial столбца. Запустите следующий запрос:

 SELECT d.objid::regclass
FROM pg_depend AS d
   JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                             d.refobjsubid = a.attnum
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype <> 'i'
  AND a.attname = 'patientid'
  AND d.refobjid = 'patient'::regclass;

Это должно дать вам имя последовательности, оставшейся после serial столбца. Удалите его, и столбец identity должен вести себя так, как требуется.

Комментарии:

1. Как человек, совершенно новичок в postgres, я был более чем немного огорчен, увидев поле PK, указанное дважды (!!!!) при написании сценария для отображения таблицы в pgAdmin. Затем, чтобы одновременно обнаружить блог Uber, указывающий на ошибку, связанную с мертвыми кортежами… Я должен спросить… Сколько ошибок у меня впереди? Я рад использовать Postgres в будущем, но сегодняшний опыт, безусловно, охладил мой энтузиазм. Тем не менее, СПАСИБО за объяснение причины и решения. Теперь я могу видеть свое поле PK в списке (насколько это безумно?) только … один раз.

2. Ну, ошибки случаются повсюду, и это здесь просто неудобство (по этой причине оно было исправлено только в v12, а не исправлено). История Uber — другое дело. Они ругали PostgreSQL, не раскрывая подробностей о своей рабочей нагрузке. Маркус написал интересный анализ , в котором он пытался угадать, что происходит, и, похоже, это комбинация а) злоупотребления реляционной базой данных в качестве хранилища значений ключей и б) запуска большого количества обновлений, что является самым слабым местом PostgreSQL.

3. Да, я смотрел это и ни в коем случае не хотел узаконить все, что сказал Uber. Просто сочетание самостоятельного поиска ошибки и ссылки на ошибку Uber в течение 30 минут вызвало у меня некоторую дрожь. Я все еще очень рад продолжить работу с Postgres.

4. Что ж, вы нашли ошибку, которая была исправлена. Это, как правило, дает мне хорошее чувство.

5. С юмором я бы ответил, что мои клиенты чувствуют себя еще лучше, когда они не находят много ошибок недавнего происхождения, исправленных или других. Но если серьезно, я бы извинился, поскольку я, вероятно, столкнулся с поиском ошибок в этом обмене. Я никогда не хочу показаться неблагодарным за этот потрясающий инструмент OSS. Только начал смотреть это и понимаю, что вы один из многих, кому я обязан этой благодарностью, особенно в это время обучения. Спасибо за ваш вклад.

Ответ №2:

Это не ответ — извиняюсь, но это позволяет мне наглядно показать сумасшедшее поведение, которое я (непреднамеренно) обнаружил сегодня утром…

введите описание изображения здесь

Все, что мне нужно было сделать, это:

 alter TABLE db.generic_items alter column generic_item_id drop default;
alter TABLE db.generic_items alter column generic_item_id add generated by default as identity;

и теперь при написании сценария таблицы для SQL я получаю (сокращенно):

 CREATE TABLE db.generic_items
(
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_generic_items PRIMARY KEY (generic_item_id),
)

Я благодарен за ответ, опубликованный выше, Лауренсом Альбе! Как он объясняет, просто удалите последовательность, которая использовалась для последовательного значения по умолчанию, и это безумие исчезнет, и таблица снова будет выглядеть нормально.

Комментарии:

1. То же самое, спасибо, что написали, что это та же проблема, сначала это не было очевидно 🙂

Ответ №3:

Опять же, это НЕ ОТВЕТ, но комментарии не позволили мне добавить достаточно текста. Извинения. Продолжение моих предыдущих комментариев. Это то, что я выполнил, и это показывает, imo, что ручного исправления недостаточно, и с большими таблицами повторяющийся трюк, который я использовал (см. Ниже), был бы непрактичным и потенциально неправильным, поскольку принятие идентификатора, принадлежащего удаленной строке.

 -- pls disregard the absence of 2 id rows, this is the final situation    
d vaste_data.studie_type
                                  Table "vaste_data.studie_type"
     Column |         Type          | Collation | Nullable |             Default
    -------- ----------------------- ----------- ---------- ----------------------------------
     id     | integer               |           | not null | generated by default as identity
     naam   | character varying(25) |           | not null |
    Indexes:
        "pk_tstudytype_tstudytype_id" PRIMARY KEY, btree (id)
    Referenced by:
        TABLE "stuwadoors" CONSTRAINT "fk_t_stuwadoors_t_studytype" FOREIGN KEY (study_type_id) REFERENCES vaste_data.studie_type(id)
        TABLE "psux" CONSTRAINT "study_studytype_fk" FOREIGN KEY (studie_type_id) FOREIGN KEY (studie_type_id) REFERENCES vaste_data.studie_type(id)
    
    alter table vaste_data.studie_type alter column id drop default;
    ALTER TABLE
    alter table vaste_data.studie_type alter column id add generated by default as identity;
    ALTER TABLE
    -- I chose to show both sequences so I could try to drop either one.
    SELECT d.objid::regclass
    FROM pg_depend AS d
       JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                                 d.refobjsubid = a.attnum
    WHERE d.classid = 'pg_class'::regclass
      AND d.refclassid = 'pg_class'::regclass
      AND a.attname = 'id'
      AND d.refobjid = 'vaste_data.studie_type'::regclass;
                      objid
    -----------------------------------------
     vaste_data.studie_type_id_seq
     vaste_data.tstudytype_tstudytype_id_seq
    (2 rows)
    
    drop sequence vaste_data.studie_type_id_seq;
    ERROR:  cannot drop sequence vaste_data.studie_type_id_seq because column id of table vaste_data.studie_type requires it
    HINT:  You can drop column id of table vaste_data.studie_type instead.
    
    d vaste_data.studie_type_id_seq
                   Sequence "vaste_data.studie_type_id_seq"
      Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
    --------- ------- --------- ------------ ----------- --------- -------
     integer |     1 |       1 | 2147483647 |         1 | no      |     1
    Sequence for identity column: vaste_data.studie_type.id
    
    alter sequence vaste_data.studie_type_id_seq start 6;
    ALTER SEQUENCE
    drop sequence vaste_data.tstudytype_tstudytype_id_seq;
    DROP SEQUENCE
    insert into vaste_data.studie_type (naam) values('Overige leiding');
    ERROR:  duplicate key value violates unique constraint "pk_tstudytype_tstudytype_id"
    DETAIL:  Key (id)=(1) already exists.
    ...
    ERROR:  duplicate key value violates unique constraint "pk_tstudytype_tstudytype_id"
    DETAIL:  Key (id)=(5) already exists.
    insert into vaste_data.studie_type (naam) values('Overige leiding');
    INSERT 0 1