[postgis-devel] [postgis-users] Upgrade issues

[Paul Ramsey]

> On Sep 3, 2019, at 7:52 PM, Regina Obe <lr at pcorp.us> wrote:
> 
> Is this the first time you are running pg_upgrade?
> 1)      I think most users don’t even know they can use an srid other than 4326 for geography, so this probably hasn’t been exercised by many users
> 2)      This would not be an issue with regular PostGIS upgrade – ALTER EXTENSION postgis UPDATE;
> 3)        It’s because pg_upgrade tries to replicate exactly what you had by first creating an empty PostGIS extension and then loading in all the extension parts from the database.  In theory it should recognize that spatial_ref_sys is a part of PostGIS, so I’m very surprised you run into a situation where spatial_ref_sys table doesn’t exist.  The spatial_ref_sys table being empty is a bit more understandable.
> 4)      I think also there was a time when the geography SRIDs were cached in a secret place, so geography didn’t rely on spatial_ref_sys.  I forget when this changed (might have been at 2.2 or 2.3).
>  
>  
> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of James Sewell
> Sent: Tuesday, September 3, 2019 10:26 PM
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Cc: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> Subject: Re: [postgis-devel] [postgis-users] Upgrade issues
>  
>>> > Paul – any thoughts on how to fix this one?
>>> 
>>> None. Is it easily replicable? Does any non-standard geography srid trigger it?
>  
> Making some progress now.
>  
> It looks like pg_upgrade from any (tested 9.6, 10, -> 11) version of Postgres will fail if any version of PostGIS (tested 2.5.1, 3.0) is installed and a table  with a Geography, non 4326 SRID column exists.
>  
> The failure can take one of two forms:
> a) When the table is created spatial_ref_sys doesn't exist
> b) When the table is created spatial_ref_sys exists but is empty (data isn't added till after the schema import using pg_upgrade)
>  
>  
>  I can't answer is how this has not been hit before???

Yeah, hard to believe, this is very old code… I guess people just don’t use non-WGS84 geography very often, or if they do they don’t think to set up their type constraints to enforce it. Or they’ve never got around to upgrading.

Anyways, the offending code is very old, and here it is:

https://github.com/postgis/postgis/blob/svn-trunk/postgis/gserialized_typmod.c#L296

When reading in the string “geography(point, 4267)” a typmod string, in the case of geography, the system checks that the provided SRID number is in fact a geodetic coordinate system and WHoops, if this is running in the context of pg_upgrade, that means it needs to read spatial_ref_sys, even though it’s still at the “setting up the schemas” stage of the process. 

Since this problem has been around forever, that kind fo points to the fact that maybe enforcing the geodetic nature of geography typmods isn’t the most important piece of data integrity checking we have to do, so the simplest fix would be to excise this check in all patch releases. Then the fix would be to upgrade to the latest patch releases before upgrading.

P.