PostgreSQL运维案例：建表报错，提示type already exists

问题描述

这是在一个客户那遇到的现象，是由于客户误操作使用delete pg_class操作删除表，后续创建过程中出现ERROR：type already exists错误。

后面自行模拟了一下：

创建表，删除pg_class记录后，再次创建表即可复现。

postgres=# create table lxs2(id int);
CREATE TABLE
postgres=# insert into lxs2 values(1);
INSERT 01
postgres=#select*from lxs2;
 id 
----
1
(1 row)
postgres=#select*from pg_class where relname='lxs2';
  oid  | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchec
ks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound 
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+--------
---+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
49197| lxs2    |2200|49199|0|10|2|49197|0|0|-1|0|0| f           | f           | p              | r       |1|
0| f           | f              | f              | f              | f                   | t              | d            | f              |0|1049076|65536|||
(1 row)
postgres=#deletefrom pg_class where oid=49197;
DELETE 1
postgres=#

问题现象

创建表失败

postgres=# create table lxs2(id int);
ERROR:  type "lxs2" already exists
HINT:  A relation has an associated type of the same name, so you must use a name that doesn't conflict with any existing type.
postgres=#

问题分析

这里我们需要了解一下建表的流程：

1. 确定表空间，schema；

2. 创建表cache和物理表文件；

3. 在pg_cass里增加一条表信息；

4. 在pg_type里增加一条表类型信息；

所以Postgres为每个表创建一个具有相同名称的复合类型。 这就是为什么错误消息提到“type”，而不是“table”。 实际上，表名不能与以下内容冲突：

r =普通表，i =索引，S =序列，v =视图，m =物化视图， c =复合类型 ，t = TOAST表，f =外表

我们使用pg_class查询，而找不到任何冲突的条目：

---
select n.nspname as schemaname, c.relname, c.relkind
from   pg_class c
join   pg_namespace n on n.oid = c.relnamespace
where  relname ='lxs2';
---
postgres=#select n.nspname as schemaname, c.relname, c.relkind
postgres-#from   pg_class c
postgres-# join   pg_namespace n on n.oid = c.relnamespace
postgres-#where  relname ='lxs2';
 schemaname | relname | relkind 
------------+---------+---------
(0 rows)
postgres=#

我们通过pg_type查找到了相关的冲突条目：

postgres=#select*from pg_type where typname='lxs2';
-[ RECORD 1]--+------------
oid            |49199
typname        | lxs2
typnamespace   |2200
typowner       |10
typlen         |-1
typbyval       | f
typtype        | c
typcategory    | C
typispreferred | f
typisdefined   | t
typdelim       |,
typrelid       |49197
typsubscript   |-
typelem        |0
typarray       |49198
typinput       | record_in
typoutput      | record_out
typreceive     | record_recv
typsend        | record_send
typmodin       |-
typmodout      |-
typanalyze     |-
typalign       | d
typstorage     | x
typnotnull     | f
typbasetype    |0
typtypmod      |-1
typndims       |0
typcollation   |0
typdefaultbin  |
typdefault     |
typacl         |
postgres=#

注意，同一名称可以在多个模式中多次存在，但不能在同一模式中存在。

所以我们将oid反过来查询pg_class

postgres=#select*from pg_class where oid=49199;
(0 rows)
postgres=#

解决方法

发现一个有冲突的复合类型，您可以重命名或删除它。

这里我们先尝试通过rename重命名：

postgres=# alter type lxs2 rename to type1;
ERROR:  lxs2 is a table's row type
HINT:  Use ALTER TABLE instead.
postgres=#

提示需要使用alter table命令

postgres=# alter table lxs2 rename to type1;
ERROR:  relation "lxs2" does not exist
postgres=#

提示没有对象。

那改用drop吧：

postgres=# drop type lxs2;
ERROR:  cache lookup failed for relation 49197
postgres=#

同样报错：cache lookup failed for relation 49197

通过pg_class查找提示的relation 49197

postgres=#select*from pg_class where oid=49197;
(0 rows)
postgres=#

那就查找表依赖（pg_depend系统表记录数据库对象之间的依赖关系。）：

postgres=#select*from pg_depend where refobjid ='49197';
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------+-------+----------+------------+----------+-------------+---------
1247|49199|0|1259|49197|0| i
(1 row)
postgres=#

查询到pg_depend表的refobjid中存在该id的引用，还可以看到objid也是对应的上面的lxs2的oid，所以我们将这条数据删除掉。

postgres=#deletefrom pg_depend where refobjid ='49197';
DELETE 1
postgres=#

再次drop type

postgres=# drop type lxs2;
DROP TYPE
postgres=#

最后再次建表使用

postgres=# create table lxs2(id int);
CREATE TABLE
postgres=#
postgres=# insert into lxs2 values(1);
INSERT 01
postgres=#select*from lxs2;
 id 
----
1
(1 row)
postgres=#