自定义PostgreSQL扩展my_extension升级报错修复咨询

尝试在自定义扩展my_extension上执行ALTER EXTENSION操作。

已执行以下修改：

• 创建新文件my_extension--1.0.1.sql
• 修改控制文件代码：
  • 将default_version = '1.0.0'改为default_version = '1.0.1'
• 修改Makefile的DATA变量指向新SQL文件：
  • 将DATA = my_extension--1.0.0.sql改为DATA = my_extension--1.0.1.sql

执行ALTER EXTENSION my_extension UPDATE TO '1.1.0';后，出现如下错误：

ERROR: extension "my_extension" has no update path from version "1.0.0" to version "1.0.1"

请问需要修改哪些内容才能修复该问题？

Makefile: #

EXTENSION = my_extension
DATA = my_extension--1.0.1.sql
REGRESS = my_extension--regress

PG_CONFIG  ?= pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

控制文件: #

EXTENSION = my_extension
DATA = my_extension--1.0.1.sql
REGRESS = my_extension--regress

PG_CONFIG  ?= pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

SQL文件my_extension--1.0.1.sql: #

-- Update path script for version 1.0.1

-- Create necessary objects (version 1.0.0)
CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Add 2 numbers using add function (version 1.0.0)
CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN a + b;

-- This is the new function added in version 1.0.1
CREATE FUNCTION complex_add(integer[]) RETURNS integer
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    AS $$
        SELECT COALESCE(SUM(val), 0)
        FROM unnest($1) AS t(val)
    $$
;

要修复这个更新路径错误，需要完成以下关键修改：

1. 创建增量升级脚本
   PostgreSQL扩展升级需要专门的版本过渡脚本，命名格式为my_extension--旧版本号--新版本号.sql。这里需要创建my_extension--1.0.0--1.0.1.sql，内容仅包含从1.0.0到1.0.1的新增变更，无需重复创建旧版本对象：

   -- 1.0.0 到 1.0.1 的增量升级脚本
   CREATE FUNCTION complex_add(integer[]) RETURNS integer
       LANGUAGE SQL
       IMMUTABLE
       RETURNS NULL ON NULL INPUT
       AS $$
           SELECT COALESCE(SUM(val), 0)
           FROM unnest($1) AS t(val)
       $$
   ;
   

2. 调整主SQL文件的定位
   保留my_extension--1.0.1.sql作为全新安装脚本（包含1.0.1版本的所有完整对象），当前脚本内容符合这个要求，无需修改。

3. 更新DATA配置
   在Makefile和控制文件的DATA变量中，同时加入安装脚本和升级脚本，让PostgreSQL识别完整的版本路径：

   DATA = my_extension--1.0.1.sql my_extension--1.0.0--1.0.1.sql
   

   控制文件中default_version = '1.0.1'的修改已经正确，无需改动。

4. 修正升级命令
   原命令目标版本写错，应改为对应新版本号：

   ALTER EXTENSION my_extension UPDATE TO '1.0.1';
   

PostgreSQL扩展升级依赖明确的版本过渡脚本。你仅提供了1.0.1的安装脚本，但未定义从1.0.0到1.0.1的升级逻辑，导致数据库无法识别升级路径，从而抛出错误。

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