Currently the public.packages view is defined like this:
create or replace view public.packages as
select
pa.id,
pa.package_name,
pa.handle,
pa.partial_name,
newest_ver.version as latest_version,
newest_ver.description_md,
pa.control_description,
pa.control_requires,
pa.created_at,
pa.default_version
from
app.packages pa,
lateral (
select *
from app.package_versions pv
where pv.package_id = pa.id
order by pv.version_struct desc
limit 1
) newest_ver;
Notice how in the lateral clause only app.package_versions are read but there's no app.package_upgrades. This results in the latest version being returned as less if there is one base version with an upgrade.
To reproduce publish an extension with my_ext--1.0.0.sql and my_ext--1.0.0--2.0.0.sql files and notice how the latest version reported is 1.0.0.
To fix, the view should take the maximum version from among the app.package_versions's version column and app.package_upgrade's to_version column.
Currently the
public.packages
view is defined like this:Notice how in the lateral clause only
app.package_versions
are read but there's noapp.package_upgrades
. This results in the latest version being returned as less if there is one base version with an upgrade.To reproduce publish an extension with
my_ext--1.0.0.sql
andmy_ext--1.0.0--2.0.0.sql
files and notice how the latest version reported is1.0.0
.To fix, the view should take the maximum version from among the
app.package_versions
'sversion
column andapp.package_upgrade
'sto_version
column.