PostgreSQL运维—建表ERROR：type already exists

[toc]

问题描述

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

后面自行模拟了一下：

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

postgres=# create table lxs2(id int);
CREATE TABLE
postgres=# insert into lxs2 values(1);
INSERT 0 1
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=# delete from 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=# delete from 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 0 1
postgres=# select * from lxs2;
 id 
----
  1
(1 row)

postgres=#