华山论剑之 PostgreSQL sequence (二)
阅读原文时间:2022年03月09日阅读:1

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 表的方式实现:

  1. 根据表 tb_test_bigserial 复制出相同表结构的表 tb_test_bigserial_new
  2. tb_test_bigserial_new 进行 DDL 或 大量 DML
  3. 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

公众号

关注 DBA Daily 公众号,第一时间收到文章的更新。

通过一线 DBA 的日常工作,学习实用数据库技术干货!

PostgreSQL VACUUM 之深入浅出

华山论剑之 PostgreSQL sequence

[PG Upgrade Series] Extract Epoch Trap

[PG Upgrade Series] Toast Dump Error

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight

ReIndex 失败原因调查

PG 数据导入 Hive 乱码问题调查

PostGIS 扩展创建失败原因调查

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章