错误：在Postgres中找到多个所有序列

Question

我正在为Patient表的现有列设置身份列。
在这里，我想使用GENERATED ALWAYS AS 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

即使重置了标识列，我仍然收到相同的错误。

ALTER TABLE Patient ALTER COLUMN PatientId RESTART WITH 6;

如果您有任何解决方案，请告诉我。

- Nayan Rudani

---

在你修改之前，patientid是否被定义为“serial”？ - user330315

是的，它之前被定义为串行。 - Nayan Rudani

Answer 1

更新：这个错误已在PostgreSQL v12的提交19781729f78中得到修复。
答案的其余部分与旧版本相关。

一个serial列有一个序列，该序列由列拥有并且具有一个获取净序列值的DEFAULT值。

如果你试图将该列更改为标识列，你会收到一个错误，即该列已经有了默认值。

现在，您必须删除默认值，但未删除属于serial列的序列。然后，当您将该列转换为标识列时，会创建第二个序列，该序列由该列拥有。

现在，当您尝试插入一行时，PostgreSQL会尝试查找和使用该列拥有的序列，但存在两个序列，因此会出现错误消息。

我认为这是PostgreSQL中的一个错误: 我的意见是，它应该重用标识列的现有序列，或者给出一个错误，即该列已经拥有一个序列，您应该删除它。我会试着修复这个错误。

同时，您需要手动删除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列中遗留下来的序列名称。删除它，身份列应该会按预期工作。

- Laurenz Albe

---

谢谢Laurenz，这正是发生在我身上的事情。你可以在最后一行加上完整的答案：DROP SEQUENCE index_name;。 - Rutrus

@Rutrus 我没有添加 DROP SEQUENCE 语句，因为序列的名称会有所不同。但是我的回答的最后一句话告诉你要删除序列，不是吗？ - Laurenz Albe

我仍然认为它应该被回溯。此外，在pgadmin中，有一个建议可以摆脱其中一个，这比运行复杂的查询要容易得多。 但是，如果删除其中一个（明显的）标识列后，pgadmin将不允许您保存。还需要考虑PR的价值。（运行11.12，所以并不过时） - Jan

继续上面的内容：查询促使我删除了最旧的序列。 然后我尝试插入一行省略了id，显然应该从序列中添加。我将开始值增加到第一个可用的id值。我收到一个错误，说id 1已经被使用，然后是id 2，一直到正确的id。 显然，缓存值没有设置为有用的值。 就我而言，这再次表明：回溯修复！ - Jan

@Jan 我明白你的意思。但是你需要写信到pgsql-hacker列表，让Peter Eisentraut或其他开发人员参与进来。最好等待14.2左右，以让人们有信心一个后补丁不会太冒险。 - Laurenz Albe

Answer 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),
)

我很感谢Laurenz Albe上面发布的答案！正如他所解释的那样，只需删除用于串行默认值的序列，这种疯狂就会消失，表格再次变得正常。

- Wellspring

---

一样的问题，谢谢你写出来，一开始并不明显 :) - Bastien Durel

Answer 3

再次声明，这不是一个答案，但是评论功能不允许我添加足够的文本。 抱歉。接着我的早前评论。 这是我执行的操作，我认为手动修复不足以解决问题，并且对于大型表格，我使用的重复技巧（见下文）可能是不切实际的，而且有可能会采用已删除行的 ID，从而导致错误。

-- 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

- Jan