rename 对 sequence 的影响

关联列与 sequence 后，即 sequence 属于该列后，drop 表或列时会自动 drop 相关 sequence。

但如果对表或列 rename 后，甚至 rename sequence后，会发生什么呢？

我们来做一下实验。

创建测试表 tb_test_sequence_rename

alvindb=>

CREATE TABLE tb_test_sequence_rename (

    test_id BIGSERIAL PRIMARY KEY,

    create_time TIMESTAMP DEFAULT clock_timestamp()

);

CREATE TABLE

查看表与 sequence：

alvindb=> \d tb_test_sequence_rename

                                            Table "alvin.tb_test_sequence_rename"

   Column    |            Type             | Collation | Nullable |                         Default

-------------+-----------------------------+-----------+----------+----------------------------------------------------------

 test_id     | bigint                      |           | not null | nextval('tb_test_sequence_rename_test_id_seq'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_sequence_rename_test_id_seq

             Sequence "alvin.tb_test_sequence_rename_test_id_seq"

  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: alvin.tb_test_sequence_rename.test_id

alvindb=>

对表进行 rename ：

alvindb=> ALTER TABLE tb_test_sequence_rename RENAME TO tb_test_sequence_rename2;

ALTER TABLE

通过如下结果，我们可以看到， rename 表后 'Owned by' 也会随之自动变化。

alvindb=> \d tb_test_sequence_rename2

                                           Table "alvin.tb_test_sequence_rename2"

   Column    |            Type             | Collation | Nullable |                         Default

-------------+-----------------------------+-----------+----------+----------------------------------------------------------

 test_id     | bigint                      |           | not null | nextval('tb_test_sequence_rename_test_id_seq'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_sequence_rename_test_id_seq

             Sequence "alvin.tb_test_sequence_rename_test_id_seq"

  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: alvin.tb_test_sequence_rename2.test_id

alvindb=>

我们再 rename 列试一下，

alvindb=> ALTER TABLE tb_test_sequence_rename2 RENAME test_id TO test_id2;

ALTER TABLE

通过如下结果，我们可以看到， rename 列后 'Owned by' 也会随之自动变化。

alvindb=> \d tb_test_sequence_rename2

                                           Table "alvin.tb_test_sequence_rename2"

   Column    |            Type             | Collation | Nullable |                         Default

-------------+-----------------------------+-----------+----------+----------------------------------------------------------

 test_id2    | bigint                      |           | not null | nextval('tb_test_sequence_rename_test_id_seq'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id2)

alvindb=> \d tb_test_sequence_rename_test_id_seq

             Sequence "alvin.tb_test_sequence_rename_test_id_seq"

  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: alvin.tb_test_sequence_rename2.test_id2

alvindb=>

我们来 rename 一下 sequence，

alvindb=> ALTER SEQUENCE tb_test_sequence_rename_test_id_seq RENAME TO tb_test_sequence_rename_test_id_seq2;

ALTER SEQUENCE

通过如下结果，我们可以看到， rename sequence 后 'Owned by' 也会随之自动变化，并且 Default 中的 sequence 也会随之变化。

alvindb=> \d tb_test_sequence_rename2

                                            Table "alvin.tb_test_sequence_rename2"

   Column    |            Type             | Collation | Nullable |                          Default

-------------+-----------------------------+-----------+----------+-----------------------------------------------------------

 test_id2    | bigint                      |           | not null | nextval('tb_test_sequence_rename_test_id_seq2'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id2)

alvindb=> \d tb_test_sequence_rename_test_id_seq2

            Sequence "alvin.tb_test_sequence_rename_test_id_seq2"

  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: alvin.tb_test_sequence_rename2.test_id2

alvindb=>

通过以上三个 rename 实验，可以发现，正常 rename 不会对 sequence 的使用产生影响。

无论是 rename 表名，列名，还是 sequence 的名字，如我们所期望，PostgreSQL 都会智能地作出相应的修改。

复制表或迁移表时 sequence 的相关操作

以下表为例，

alvindb=> \d tb_test_bigserial

                                            Table "alvin.tb_test_bigserial"

   Column    |            Type             | Collation | Nullable |                      Default

-------------+-----------------------------+-----------+----------+----------------------------------------------------

 test_id     | bigint                      |           | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_bigserial_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_bigserial_test_id_seq

                Sequence "alvin.tb_test_bigserial_test_id_seq"

  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: alvin.tb_test_bigserial.test_id

在一些情况下，为避免 DDL 或大量 DML 对表 tb_test_bigserial 的影响，我们可以通过 RENAME 表的方式实现：

根据表 tb_test_bigserial 复制出相同表结构的表 tb_test_bigserial_new
对 tb_test_bigserial_new 进行 DDL 或大量 DML
将 tb_test_bigserial_new RENAME 回 tb_test_bigserial

根据表 tb_test_bigserial 复制出相同表结构的表 tb_test_bigserial_new：

alvindb=> CREATE TABLE tb_test_bigserial_new (LIKE tb_test_bigserial INCLUDING ALL);

CREATE TABLE

alvindb=> \d tb_test_bigserial_new

                                          Table "alvin.tb_test_bigserial_new"

   Column    |            Type             | Collation | Nullable |                      Default

-------------+-----------------------------+-----------+----------+----------------------------------------------------

 test_id     | bigint                      |           | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_bigserial_new_pkey" PRIMARY KEY, btree (test_id)

对 tb_test_bigserial_new 进行 DDL 或大量 DML 后，将 tb_test_bigserial_new RENAME 回 tb_test_bigserial：

alvindb=> BEGIN;

BEGIN

alvindb=> ALTER TABLE tb_test_bigserial RENAME TO tb_test_bigserial_old;

ALTER TABLE

alvindb=> ALTER TABLE tb_test_bigserial_new RENAME TO tb_test_bigserial;

ALTER TABLE

alvindb=> END;

COMMIT

这时，查看新表结构：

alvindb=> \d tb_test_bigserial

                                            Table "alvin.tb_test_bigserial"

   Column    |            Type             | Collation | Nullable |                      Default

-------------+-----------------------------+-----------+----------+----------------------------------------------------

 test_id     | bigint                      |           | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_bigserial_new_pkey" PRIMARY KEY, btree (test_id)

此处，我们只关注 sequence。上述的索引的名字可以根据需要决定是否需要 RENAME 回原来的名字。

查看 sequence，

alvindb=> \d tb_test_bigserial_test_id_seq

                Sequence "alvin.tb_test_bigserial_test_id_seq"

  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: alvin.tb_test_bigserial_old.test_id

从以上 'Owned by' 可以看出，sequence tb_test_bigserial_test_id_seq 还是归旧表 tb_test_bigserial_old 的列所有。

从上文“rename 对 sequence 的影响”我们知道，这是正常的。

此时 DROP 旧表，会提示新表 tb_test_bigserial 还在依赖 sequence tb_test_bigserial_test_id_seq。

alvindb=> DROP TABLE tb_test_bigserial_old;

ERROR:  cannot drop table tb_test_bigserial_old because other objects depend on it

DETAIL:  default value for column test_id of table tb_test_bigserial depends on sequence tb_test_bigserial_test_id_seq

HINT:  Use DROP ... CASCADE to drop the dependent objects too.

alvindb=>

以下 RENAME 表时关键的一步：

alvindb=> ALTER SEQUENCE tb_test_bigserial_test_id_seq OWNED BY tb_test_bigserial.test_id;

ALTER SEQUENCE

通过上述 SQL，sequence tb_test_bigserial_test_id_seq 就归新表的列所有了。

在日常操作中，我们有可能忘记修改 sequence 的所属关系。以致后来 DROP 老表时加了 CASCADE，将 sequence 也一起 DROP 掉了，从而引发问题。

此时 DROP 表就不报错了：

alvindb=> DROP TABLE tb_test_bigserial_old;

DROP TABLE

以下是 RENAME 后所期望的结果（注意 sequence 的 'Owned by'）：



alvindb=> \d tb_test_bigserial

                                            Table "alvin.tb_test_bigserial"

   Column    |            Type             | Collation | Nullable |                      Default

-------------+-----------------------------+-----------+----------+----------------------------------------------------

 test_id     | bigint                      |           | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)

 create_time | timestamp without time zone |           |          | clock_timestamp()

Indexes:

    "tb_test_bigserial_new_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d tb_test_bigserial_test_id_seq

                Sequence "alvin.tb_test_bigserial_test_id_seq"

  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache

--------+-------+---------+---------------------+-----------+---------+-------

 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

Owned by: alvin.tb_test_bigserial.test_id