Re: pg_upgrade fails for PostGIS custom SRIDs

On Wed, Feb 19, 2025 at 11:27 AM Jakub Wartak
<jakub(dot)wartak(at)enterprisedb(dot)com> wrote:
>
> Hi pg-hackers and postgis-hackers, sorry for cross-posting but we
> think it really affects how those two products work together. This is
> about pg_upgrade failure for tables with custom SRIDs.
>
> PostGIS is an extension that uses a special table called
> public.spatial_ref_sys for handling available spatial reference
> systems. It holds the numeric SRIDs and textual descriptions of the
> coordinate systems. Assume someone has done this on older PG version
> (assume PostGIS is already installed):
>
> testgis=# SELECT * FROM spatial_ref_sys WHERE srid = 4283;
> 4283 | EPSG | 4283 |
> GEOGCS["GDA94",DATUM["Geocentric_Datum_of_Australia_1994 ... |
> +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,...
>
> -- note the non-standard SRID #4283:
> testgis=# CREATE TABLE table4_4283 (
> testgis(# "id" integer NOT NULL,
> testgis(# "name" "text" NOT NULL,
> testgis(# "location" "public"."geography"(Point,4283) NOT NULL
> testgis(# );
>
> testgis=# INSERT INTO table4_4283 VALUES (1, 'empty',
> ST_GeomFromText('POINT(-71.064544 42.28787)', 4283));
>
> testgis=# select ST_AsText(location) from table4_4283 ;
> st_astext
> POINT(-71.064544 42.28787)
> (1 row)
> testgis=#
>
> and then one tries to upgrade such an installation (let's say PG13 ->
> 16, but also any other) using pg_upgrade. What happens is that
> pg_upgrade (binary mode) will try to:
>
> 1) "/usr/edb/as15/bin/pg_dump" [..] --binary-upgrade --format=custom
> --file="[..]/dump/pg_upgrade_dump_16435.custom" 'dbname=testgis'
>
> 2) "/usr/edb/as15/bin/pg_restore"[..] --create --exit-on-error
> --verbose --dbname template1
> "/tmp/as15/pg_upgrade_output.d/20250219T092709.317/dump/pg_upgrade_dump_16435.custom"
>
> and this is going to fail with "Cannot find SRID in spatial_ref_sys"
>
> $ grep -e EXTENSION -e spatial_ref_sys -e table4 pg_upgrade_dump_16435.log
> pg_restore: read TOC entry 9 (ID 6) for EXTENSION postgis
> pg_restore: read TOC entry 10 (ID 6661) for COMMENT EXTENSION "postgis"
> pg_restore: read TOC entry 1240 (ID 715) for TABLE spatial_ref_sys
> pg_restore: read TOC entry 1241 (ID 6991) for ACL TABLE "spatial_ref_sys"
> pg_restore: read TOC entry 1242 (ID 719) for TABLE table4_4283
> pg_restore: read TOC entry 1243 (ID 6404) for CONSTRAINT
> spatial_ref_sys spatial_ref_sys_pkey
> pg_restore: creating EXTENSION "postgis"
> pg_restore: creating COMMENT "EXTENSION "postgis""
> pg_restore: creating TABLE "public.spatial_ref_sys"
> pg_restore: creating TABLE "public.table4_4283"
> pg_restore: from TOC entry 719; 1259 17528 TABLE table4_4283 postgres
> pg_restore: error: could not execute query: ERROR: Cannot find SRID
> (4283) in spatial_ref_sys
> CREATE TABLE "public"."table4_4283" (
> WHERE oid = '"public"."table4_4283"'::pg_catalog.regclass;
>
> The reason is that spatial_ref_sys is empty at that time (while
> PostGIS runtime needs to use this data for data projection, it has
> hard-coded default SRID 4326 / WGS84 for default geom/geography DDLs
> so it works). The table data exists, but it is empty because EXTENSION
> upgrade code-path uses PG's binary_upgrade_create_empty_extension()
> which seems to hacks around to force-register the EXTENSION, without
> proper data initialization (like CREATE EXTENSION would do). See
> src/bin/pgdump's dumpExtension() which calls this.
>
> Thoughts on this? Search engines say nothing for "pg_upgrade" "Cannot
> find SRID", but apparently PostGIS earlier had a bug report for
> something close [1] and I could not locate mentioned there -hackers
> discussion. Part of me believes that if the run-time of extensions in
> DDL depends on raw table data, then pg_upgrade is to blame, but maybe
> it is an anti-pattern and it should be reported to PostGIS instead?
>
> The problem is that from user or extension perspective there's no
> place (or option) today to alter anything in the custom dump that
> pg_dump (1) does that ends up being used by pg_restore(2), so that
> additional script/data/sequence-of-actions would be used DURING the
> pg_upgrade. Potential solution could also be just to somehow hack
> pg_upgrade/pg_restore to have chance to interrupt after all
> DB/extension initialization is done, but before real user tables were
> created and then allow generic command like e.g. psql testgis -f
> contrib/postgis-3.4/spatial_ref_sys.sql which would properly load the
> data, but that was not tested)
>
> Workaround: easy, just use normal pg_dump/pg_restore (but that might
> be slow / without pg_upgrade). Workaround via placing spatial_ref_sys
> in template1 does not seem to work.
>

Re-sending this just in case, as I was not subscripted to
postgis-devel(at)lists(dot)osgeo(dot)org before.

-J.