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

Question

Trying to implement ALTER EXTENSION on custom extension my_extension.

Made the following changes:

• Created a new file my_extension--1.0.1.sql
• I changed the code of the control file:
  • default_version = '1.0.0' to default_version = '1.0.1'
• Changed the Makefile DATA variable to new SQL file:
  • DATA = my_extension--1.0.0.sql to DATA = my_extension--1.0.1.sql

After running 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"

What should I change to fix this?

Code for different files:

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)

Control File:

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 File 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)
    $$
;

Answer 1

This way of updating databases dynamically from one version to the next is wrong.

Answer: https://www.postgresql.org/docs/current/extend-extensions.html#id-1.8.3.20.14

One advantage of the extension mechanism is that it provides convenient ways to manage updates to the SQL commands that define an extension's objects. This is done by associating a version name or number with each released version of the extension's installation script. In addition, if you want users to be able to update their databases dynamically from one version to the next, you should provide update scripts that make the necessary changes to go from one version to the next. Update scripts have names following the pattern *extension*--*old_version*--*target_version*.sql (for example, foo--1.0--1.1.sql contains the commands to modify version 1.0 of extension foo into version 1.1).

Given that a suitable update script is available, the command ALTER EXTENSION UPDATE will update an installed extension to the specified new version. The update script is run in the same environment that CREATE EXTENSION provides for installation scripts: in particular, search_path is set up in the same way, and any new objects created by the script are automatically added to the extension. Also, if the script chooses to drop extension member objects, they are automatically dissociated from the extension.

If an extension has secondary control files, the control parameters that are used for an update script are those associated with the script's target (new) version.

ALTER EXTENSION is able to execute sequences of update script files to achieve a requested update. For example, if only foo--1.0--1.1.sql and foo--1.1--2.0.sql are available, ALTER EXTENSION will apply them in sequence if an update to version 2.0 is requested when 1.0 is currently installed.

Alternate way:

Without changing the files and creating update scripts is: Dropping the current extension version DROP EXTENSION my_extension

make the necessary changes in the files to point towards the target version and run CREATE EXTENSION