将 PostgreSQL 中的列中的“identity标志”移除。

Question

将 PostgreSQL 中的列中的“identity标志”移除。

4

我有一些表格在PostgreSQL 12.9中被声明为类似以下代码：

-- This table is written in old style
create table old_style_table_1 (
    id bigserial not null primary key,
    ...
);

-- This table uses new feature
create table new_style_table_2 (
    id bigint generated by default as identity,
    ...
);

第二个表格似乎是使用在第10版本中引入的identity标识进行声明的。

时间流逝，我们已经对旧表进行了分区，同时保留了原始序列：

CREATE TABLE partitioned_old_style_table_1 (LIKE old_style_table_1 INCLUDING DEFAULTS) PARTITION BY HASH (user_id);
CREATE TABLE partitioned_new_style_table_2 (LIKE new_style_table_2 INCLUDING DEFAULTS) PARTITION BY HASH (user_id);

他们的 id 列的数据定义语言（DDL）似乎是 id bigint default nextval('old_style_table_1_id_seq') not null 和 id bigint default nextval('new_style_table_2_id_seq') not null。

到目前为止，一切都运作良好。分区表证明是一个巨大的福音，我们决定通过删除它们来淘汰旧表。

DROP TABLE old_style_table_1, new_style_table_2;
-- [2BP01] ERROR: cannot drop desired object(s) because other objects depend on them 
-- Detail: default value for column id of table old_style_table_1 depends on sequence old_style_table_1_id_seq
-- default value for column id of table new_style_table_2 depends on sequence new_style_table_2_id_seq

在一番思考后，我发现在 Postgres 中，序列可能拥有所有者，因此我选择更改它们：

ALTER SEQUENCE old_style_table_1_id_seq OWNED BY partitioned_old_style_table_1.id;
DROP TABLE old_style_table_1;
-- Worked out flawlessly

ALTER SEQUENCE new_style_table_2_id_seq OWNED BY partitioned_new_style_table_2.id;
ALTER SEQUENCE new_style_table_2_id_seq OWNED BY NONE;
-- Here's the culprit of the question:
-- [0A000] ERROR: cannot change ownership of identity sequence

所以，显然这个列的 pg_attribute.attidentity 被设置为 'd'，这使我不能：

• 改变该列的默认值:

ALTER TABLE new_style_table_2 ALTER COLUMN id SET DEFAULT 0;
-- [42601] ERROR: column "id" of relation "new_style_table_2" is an identity column

• 删除默认值:

ALTER TABLE new_style_table_2 ALTER COLUMN id DROP DEFAULT;
-- [42601] ERROR: column "id" of relation "new_style_table_2" is an identity column
-- Hint: Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.

• 放弃标识、列或整个表（新表已依赖序列）：

ALTER TABLE new_style_table_2 ALTER COLUMN id DROP IDENTITY IF EXISTS;
-- or
ALTER TABLE new_style_table_2 DROP COLUMN id;
-- or
DROP TABLE new_style_table_2;
-- result in
-- [2BP01] ERROR: cannot drop desired object(s) because other objects depend on them 
-- default value for column id of table partitioned_new_style_table_2 depends on sequence new_style_table_2_id_seq

我查阅了文档，它提供了设置 SET IDENTITY 或 ADD IDENTITY 的方法，但没有提供取消或更改为一次性序列的方法，而不是试图删除现有序列。

➥ 那么，我如何从列-序列对中删除身份标识，以便它不会影响使用此序列的其他表？

更新：在本地主机上运行了UPDATE pg_attribute SET attidentity='' WHERE attrelid=16816;，仍然收到[2BP01]和[0A000]错误信息。:/

尽管我设法执行了DROP DEFAULT值部分，但似乎走到了死胡同。

- Xobotun

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Laurenz Albe · Accepted Answer

4

我认为没有一种安全且受支持的方法可以在不进行目录修改的情况下完成（操作）。幸运的是，序列没有任何特殊之处，因此删除它们不会成为问题。所以请停机一段时间，并执行以下操作：

移除使用自增序列的默认值
记录当前序列的值
删除表格
创建一个带有适当START值的新序列
使用新序列设置新的默认值

如果需要标识列，应该在分区表上定义，而不是在其中一个分区上定义。

- Laurenz Albe

叹气，我想那是唯一简单的方法了，对吧。我试图避免停机时间，并且也许在迁移中创建一个序列，但是 START 似乎只接受文字，不接受 SELECT nextval('old_sequence') 这样的语句。我不喜欢手动操作生产数据库的想法，但我也没有访问 pg_catalog 的权限，所以...好吧，谢谢。:D - Xobotun

1

只需一些小的努力，停机时间就可以缩短到几秒钟。创建一个新序列，起始值比当前序列的值高100000左右，逐个更改列默认值，然后删除旧序列。 - Laurenz Albe

考虑到我们使用64位的BIGINT，10^5的增量与其10^19的范围相比微不足道。尽管如此，我们的部署实践非常严格，这意味着新版本需要在测试环境中“沉睡”一两天，以收集性能数据并记录新的错误。由于现在和迁移应用于生产之间的时间跨度，冒险出现不一致的ID似乎比停机五分钟更糟糕。至少我是这么认为的，在这个项目上也是如此。 :D 谢谢，我今天会与团队讨论这个问题。也许我们会将这些未使用的表截断直到某个停机时间... - Xobotun

1

你说的很有道理。我只是想指出，如果真的很重要，那么停机时间是可以避免的。 - Laurenz Albe