postgresql - 从 PostgreSQL 中的列中删除 "identity flag"

Question

我在 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,
    ...
);

第二个表似乎是使用 identity flag 声明的在第 10 版中引入。

时间过去了，我们对旧表进行了分区，同时保留了原始序列:

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

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

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

我已经查找了 documentation ，它提供了 SET IDENTITY 或 ADD IDENTITY 的方法，但无法删除它或更改为一次性序列而不尝试删除现有序列。

➥ 那么，我怎样才能从列序列对中删除标识标志，这样它就不会影响使用该序列的其他表？

UPD:尝试在本地主机上运行 UPDATE pg_attribute SET attidentity='' WHERE attrelid=16816;，仍然收到 [2BP01] 和 [0A000]。 :/

虽然我设法执行了 DROP DEFAULT 值位，但它似乎是一个死胡同。

Answer 1

我认为没有一种安全且受支持的方法可以做到这一点(无需修改目录)。幸运的是，序列并没有什么特别之处会导致丢弃它们成为问题。因此，请稍作休息并:

• 删除使用标识序列的默认值

• 记录序列的当前值

• 放下 table

• 使用适当的START 值创建一个新序列

• 使用新序列设置新的默认值

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