错误:在 Postgres 中发现了不止一个拥有的序列 [英] ERROR: more than one owned sequence found in Postgres

问题描述

我正在为 Patient 表的现有列设置身份列.
这里我想使用 GENERATED ALWAYS AS IDENTITY.

I'm setting up a identity column to my existing columns for the Patient table.
Here I would like to use GENERATED ALWAYS AS IDENTITY.

所以我使用以下语句设置标识列(以前它是 serial):

So I setup the identity column by using the following statement (previously it was serial):

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

对于现有的患者表，我总共有 5 条记录.(patientId 1 到 5)
当我在身份设置后插入一条新记录时，它会抛出如下错误:

For the existing patient table I have a total of 5 records. (patientId 1 to 5)
When I insert a new record after the identity setup, it will throw an error like:

more than one owned sequence found

即使在重置身份列后，我仍然遇到同样的错误.

Even after resetting the identity column, I still get the same error.

ALTER TABLE Patient ALTER COLUMN PatientId RESTART WITH 6;

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

Let me know if you have any solutions.

推荐答案

更新: 此错误已在 PostgreSQL v12 中通过提交 19781729f78.
答案的其余部分与旧版本相关.

Update: This bug has been fixed in PostgreSQL v12 with commit 19781729f78.
The rest of the answer is relevant for older versions.

serial 列有一个属于该列的序列和一个获得净序列值的 DEFAULT 值.

A serial column has a sequence that is owned by the column and a DEFAULT value that gets the net sequence value.

如果您尝试将该列更改为标识列，您将收到一个错误，指出该列已经有一个默认值.

If you try to change that column into an identity column, you'll get an error that there is already a default value for the column.

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

Now you must have dropped the default value, but not the sequence that belongs to the serial column. Then when you converted the column into an identity column, a second sequence owned by the column was created.

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

Now when you try to insert a row, PostgreSQL tries to find and use the sequence owned by the column, but there are two, hence the error message.

我认为这是 PostgreSQL 中的一个错误:在我看来，它应该重新调整标识列的现有序列的用途，或者给您一个错误，表明该列已经拥有一个序列，您应该算了吧.我会努力修复这个错误.

I'd argue that this is a bug in PostgreSQL: in my opinion, it should either have repurposed the existing sequence for the identity column or given you an error that there is already a sequence owned by the column, and you should drop it. I'll try to get this bug fixed.

同时，您应该手动删除 serial 列中留下的序列.运行以下查询:

Meanwhile, you should manually drop the sequence left behind from the serial column. Run the following query:

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 列留下的序列的名称.删除它，标识列应该按预期运行.

That should give you the name of the sequence left behind from the serial column. Drop it, and the identity column should behave as desired.

这篇关于错误:在 Postgres 中发现了不止一个拥有的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！