最近在highgo瀚高（IvorySQL） 数据库中，对普通表修改某一列的数据类型时，出现 cache lookup failed for foreign server 97007982。经过逐级排查，最终发现问题并不在目标表本身，而是该列被一个历史 View 依赖，而 View 曾经依赖 DBLink 相关对象；DBLink 删除后，View 中残留了异常依赖关系。
一、问题现象
业务需要修改表：
PostgreSQL> ALTER TABLE xxxxxxx ALTER COLUMN county_id TYPE Varchar(255)；
SQL 错误[Xx000]: ERROR:cache lookup failed for foreign server 97007982错误不是原生的postgreSQL报错，HighgoDB(IvorySQL )本身是基于 PostgreSQL 的 Oracle 兼容数据库，并保持 PostgreSQL 内核兼容路线。
这个现象是HIGHGO的某段 DDL/兼容层代码，在执行 ALTER COLUMN TYPE 时调用了 GetForeignServer(97007982)，但这个 OID 已经不存在。
PostgreSQL 内核的 GetForeignServer() 本身就是通过 FOREIGNSERVEROID cache 查找 OID；找不到就直接抛出：
cache lookup failed for foreign server %u
二、问题分析
先确认这个 OID 是否还存在
SELECT oid, srvname, srvowner, srvfdw
FROM pg_foreign_server
WHERE oid = 97007982;无数据，pg_foreign_server 中已经找不到这个 OID。
找出是谁引用了这个 Foreign Server
SELECT
c.oid,
n.nspname AS schema_name,
c.relname AS foreign_table,
c.relkind,
ft.ftserver
FROM pg_foreign_table ft
JOIN pg_class c
ON c.oid = ft.ftrelid
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE ft.ftserver = 97007982;
-- or --
SELECT
c.oid AS table_oid,
n.nspname AS schema_name,
c.relname AS table_name,
c.relkind,
ft.ftserver,
fs.srvname,
fs.srvfdw,
fdw.fdwname
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
LEFT JOIN pg_foreign_table ft
ON ft.ftrelid = c.oid
LEFT JOIN pg_foreign_server fs
ON fs.oid = ft.ftserver
LEFT JOIN pg_foreign_data_wrapper fdw
ON fdw.oid = fs.srvfdw
WHERE c.relname = 'xxxxx';schema = xxx
table = xxxxx
relkind = r
oid = 342358206
ftserver = NULL
srvname = NULL
srvfdw = NULL
fdwname = NULL
确认 Foreign Server 是否存在
SELECT
oid,
srvname,
srvowner,
srvfdw
FROM pg_foreign_server
WHERE oid = 97007982;这个 Foreign Server 已经不存在。
SELECT * FROM pg_foreign_table;并不存在那个表的记录。xxxx 不是 Foreign Table。
查 97007982 有没有出现在依赖关系里
SELECT *
FROM pg_depend
WHERE objid = 97007982
OR refobjid = 97007982;
SELECT *
FROM pg_shdepend
WHERE objid = 97007982
OR refobjid = 97007982;
都是 0 行,标准 dependency catalog 里没有直接引用。
检查这个表有没有特殊属性
SELECT
c.oid,
n.nspname,
c.relname,
c.relkind,
c.relispartition,
c.relispartition,
c.relhassubclass,
c.reloptions
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE c.oid = 342358206; --- xxx oid检查 county_id 的列属性
SELECT
attrelid,
attnum,
attname,
atttypid,
format_type(atttypid, atttypmod) AS data_type,
attnotnull,
atthasdef,
attidentity,
attgenerated
FROM pg_attribute
WHERE attrelid = 342358206
AND attname = 'county_id';
attrelid | attnum | attname | atttypid | data_type | attnotnull | atthasdef | attidentity | attgenerated
-----------+--------+-----------+----------+---------------+------------+-----------+-------------+--------------
342358206 | 21 | county_id | 9002 | varchar2(255) | f | f | |
(1 row)
查这个表是否存在继承/分区关系
SELECT
i.inhrelid,
i.inhparent,
child.relname AS child_table,
parent.relname AS parent_table
FROM pg_inherits i
JOIN pg_class child
ON child.oid = i.inhrelid
JOIN pg_class parent
ON parent.oid = i.inhparent
WHERE i.inhrelid = 342358206
OR i.inhparent = 342358206;非分区表
检查 county_id 的默认值
SELECT
a.attname,
a.atttypid,
format_type(a.atttypid, a.atttypmod) AS data_type,
pg_get_expr(ad.adbin, ad.adrelid) AS default_expr
FROM pg_attribute a
LEFT JOIN pg_attrdef ad
ON ad.adrelid = a.attrelid
AND ad.adnum = a.attnum
WHERE a.attrelid = 342358206
AND a.attname = 'county_id';
attname | atttypid | data_type | default_expr
-----------+----------+---------------+--------------
county_id | 9002 | varchar2(255) |
(1 row)查表上的索引
SELECT
i.indexrelid,
i.indexrelid::regclass AS index_name,
i.indisprimary,
i.indisunique,
i.indisvalid
-- , pg_get_indexdef(i.indexrelid) AS indexdef
FROM pg_index i
WHERE i.indrelid = 342358206
ORDER BY i.indexrelid, x.n;
index_name | attname | n
----------------------------------------------------+---------------+---
anbob.xxxxxxxxxxxxxxxxxxxdress_address_level_idx | address_level | 1
anbob.xxxxxxxxxxxxxxxxxxxdress_related_add_idx | related_add | 1
anbob.xxxxxxxxxxxxxxxxxxxdress_full_town_idx | full_town | 1
anbob.xxxxxxxxxxxxxxxxxxxdress_full_address_idx | full_address | 1
anbob.xxxxxxxxxxxxxxxxxxxdress_int_id_stateflag_idx | int_id | 1
anbob.xxxxxxxxxxxxxxxxxxxdress_int_id_stateflag_idx | stateflag | 2
anbob.xxxxxxxxxxxxxxxxxxxdress_cengji_idx | int_id | 1
anbob.xxxxxxxxxxxxxxxxxxxdress_cengji_idx | stateflag | 2
anbob.xxxxxxxxxxxxxxxxxxxdress_cengji_idx | related_add | 3
anbob.xxxxxxxxxxxxxxxxxxxdress_int_id_idx | int_id | 1
(10 rows)查这个表的约束
SELECT
con.oid,
conname,
contype,
pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
WHERE con.conrelid = 342358206
OR con.confrelid = 342358206;
oid | conname | contype | definition
-----+---------+---------+------------
(0 rows)无特殊约束
把 county_id 所有依赖对象列出来
WITH col AS (
SELECT
attrelid,
attnum
FROM pg_attribute
WHERE attrelid = 342358206
AND attname = 'county_id'
)
SELECT
d.classid::regclass AS dependent_catalog,
d.objid,
d.objsubid,
d.deptype,
CASE
WHEN d.classid = 'pg_class'::regclass
THEN d.objid::regclass::text
WHEN d.classid = 'pg_constraint'::regclass
THEN (
SELECT conname
FROM pg_constraint
WHERE oid = d.objid
)
WHEN d.classid = 'pg_attrdef'::regclass
THEN 'column default'
ELSE NULL
END AS dependent_name
FROM pg_depend d
JOIN col c
ON d.refobjid = c.attrelid
AND d.refobjsubid = c.attnum
ORDER BY d.classid, d.objid;
dependent_catalog | objid | objsubid | deptype | dependent_name
-------------------+-----------+----------+---------+----------------
pg_rewrite | 353295252 | 0 | n |
pg_rewrite | 353302453 | 0 | n |
pg_rewrite | 353352031 | 0 | n |
pg_rewrite | 353361110 | 0 | n |
pg_rewrite | 353427080 | 0 | n |
pg_rewrite | 356043980 | 0 | n |
(6 rows)pg_depend 显示，county_id 被 6 个 pg_rewrite 对象直接依赖
pg_rewrite 是 PostgreSQL 用来保存 VIEW/RULE 的 rewrite rule 的 catalog。
你的：
xxxxxxxndarddress.county_id
被 6 个 pg_rewrite 引用：
xxxxxxxndarddress
|
+-- county_id
|
+-- pg_rewrite 353295252
+-- pg_rewrite 353302453
+-- pg_rewrite 353352031
+-- pg_rewrite 353361110
+-- pg_rewrite 353427080
+-- pg_rewrite 356043980
6 个 pg_rewrite 找到对应的 VIEW
SELECT
r.oid AS rewrite_oid,
r.ev_class AS view_oid,
n.nspname,
c.relname,
c.relkind,
CASE c.relkind
WHEN 'v' THEN 'VIEW'
WHEN 'm' THEN 'MATERIALIZED VIEW'
WHEN 'r' THEN 'TABLE'
WHEN 'f' THEN 'FOREIGN TABLE'
ELSE c.relkind::text
END AS object_type,
r.rulename
FROM pg_rewrite r
JOIN pg_class c
ON c.oid = r.ev_class
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE r.oid IN (
353295252,
353302453,
353352031,
353361110,
353427080,
356043980
)
ORDER BY r.oid;把这 6 个 VIEW 的定义直接拿出来：
SELECT
r.oid AS rewrite_oid,
n.nspname,
c.relname,
c.relkind,
pg_get_viewdef(c.oid, true) AS view_definition
FROM pg_rewrite r
JOIN pg_class c
ON c.oid = r.ev_class
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE r.oid IN (
353295252,
353302453,
353352031,
353361110,
353427080,
356043980
)
ORDER BY r.oid;找到了问题的view, 有一个view之前依赖过dblink, 后面dblink删掉了导致的。
目前的问题链
anbob.xxxxxxxxandarddress
│
│ OID 342358206
▼
county_id
attnum = 21
varchar2(255)
│
▼
pg_depend
│
├── pg_rewrite 353295252
├── pg_rewrite 353302453
├── pg_rewrite 353352031
├── pg_rewrite 353361110
├── pg_rewrite 353427080
└── pg_rewrite 356043980
│
▼
6 个 VIEW/RULE
│
▼
ALTER COLUMN TYPE
│
▼
IvorySQL DDL 处理
│
▼
Foreign Server OID
97007982
│
X
pg_foreign_server
中不存在思路总结
第一步：不要被错误信息带偏
看到：
cache lookup failed for foreign server
不要立即认为：
目标表 = Foreign Table
先确认：
SELECT relkind FROM pg_class WHERE oid = ...;
第二步：比较“成功列”和“失败列”
这是本案例非常有效的一步。
如果：
同一张表：
column A → ALTER TYPE OK
column B → ALTER TYPE OK
county_id → ERROR
那么优先考虑：
column-level dependency
而不是：
table-level metadata
第三步：查 pg_depend
核心 SQL：
SELECT
d.classid::regclass,
d.objid,
d.objsubid,
d.refclassid::regclass,
d.refobjid,
d.refobjsubid,
d.deptype
FROM pg_depend d
WHERE d.refclassid = 'pg_class'::regclass
AND d.refobjid = <table_oid>
AND d.refobjsubid = <attnum>;
重点关注：
pg_rewritepg_constraintpg_attrdefpg_class
等依赖对象。
第四步：如果出现 pg_rewrite
优先考虑：
VIEWRULE
然后通过：
SELECT pg_get_viewdef(...);
获取 View 定义。
第五步：检查 View 的历史依赖
重点关注：
- DBLink
- Foreign Table
- Foreign Server
- FDW
- 跨库对象
- 已经删除的对象
- 历史迁移产生的对象
解决方案
如果 View 已经不再使用：
删除异常 View
如果 View 仍然需要：
1. 保存 View 当前定义
2. 清理历史 DBLink 相关依赖
3. 按当前实际业务定义重新创建 View
4. 重新检查 pg_depend
5. 再执行 ALTER COLUMN TYPE
总结
本案例最终根因可以概括为：
anbob.xxxxxdarddress 是普通表，本身不存在 Foreign Server 问题。真正触发异常的是 county_id 的列级依赖。该列被一个 View 的 pg_rewrite 规则引用，而该 View 历史上依赖 DBLink 相关对象。DBLink 删除后，View 的历史依赖没有被完整清理。当 IvorySQL 执行 ALTER COLUMN TYPE 并重新处理该列的依赖关系时，尝试访问已经不存在的 Foreign Server OID 97007982，最终产生 cache lookup failed for foreign server 97007982。
ALTER TABLE
↓
ALTER COLUMN county_id TYPE
↓
检查该列的依赖对象
↓
发现相关 View / pg_rewrite
↓
IvorySQL 重新处理 View
↓
尝试访问历史 DBLink 对应的 Foreign Server
↓
pg_foreign_server 中找不到 97007982
↓
ERROR: cache lookup failed for foreign server 97007982删除 DBLink 后，并不是所有操作都会立即访问这个历史依赖。这也是为什么这类问题经常表现为：
“数据库一直正常，今天突然 ALTER TABLE 报一个看起来完全不相关的 Foreign Server 错误。”