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.

[strk]

PostGIS 3.1.1 did create a primary key on SRID column of spatial_ref_sys:

​https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.1.1/postgis/postgis.sql.in#L1966-L1973

Can any of our bots reproduce this ?

[strk]

Which PostgreSQL version is the reporting user using ?

[strk]

Dronie reports PASS: postgis extension upgrade 3.1.6--3.3.1! with PostgreSQL version: 11.17 on line 11177 in ​https://dronie.osgeo.org/postgis/postgis/3542/1/3 and with PostgreSQL version: 13.8 on line 10080 in ​https://dronie.osgeo.org/postgis/postgis/3542/1/4

[strk]

The ON CONFLICT construct is new in 3.3.0, as per #5024 and servers the purpose of adding missing records while NOT overriding existing records.

BTW, the NEWS entry is misleading as it says - #5024, Update spatial_ref_sys as part of ALTER EXTENSION update postgis (Paul Ramsey) while there is NEVER an UPDATE (due to ON CONFLICT DO NOTHING)

[doctorwho80]

Replying to strk:

Which PostgreSQL version is the reporting user using ?

PostgreSQL 12.12 (Debian 12.12-1.pgdg110+1) on x86_64. PostGIS 3.3.1

Database server has been updated only with binaries without SQL step :

SELECT postgis_extensions_upgrade();

Some database upgraded successfully some other not due to primary key missing. No explication why some are fine and other are not -_-

[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

[doctorwho80]

It sounds good from my point of view. Many thanks for your support.

[robe]

Replying to 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

[robe]

I feel like it's safest to just not fix an issue that seems to be user error. We are more liable to cause upgrade issues by introducing a fix for something that should never have needed fixing.

[strk]

I suspect the primary key was removed when going from 1.x to 2.x which is when we restricted the allowed range of SRID values. In that occasion the postgis_restore.pl script made an effort of MOVING out-of-range SRID values within the valid range, but doing so might have introduced a conflict (same SRID / different other values) so in order not to still allow restore it dropped the constraint, loaded the data and then tried to re-add the constraint. Maybe in your case re-adding the constraint failed.

Is your problem solved by now ? Were you able to re-add the primary key ? Did you find conflicts ?

[robe]

@strk it wasn't me. The reporter was able to create the primary key on his system. It was only one of his databases that had the issue.