[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.3.x
Resolution:           |   Keywords:
----------------------+---------------------------
Comment (by robe):

 Replying to [comment:8 strk]:
 > I think the explanation is that someone removed the primary key on the
 > spatial_ref_sys table on some databases. Wheter or not it's up to us
 > to re-create that index is debatable. The database is in unexpected
 > conditions (read "broken") without that primary key. Maybe what we
 > can do is print a friendly error message for the user recommending to
 > repair the database in order to proceed with the upgrade

 My concern was that these databases might have been around for some time
 even as far back as the 0.1 days and have just undergone continuous
 upgrade.  3.3.0 would be the only version to catch this issue since no
 other version today relied on the primary key being there to upgrade.

 But I see even 1.0 had a primary key -
 https://git.osgeo.org/gitea/postgis/postgis/src/tag/1.0.0/lwgeom/lwpostgis.sql.in#L1957

 How the heck did we even create postgis.sql back in 0.8 and before days -
 https://git.osgeo.org/gitea/postgis/postgis/src/tag/0.8.0

 I see a spatial_ref_sys.sql but nothing that looks like a postgis.sql file
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5237#comment:10>
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.