PostgreSQL学徒

命名不规范，事后泪两行

数据库原理
Word count: 1.3kReading time: 5 min
2025/03/12
loading

前言

前阵子在群里又看到一则由于命名导致的坑爹案例，在 PostgreSQL 中，索引、表、视图和序列共享相同的命名空间，因此，同一个模式下，不能拥有相同的名称。就是这样一个小问题，相信不少老油条也掉过坑里，借此机会也简单唠唠在建表时，我们需要注意的若干事项。

同名对象

让我们复现一下当时的案发现场：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
postgres=# CREATE TABLE tbl_comprehensive_sales_analytics (
    id          SERIAL          NOT NULL,
    column_a    VARCHAR(100)    NOT NULL,
    column_b    INTEGER         NOT NULL,
    column_c    TEXT,
    column_d    DATE,
    column_e    TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    column_f    BOOLEAN         DEFAULT FALSE,
    column_g    NUMERIC(10, 2),
    column_h    VARCHAR(255),
    column_i    TEXT,
    CONSTRAINT tbl_comprehensive_sales_analytics PRIMARY KEY (id)
);
ERROR:  relation "tbl_comprehensive_sales_analytics" already exists
postgres=# select oid from pg_class where relname  = 'tbl_comprehensive_sales_analytics';
 oid 
-----
(0 rows)

可以看到，在 pg_class 中并没有 tbl_comprehensive_sales_analytics 这个表，包括在 pg_views、pg_sequences 等等，都没有这个对象，那为何还会提示已经存在，千万不要多虑成是不是遇到什么 BUG 或者系统表损坏了等等。

相信眼尖的读者已经发现了，是的，在此例中约束名和表名是一样的！也正是如此，才会导致建了表之后，再去建同名的约束，会导致 “already exists” 的报错。尤其是从其他数据库迁移过来的话，更容易掉到这个坑里，需要格外注意。那为何会这样？看一下 pg_class 的定义就明白了：

1
2
3
4
5
6
7
8
9
10
11
postgres=# \d pg_class
                     Table "pg_catalog.pg_class"
       Column        │     Type     │ Collation │ Nullable │ Default 
---------------------+--------------+-----------+----------+---------
 oid                 │ oid          │           │ not null │ 
 relname             │ name         │           │ not null │
[...]
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

第二个索引 “pg_class_relname_nsp_index” UNIQUE, btree (relname, relnamespace) 限制了在同一个 schema 下面，不能拥有相同的名称。另外值得注意的是，如果是约束 (非唯一约束和主键约束) 是可以的，可以简单理解成其并没有“实体”。至于唯一约束和主键约束，因为每次在声明唯一约束或主键约束时，PostgreSQL 会自动创建一个唯一索引用于支持这个约束。也就是说，如果你只是简单地创建了一个唯一索引而没有明确声明一个完整性约束，效果似乎是完全一样的：索引列将不允许重复。那么区别是什么呢？完整性约束定义了一个绝不能违反的属性，而索引只是保证这一属性的一种机制。理论上，约束也可以通过其他手段施加。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# CREATE TABLE tbl_comprehensive_sales_analytics (
    id          SERIAL          NOT NULL,
    column_a    VARCHAR(100)    NOT NULL,
    column_b    INTEGER         NOT NULL,
    column_c    TEXT,
    column_d    DATE,
    column_e    TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    column_f    BOOLEAN         DEFAULT FALSE,
    column_g    NUMERIC(10, 2),
    column_h    VARCHAR(255),
    column_i    TEXT,
    CONSTRAINT tbl_comprehensive_sales_analytics check(id < 10)  
);
CREATE TABLE

63 字符的限制

另外一个问题也是老生常谈了，在数据库中，有着各种各样的限制，比如默认情况下函数最大的参数数量为 32，索引列最大为 32 列，标识符长度不超过 63 字节等等。让我们看个之前的实际案例，整一个特别长名字的分区表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# create table a_long_table_name_with_exactly_sixty_three_characters_testtbl01(id int,info text,t_time timestamp) partition by range(t_time);     ---父表
CREATE TABLE
postgres=# create table a_long_table_name_with_exactly_sixty_three_characters_test_par1 partition of a_long_table_name_with_exactly_sixty_three_characters_testtbl01 for values FROM ('2023-01-01') TO ('2024-01-01');  ---子表
CREATE TABLE                                                        
postgres=# create table a_long_table_name_with_exactly_sixty_three_characters_test_par2 partition of a_long_table_name_with_exactly_sixty_three_characters_testtbl01 for values FROM ('2022-01-01') TO ('2023-01-01');  ---子表
CREATE TABLE

postgres=# \d a_long_table_name_with_exactly_sixty_three_characters_testtbl01
Partitioned table "public.a_long_table_name_with_exactly_sixty_three_characters_testtbl01"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 id     | integer                     |           |          | 
 info   | text                        |           |          | 
 t_time | timestamp without time zone |           |          | 
Partition key: RANGE (t_time)
Number of partitions: 2 (Use \d+ to list them.)

然后为子表添加索引：

1
2
3
4
5
6
7
8
9
postgres=# begin;
BEGIN
postgres=*# create index on a_long_table_name_with_exactly_sixty_three_characters_test_par1(id,info);
CREATE INDEX
postgres=*# select pg_backend_pid();
 pg_backend_pid 
----------------
          12212
(1 row)

另一个会话同样添加索引，会莫名发现被卡主，操作的明明是两个对象：

1
2
3
4
5
6
7
8
9
10
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
 pg_backend_pid 
----------------
          12322
(1 row)

postgres=*# create index on a_long_table_name_with_exactly_sixty_three_characters_test_par2(id,info);
---此处卡住

这个问题原理其实类似，建索引的时候如果不指定索引名，那么会默认用表名 + 字段名 + idx的后缀命名。让我们瞅瞅第一个会话的索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# begin;
BEGIN
postgres=*# create index on a_long_table_name_with_exactly_sixty_three_characters_test_par1(id,info);
CREATE INDEX
postgres=*# \d a_long_table_name_with_exactly_sixty_three_characters_test_par1
Table "public.a_long_table_name_with_exactly_sixty_three_characters_test_par1"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 id     | integer                     |           |          | 
 info   | text                        |           |          | 
 t_time | timestamp without time zone |           |          | 
Partition of: a_long_table_name_with_exactly_sixty_three_characters_testtbl01 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2024-01-01 00:00:00')
Indexes:
    "a_long_table_name_with_exactly_sixty_three_characte_id_info_idx" btree (id, info)

其索引变成了 a_long_table_name_with_exactly_sixty_three_characte_id_info_idx，没有了 test_par1，被自动截断了！

1
2
3
4
5
postgres=*# select length('a_long_table_name_with_exactly_sixty_three_characte_id_info_idx');
 length 
--------
     63
(1 row)

因此，第二个会话为什么被阻塞也就说得通了，其创建的索引也是这个！当然需要等待了。将日志设置为 NOTICE 会更为明显：

1
2
3
4
5
postgres=# begin;
BEGIN
postgres=*# create table long_long_long_long_long_longlong_long_longlong_long_longlong_long_long(id int);
NOTICE:  identifier "long_long_long_long_long_longlong_long_longlong_long_longlong_long_long" will be truncated to "long_long_long_long_long_longlong_long_longlong_long_longlong_l"
CREATE TABLE

碰到这种问题，还是老老实实按照规范，避免超过 63 字符，或者显示给索引命名，要么就去改代码，更改 src/include/pg_config_manual.h 中的 NAMEDATALEN 常量来提高这个限制。

小结

遵循规范，可以让我们少走很多弯路，否则事前一时爽，事后泪两行啊。

参考

https://stackoverflow.com/questions/67573434/does-postgres-only-allow-one-index-with-the-same-name

CATALOG
  1. 1. 前言
  2. 2. 同名对象
  3. 3. 63 字符的限制
  4. 4. 小结
  5. 5. 参考
Total : 37
2025
2024
2023
2022
2021
年终总结 Greenplum SQL 优化 新特性解读 Double Buffer 数据库原理 培训认证 PostgreSQL 生态 Online DDL 个人感想 内存 统计信息 VACUUM 原理 书籍与资料