Installing a new pg_tle extension version which requires following an upgrade path doesnt' work

[tiniscule]

Bug report

I have 2 files for my extension.

basejump_core--2.0.1.sql basejump_core--2.0.1--2.1.0.sql

Default version set to 2.1.0.

On a clean install, running create extension "basejump_core" version '2.1.0'; will fail with the following error:

ERROR:  could not find sql function "basejump_core--2.1.0.sql" for extension basejump_core in schema pgtle

Based on the pg_tle docs, this appears like it should work by first installing 2.0.1 and then following the 2.1.0 path - but doesn't seem to.

To Reproduce

Here's a PR with the upgrade I'm working on which doesn't work: https://github.com/usebasejump/basejump/pull/57

Expected behavior

I expect the extension to be installable to 2.1.0 without having a dedicated basejump_core--2.1.0.sql extension file

[imor]

Hey @tiniscule,

How did you install basejump_core? Did you use the dbdev cli's install command? If yes, was the dbdev version older than 0.1.4? There was a fix in 0.1.4 about update paths not being installed.

To debug, you can see which update paths are available with select * from pgtle.extension_update_paths('basejump_core'); command. This will return a source, target and a path column. If you see a source = 2.0.1 a target = 2.1.0 and a path = 2.0.1--2.1.0 it means the update path is available and you should be able to create the extension. if the path is null or the entire row is missing then it won't work.

[tiniscule]

Thanks for the quick response @imor , yes it was installed with the dbdev local command.

dbdev version: 0.1.5;

result of extension_update_paths query:

That second one seems odd, is that to be expected? here's the file structure:

[imor]

That second one seems odd, is that to be expected?

Yes, that is ok. Postgres doesn't really understand the contents of a version. They are just treated a opaque strings. So it doesn't know which version is greater, it just lists all combinations of source and target.

That's really weird, the data you have shared looks completely fine to me. The create extension "basejump_core" version '2.1.0'; command should have caused pg_tle to first run basejump_core--2.0.1.sql and then run basejump_core--2.0.1--2.1.0.sql to create the extension.

To further debug it can you please share:

1. The pg_tle extension version you have installed. You can check it by running select extversion from pg_extension where extname = 'pg_tle';
2. The code for basejump_core from a branch or tag with exact files above.
3. Check which .sql and .control functions are present by running select * from pg_proc where proname like '%basejump_core%';.

[tiniscule]

pg_tle version: 1.0.4 Code: https://github.com/usebasejump/basejump/pull/57 functions:

[imor]

Okay this is a pg_tle bug. You can reproduce it by running the following pgtle api calls:

-- install version 1.0 of an extension
SELECT pgtle.install_extension
(
 'test123',
 '1.0',
 'Test TLE Functions',
$_pgtle_$
  CREATE OR REPLACE FUNCTION test123_func()
  RETURNS INT AS $$
  (
    SELECT 10
  )$$ LANGUAGE sql;
$_pgtle_$
);

-- create update path to 1.1
SELECT pgtle.install_update_path
(
 'test123',
 '1.0',
 '1.1',
$_pgtle_$
  CREATE OR REPLACE FUNCTION test123_func()
  RETURNS INT AS $$
  (
    SELECT 11
  )$$ LANGUAGE sql;
$_pgtle_$
);

--confirm that the upgrade paths are registered
select * from pgtle.extension_update_paths('test123');

-- create extension should pass but it fails with the error:
-- ERROR:  could not find sql function "test123--1.1.sql" for extension test123 in schema pgtle 
-- 
-- SQL state: XX000
CREATE EXTENSION test123 version '1.1';

I couldn't reproduce it earlier because I was on the latest dev version of pg_tle. We plan to update pg_tle to the latest version on our platform soon but until then dbdev TLEs will have to use a test123--1.1.sql file as well. If you have published a basejump-basejump_core without the basejump-basejump_core--2.0.1.sql, just add this file and run dbdev publish again and that should fix it.