问题描述
这是在一个客户那遇到的现象，是由于客户误操作使用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=#
问题分析
这里我们需要了解一下建表的流程：
确定表空间，schema；
创建表cache和物理表文件；
在pg_cass里增加一条表信息；
在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=#
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
