postgis_extensions_upgrade fails with ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

[robe]

Actual error was on a french system with below and mentioned in IRC by a DoctorWho80

NOTICE:  Updating extension postgis from 3.1.1 to 3.3.1
ERREUR:  il n'existe aucune contrainte unique ou contrainte d'exclusion correspondant à la spécification ON CONFLICT
CONTEXTE : instruction SQL « ALTER EXTENSION postgis UPDATE TO "3.3.1"; »
fonction PL/pgSQL postgis_extensions_upgrade(), ligne 79 à EXECUTE

We traced the issue down to missing unique constraint on spatial_ref_sys.

the fix was to do

ALTER TABLE IF EXISTS spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_pkey PRIMARY KEY (srid);

and then rerun:

SELECT postgis_extensions_upgrade();

It is unclear if the issue was at a time postgis did not have a primary key on this table, or that some time ago whoever was managing this database explicitly removed the primary key.

If it is our fault (might be good to do anyway), we should guard against it by adding the primary key if we find it does not exist.

This is only an issue for PostGIS 3.3+ because prior versions do not try to load missing spatial_ref_sys records.