[postgis-devel] PostGIS upgrade error

[Regina Obe]

It looks like you are running a 2.4 before the view upgrade fix was applied.  If you can’t upgrade your old cluster to the latest 2.4 micro, you can apply the attached create view definitions and that should fix your issue.  

 

The issue is that I think in PG 12, pg_constraint.consrc was taken out so we had to rework the views to use pg_get_constraintdef function instead.

Since pg_upgrade tries to run the old view definition, it fails.

 

Hope that helps,

Regina

 

 

 

From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of Nikhil Shetty
Sent: Thursday, August 18, 2022 11:50 AM
To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: [postgis-devel] PostGIS upgrade error

 

Hi Team,

 

We are upgrading PostgreSQL (11.7 to 13.6) and PostGIS (2.4.4 to 3.2.1) but we are getting below error during pg_upgrade.

 

pg_restore: while PROCESSING TOC:

pg_restore: from TOC entry 309; 1259 17730 VIEW geometry_columns postgres

pg_restore: error: could not execute query: ERROR:  column s.consrc does not exist

LINE 28:             "replace"("split_part"("s"."consrc", ''''::"text...

                                            ^

HINT:  Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".

Command was:

-- For binary upgrade, must preserve pg_type oid

SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17732'::pg_catalog.oid);

 

 

-- For binary upgrade, must preserve pg_type array oid

SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('17731'::pg_catalog.oid);

 

 

-- For binary upgrade, must preserve pg_class oids

SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('17730'::pg_catalog.oid);

 

CREATE VIEW "public"."geometry_columns" AS

 SELECT ("current_database"())::character varying(256) AS "f_table_catalog",

    "n"."nspname" AS "f_table_schema",

    "c"."relname" AS "f_table_name",

    "a"."attname" AS "f_geometry_column",

    COALESCE("public"."postgis_typmod_dims"("a"."atttypmod"), "sn"."ndims", 2) AS "coord_dimension",

    COALESCE(NULLIF("public"."postgis_typmod_srid"("a"."atttypmod"), 0), "sr"."srid", 0) AS "srid",

    ("replace"("replace"(COALESCE(NULLIF("upper"("public"."postgis_typmod_type"("a"."atttypmod")), 'GEOMETRY'::"text"), "st"."type", 'GEOMETRY'::"text"), 'ZM'::"text", ''::"text"), 'Z'::"text", ''::"text"))::character varying(30) AS "type"

   FROM (((((("pg_class" "c"

     JOIN "pg_attribute" "a" ON ((("a"."attrelid" = "c"."oid") AND (NOT "a"."attisdropped"))))

     JOIN "pg_namespace" "n" ON (("c"."relnamespace" = "n"."oid")))

     JOIN "pg_type" "t" ON (("a"."atttypid" = "t"."oid")))

     LEFT JOIN ( SELECT "s"."connamespace",

            "s"."conrelid",

            "s"."conkey",

            "replace"("split_part"("s"."consrc", ''''::"text", 2), ')'::"text", ''::"text") AS "type"

           FROM "pg_constraint" "s"

          WHERE ("s"."consrc" ~~* '%geometrytype(% = %'::"text")) "st" ON ((("st"."connamespace" = "n"."oid") AND ("st"."conrelid" = "c"."oid") AND ("a"."attnum" = ANY ("st"."conkey")))))

     LEFT JOIN ( SELECT "s"."connamespace",

            "s"."conrelid",

            "s"."conkey",

            ("replace"("split_part"("s"."consrc", ' = '::"text", 2), ')'::"text", ''::"text"))::integer AS "ndims"

           FROM "pg_constraint" "s"

          WHERE ("s"."consrc" ~~* '%ndims(% = %'::"text")) "sn" ON ((("sn"."connamespace" = "n"."oid") AND ("sn"."conrelid" = "c"."oid") AND ("a"."attnum" = ANY ("sn"."conkey")))))

     LEFT JOIN ( SELECT "s"."connamespace",

            "s"."conrelid",

            "s"."conkey",

            ("replace"("replace"("split_part"("s"."consrc", ' = '::"text", 2), ')'::"text", ''::"text"), '('::"text", ''::"text"))::integer AS "srid"

           FROM "pg_constraint" "s"

          WHERE ("s"."consrc" ~~* '%srid(% = %'::"text")) "sr" ON ((("sr"."connamespace" = "n"."oid") AND ("sr"."conrelid" = "c"."oid") AND ("a"."attnum" = ANY ("sr"."conkey")))))

  WHERE (("c"."relkind" = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) AND (NOT ("c"."relname" = 'raster_columns'::"name")) AND ("t"."typname" = 'geometry'::"name") AND (NOT "pg_is_other_temp_schema"("c"."relnamespace")) AND "has_table_privilege"("c"."oid", 'SELECT'::"text"));

 

-- For binary upgrade, handle extension membership the hard way

ALTER EXTENSION "postgis" ADD VIEW "public"."geometry_columns";

 

 

We are creating below links before the upgrade 

 

>>>>>>>:/usr/pgsql-13/lib $ ls -l | grep postgis

lrwxrwxrwx  1 root root      30 Aug 18 02:35 postgis-2.4.so <http://2.4.so>  -> /usr/pgsql-13/lib/postgis-3.so

-rwxr-xr-x  1 root root 1076312 May 26  2021 postgis-3.so

-rwxr-xr-x  1 root root  858608 May 26  2021 postgis_raster-3.so

-rwxr-xr-x  1 root root  435648 May 26  2021 postgis_sfcgal-3.so

-rwxr-xr-x  1 root root  564928 May 26  2021 postgis_topology-3.so

lrwxrwxrwx  1 root root      37 Aug 18 02:35 rtpostgis-2.4.so <http://2.4.so>  -> /usr/pgsql-13/lib/postgis_raster-3.so

 

Thanks,

Nikhil

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220818/cf9f7bfe/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgis_views.sql
Type: application/octet-stream
Size: 4153 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220818/cf9f7bfe/attachment-0001.obj>