[postgis-tickets] [PostGIS] #5237: postgis_extensions_upgrade fails with ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

[PostGIS]

#5237: postgis_extensions_upgrade fails with ERROR: there is no unique or
exclusion constraint matching the ON CONFLICT specification
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 3.3.2
Component:  postgis  |    Version:  3.2.x
 Keywords:           |
---------------------+---------------------------
 Actual error was on a french system with below and mentioned in IRC by a
 DoctorWho80


 {{{
 ERREUR:  il n'existe aucune contrainte unique ou contrainte d'exclusion
 correspondant à la spécification ON CONFLICT
 }}}

 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.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5237>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.