PostgreSQL运维案例:建表报错,提示type already exists

2023/07/12 14:52
阅读数 526

问题描述

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

后面自行模拟了一下:

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

  
  
  
  1. postgres=# create table lxs2(id int);

  2. CREATE TABLE

  3. postgres=# insert into lxs2 values(1);

  4. INSERT 0 1

  5. postgres=# select * from lxs2;

  6. id

  7. ----

  8. 1

  9. (1 row)

  10. postgres=# select * from pg_class where relname='lxs2';

  11. oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchec

  12. ks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound

  13. -------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+--------

  14. ---+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------

  15. 49197 | lxs2 | 2200 | 49199 | 0 | 10 | 2 | 49197 | 0 | 0 | -1 | 0 | 0 | f | f | p | r | 1 |

  16. 0 | f | f | f | f | f | t | d | f | 0 | 1049076 | 65536 | | |

  17. (1 row)

  18. postgres=# delete from pg_class where oid=49197;

  19. DELETE 1

  20. postgres=#

问题现象

创建表失败

  
  
  
  1. postgres=# create table lxs2(id int);

  2. ERROR: type "lxs2" already exists

  3. 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.

  4. 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查询,而找不到任何冲突的条目:

  
  
  
  1. ---

  2. select n.nspname as schemaname, c.relname, c.relkind

  3. from pg_class c

  4. join pg_namespace n on n.oid = c.relnamespace

  5. where relname = 'lxs2';

  6. ---

  7. postgres=# select n.nspname as schemaname, c.relname, c.relkind

  8. postgres-# from pg_class c

  9. postgres-# join pg_namespace n on n.oid = c.relnamespace

  10. postgres-# where relname = 'lxs2';

  11. schemaname | relname | relkind

  12. ------------+---------+---------

  13. (0 rows)

  14. postgres=#

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

  
  
  
  1. postgres=# select * from pg_type where typname='lxs2';

  2. -[ RECORD 1 ]--+------------

  3. oid | 49199

  4. typname | lxs2

  5. typnamespace | 2200

  6. typowner | 10

  7. typlen | -1

  8. typbyval | f

  9. typtype | c

  10. typcategory | C

  11. typispreferred | f

  12. typisdefined | t

  13. typdelim | ,

  14. typrelid | 49197

  15. typsubscript | -

  16. typelem | 0

  17. typarray | 49198

  18. typinput | record_in

  19. typoutput | record_out

  20. typreceive | record_recv

  21. typsend | record_send

  22. typmodin | -

  23. typmodout | -

  24. typanalyze | -

  25. typalign | d

  26. typstorage | x

  27. typnotnull | f

  28. typbasetype | 0

  29. typtypmod | -1

  30. typndims | 0

  31. typcollation | 0

  32. typdefaultbin |

  33. typdefault |

  34. typacl |

  35. postgres=#

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

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

  
  
  
  1. postgres=# select * from pg_class where oid=49199;

  2. (0 rows)

  3. postgres=#

解决方法

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

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

  
  
  
  1. postgres=# alter type lxs2 rename to type1;

  2. ERROR: lxs2 is a table's row type

  3. HINT: Use ALTER TABLE instead.

  4. postgres=#

提示需要使用alter table命令

  
  
  
  1. postgres=# alter table lxs2 rename to type1;

  2. ERROR: relation "lxs2" does not exist

  3. postgres=#

提示没有对象。

那改用drop吧:

  
  
  
  1. postgres=# drop type lxs2;

  2. ERROR: cache lookup failed for relation 49197

  3. postgres=#

同样报错:cache lookup failed for relation 49197

通过pg_class查找提示的relation 49197

  
  
  
  1. postgres=# select * from pg_class where oid=49197;

  2. (0 rows)

  3. postgres=#

那就查找表依赖(pg_depend系统表记录数据库对象之间的依赖关系。):

  
  
  
  1. postgres=# select * from pg_depend where refobjid ='49197';

  2. classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype

  3. ---------+-------+----------+------------+----------+-------------+---------

  4. 1247 | 49199 | 0 | 1259 | 49197 | 0 | i

  5. (1 row)

  6. postgres=#

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

  
  
  
  1. postgres=# delete from pg_depend where refobjid ='49197';

  2. DELETE 1

  3. postgres=#

再次drop type

  
  
  
  1. postgres=# drop type lxs2;

  2. DROP TYPE

  3. postgres=#

最后再次建表使用

  
  
  
  1. postgres=# create table lxs2(id int);

  2. CREATE TABLE

  3. postgres=#

  4. postgres=# insert into lxs2 values(1);

  5. INSERT 0 1

  6. postgres=# select * from lxs2;

  7. id

  8. ----

  9. 1

  10. (1 row)

  11. postgres=#


本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部