如何规避PostgreSQL中频繁变更物化视图的依赖错误？

1. 改用动态返回类型规避强依赖 #

不要将函数的返回类型直接绑定到物化视图，改用RETURNS SETOF record，让函数返回动态结构：

CREATE OR REPLACE FUNCTION foo()
  RETURNS SETOF record
  LANGUAGE sql STABLE
  AS $$
    SELECT * FROM my_view;
$$;

调用时需显式指定列定义：

SELECT * FROM foo() AS f(col1 float8, col2 float8, col3 float8);

如果希望调用更便捷，也可以用RETURNS TABLE，但需要在物化视图变更时同步更新函数的返回列定义；若追求完全无需修改函数，SETOF record是更优选择。

2. 利用系统表动态适配列变更 #

通过PostgreSQL系统表pg_catalog.pg_attribute获取物化视图的实时列信息，在函数内部动态构造查询，自动适配列变更：

CREATE OR REPLACE FUNCTION foo()
  RETURNS SETOF record
  LANGUAGE plpgsql STABLE
  AS $func$
DECLARE
  cols text;
BEGIN
  -- 获取物化视图的非隐藏列名
  SELECT string_agg(quote_ident(attname), ', ')
  INTO cols
  FROM pg_catalog.pg_attribute
  WHERE attrelid = 'my_view'::regclass
    AND attnum > 0
    AND NOT attisdropped;

  RETURN QUERY EXECUTE format('SELECT %s FROM my_view', cols);
END
$func$;

这种方式下，函数会自动感知物化视图的列增减，无需任何修改或重建。

3. 用普通视图做中间层隔离依赖 #

创建一个普通视图作为物化视图的包装层，让函数依赖这个包装视图而非底层物化视图：

CREATE OR REPLACE VIEW my_view_wrapper AS SELECT * FROM my_view;

修改函数的返回类型和查询目标：

CREATE OR REPLACE FUNCTION foo()
  RETURNS SETOF my_view_wrapper
  LANGUAGE sql STABLE
  AS $$
    SELECT * FROM my_view_wrapper;
$$;

然后将包装视图的重建逻辑加入到物化视图生成函数make_view中，确保每次物化视图更新时同步更新包装视图：

-- 修改make_view函数的末尾部分
raise notice '%', mv_sql; 
EXECUTE mv_sql;

-- 同步重建包装视图
EXECUTE $$DROP VIEW IF EXISTS my_view_wrapper;$$;
EXECUTE $$CREATE VIEW my_view_wrapper AS SELECT * FROM my_view;$$;
END
$func$;

这样函数依赖的包装视图会始终与物化视图结构保持一致，无需重建函数。

4. 禁用依赖跟踪（不推荐） #

可以通过修改系统表pg_depend手动删除函数与物化视图的依赖关系，但这属于非官方hack手法，可能导致数据库元数据不一致，后续版本升级或维护时极易引发问题，禁止在生产环境使用。

结合你的业务场景，除了解决动态列依赖问题，还可以考虑以下性能优化方向：

• 优化空间索引：在几何表上创建更高效的GIST或SP-GIST索引，针对查询场景调整索引参数（如设置fillfactor），减少物化视图的依赖需求
• 分区物化视图：按geo_type分区创建物化视图，新增几何类型时仅需新增对应分区，不影响现有函数和视图结构
• 改用JSONB存储关联结果：将点与几何对象的关联结果存储为JSONB格式，避免动态列问题，函数返回JSONB类型，查询时按需解析，适合灵活查询场景

内容的提问来源于stack exchange，提问作者Him