## #5569 closed defect (fixed)

# postgis_restore doesn't strip 2.1 function signatures

Reported by: | kesar | Owned by: | strk |
---|---|---|---|

Priority: | low | Milestone: | PostGIS 3.4.1 |

Component: | build | Version: | 3.4.x |

Keywords: | postgis_restore | Cc: | pramsey |

## Description

Hi,

we are migrating an old database to a new server and doing the HARD UPGRADE (as instructed in http://postgis.net/docs/manual-3.4/postgis_administration.html#hard_upgrade) from Postgis 2.1.8 to 3.4.

Seems that some deprecated/old functions are not stripped by *postgis_restore*:

CREATE FUNCTION pgis_abs_in(cstring) RETURNS pgis_abs LANGUAGE c IMMUTABLE STRICT AS '$libdir/postgis-2.1', 'pgis_abs_in'; CREATE FUNCTION pgis_abs_out(pgis_abs) RETURNS cstring LANGUAGE c IMMUTABLE STRICT AS '$libdir/postgis-2.1', 'pgis_abs_out'; CREATE FUNCTION pgis_geometry_accum_finalfn(pgis_abs) RETURNS geometry[] LANGUAGE c AS '$libdir/postgis-2.1', 'pgis_geometry_accum_finalfn'; CREATE FUNCTION pgis_geometry_accum_transfn(pgis_abs, geometry) RETURNS pgis_abs LANGUAGE c AS '$libdir/postgis-2.1', 'pgis_geometry_accum_transfn'; CREATE FUNCTION pgis_geometry_collect_finalfn(pgis_abs) RETURNS geometry LANGUAGE c AS '$libdir/postgis-2.1', 'pgis_geometry_collect_finalfn'; CREATE FUNCTION pgis_geometry_makeline_finalfn(pgis_abs) RETURNS geometry LANGUAGE c AS '$libdir/postgis-2.1', 'pgis_geometry_makeline_finalfn'; CREATE FUNCTION pgis_geometry_polygonize_finalfn(pgis_abs) RETURNS geometry LANGUAGE c AS '$libdir/postgis-2.1', 'pgis_geometry_polygonize_finalfn'; CREATE FUNCTION pgis_geometry_union_finalfn(pgis_abs) RETURNS geometry LANGUAGE c AS '$libdir/postgis-2.1', 'pgis_geometry_union_finalfn'; CREATE FUNCTION st_askml(geog geography, maxdecimaldigits integer DEFAULT 15) RETURNS text LANGUAGE sql IMMUTABLE STRICT AS $_$SELECT _ST_AsKML(2, $1, $2, null)$_$; CREATE FUNCTION st_askml(geom geometry, maxdecimaldigits integer DEFAULT 15) RETURNS text LANGUAGE sql IMMUTABLE STRICT AS $_$ SELECT _ST_AsKML(2, ST_Transform($1,4326), $2, null); $_$; CREATE FUNCTION st_dwithin(geography, geography, double precision) RETURNS boolean LANGUAGE sql IMMUTABLE AS $_$SELECT $1 && _ST_Expand($2,$3) AND $2 && _ST_Expand($1,$3) AND _ST_DWithin($1, $2, $3, true)$_$;

It also tries to add comments to non-existent functions and constraints to already existing Postgis 3.4 tables.

Here are the error messages:

ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_abs_in(cstring) does not exist ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_abs_out(pgis_abs) does not exist ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_geometry_accum_finalfn(pgis_abs) does not exist ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_geometry_accum_transfn(pgis_abs, geometry) does not exist ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_geometry_collect_finalfn(pgis_abs) does not exist ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_geometry_makeline_finalfn(pgis_abs) does not exist ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_geometry_polygonize_finalfn(pgis_abs) does not exist ERROR: could not access file "$libdir/postgis-2.1": No such file or directory ERROR: function public.pgis_geometry_union_finalfn(pgis_abs) does not exist ERROR: function st_asgeojson(integer, geography, integer, integer) does not exist ERROR: function st_asgeojson(integer, geometry, integer, integer) does not exist ERROR: function st_askml(integer, geography, integer, text) does not exist ERROR: function st_count(text, text, boolean) does not exist ERROR: function st_count(text, text, integer, boolean) does not exist ERROR: function st_force3d(geometry) does not exist ERROR: function st_force3dm(geometry) does not exist ERROR: function st_force3dz(geometry) does not exist ERROR: function st_force4d(geometry) does not exist ERROR: function st_histogram(text, text, integer, integer, boolean) does not exist ERROR: function st_histogram(text, text, integer, boolean, integer, boolean) does not exist ERROR: function st_histogram(text, text, integer, integer, double precision[], boolean) does not exist ERROR: function st_histogram(text, text, integer, boolean, integer, double precision[], boolean) does not exist ERROR: function st_quantile(text, text, integer, double precision[]) does not exist ERROR: function st_quantile(text, text, integer, boolean, double precision[]) does not exist ERROR: function st_summarystats(text, text, boolean) does not exist ERROR: function st_summarystats(text, text, integer, boolean) does not exist ERROR: function validatetopology(character varying) does not exist ERROR: multiple primary keys for table "layer" are not allowed ERROR: relation "layer_schema_name_table_name_feature_column_key" already exists ERROR: relation "topology_name_key" already exists ERROR: multiple primary keys for table "topology" are not allowed ERROR: rule "geometry_columns_delete" for relation "geometry_columns" already exists ERROR: rule "geometry_columns_insert" for relation "geometry_columns" already exists ERROR: rule "geometry_columns_update" for relation "geometry_columns" already exists ERROR: trigger "layer_integrity_checks" for relation "layer" already exists ERROR: constraint "layer_topology_id_fkey" for relation "layer" already exists ERROR: multiple primary keys for table "spatial_ref_sys" are not allowed

### Attachments (1)

### Change History (27)

### comment:1 by , 12 months ago

### comment:2 by , 12 months ago

Also please report the full target version from which postgis_restore was built (unfortunately we never added a —version switch to it so you'll have to figure out in some other way)

### comment:3 by , 12 months ago

Cc: | added |
---|

The `pgis_abs_in`

signature was deprecated in 2.5.0 according to postgis/postgis_legacy.c and there's a mention in it in doc/rfc/postgis_rfc_03_sheet.txt that I don't understand (rfc03 was commited by Paul in 2009: ideas Paul?)

What's clearly missing is a DROP of that function in any of the upgrade scripts, which means whoever is upgrading from a version < 2.5.0 might have that function left in the database. The regress/core/regress.sql test should catch this case reporting `unexpected probin`

but none of our bots are testing upgrades from 2.4.x: oldest upgrade is tested by woodie from 2.5.9: https://woodie.osgeo.org/repos/30/pipeline/1305/14

So, Paul or Regina: any idea why we'd ever want to keep those old functions rather than dropping them on upgrade (adding them to postgis_after_upgrade or something ?)

The only reason for that I can think of is that our upgrade script is not able to change the "geometry" TYPE definition and thus will STILL use the old functions, but it would not seem a good reason to keep old function around to me (rather we should fix the upgrade)

### by , 12 months ago

Attachment: |
postgis21_manifest.txt
added |
---|

output from `pg_restore -l`

, non-postgis information redacted

### comment:4 by , 12 months ago

Hi & thank you!

OK, I added a sanitized version of the manifest output as an attachment.

The version of *postgis_restore* we are using is installed from Postgresql.org RHEL8 pgdg13-repository's package *postgis34_13*

$ dnf info 'postgis34_13' | grep Source Source : postgis34_13-3.4.0-1PGDG.rhel8.src.rpm

### comment:5 by , 12 months ago

Looking at the generated DATA section in postgis_restore.pl, and focusing to a single signature, we have:

In manifest:

6714; 0 0 COMMENT public FUNCTION st_asgeojson(gj_version integer, geog geography, maxdecimaldigits integer, options integer) postgres 1019; 1255 304728 FUNCTION public st_asgeojson(integer, geography, integer, integer) postgres

In skip list:

COMMENT FUNCTION st_asgeojson(version integer, geog geography, maxdecimaldigits integer, options integer) FUNCTION st_asgeojson(integer, geography, integer, integer)

Sounds like our upgrade procedure is getting some parameter names wrong

kesar: are you able by any chance to provide also a schema-only dump for us to test restoring ?

### comment:6 by , 12 months ago

The source of our `st_asgeojson(version integer...`

SKIP line is postgis/legacy.sql.in which is where that signature is found. There's no mention anywhere about a `gj_version integer`

parameter ever being found in postgis, for ST_AsGeoJSON

### comment:7 by , 12 months ago

The `ST_AsGeoJson(gj_version int4, geog geography, maxdecimaldigits int4 DEFAULT 15, options int4 DEFAULT 0)`

signature was present in 2.5 branch, dropped in 3.0

### comment:8 by , 12 months ago

The actual source is postgis/uninstall_legacy.sql which drops the "version integer" but not the "gj_version integer". I guess we want to enhance utils/create_skip_signatures.pl to also parse _postgis_drop_function_by_identity as that's where the knowledge about obsoleted functions is now encoded

### comment:9 by , 12 months ago

I would add that this `ST_AsGeoJSON`

case should emerge in upgrades dump-reload based upgrades from 2.5 but evidently none of our bots are testing that !
Commit [ecea1b90e42943d78affaf8ddb96dbacc198f9a9/git] added support for those tests in run_test.pl so we want to enable them in CI.

I filed https://trac.osgeo.org/postgis/ticket/5573 for this

### comment:10 by , 12 months ago

The `ST_AsGeoJSON`

issue is now confirmed by CI:
https://woodie.osgeo.org/repos/30/pipeline/1324/7#L9821

### comment:11 by , 12 months ago

With recent commits we got CI successfully test of hard-upgrade procedure with a dump of a database created via script from 2.5.9 — the code is in the current stable-3.4 branch. Your dump is pretty old, coming from 2.1. Our CI infrastructure is not ready to test 2.1 and we don't even have anymore the information about which PostgreSQL version worked with 2.1 ( 2.5 is the oldest we have info for: https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS ).

Getting our hands on that structure-only dump could be useful to run some manual tests

### comment:12 by , 12 months ago

Cc: | added; removed |
---|---|

Summary: | postgis_restore doesn't strip some old functions → postgis_restore doesn't strip 2.1 function signatures |

### comment:13 by , 12 months ago

I found a way to test without having the dump:

touch x; postgis_restore -v -L postgis21_manifest.txt x 2>&1 | grep KEEP

we will not want any postgis object in the output.

With the current code the left over are:

KEEP: SHELLTYPE pgis_abs KEEP: FUNCTION pgis_abs_in(cstring) KEEP: FUNCTION pgis_abs_out(pgis_abs) KEEP: FUNCTION pgis_geometry_accum_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_accum_transfn(pgis_abs, geometry) KEEP: FUNCTION pgis_geometry_collect_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_makeline_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_polygonize_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_union_finalfn(pgis_abs)

### comment:14 by , 12 months ago

In addition to those functions reported below, we also have a lot of unexpected COMMENT retained which shouldnt be:

KEEP: COMMENT FUNCTION st_asgeojson(gj_version integer, geog geography, maxdecimaldigits integer, options integer) KEEP: COMMENT FUNCTION st_asgeojson(gj_version integer, geom geometry, maxdecimaldigits integer, options integer) KEEP: COMMENT FUNCTION st_askml(geog geography, maxdecimaldigits integer) KEEP: COMMENT FUNCTION st_askml(geom geometry, maxdecimaldigits integer) KEEP: COMMENT FUNCTION st_askml(version integer, geog geography, maxdecimaldigits integer, nprefix text) KEEP: COMMENT FUNCTION st_aslatlontext(geometry, text) KEEP: COMMENT FUNCTION st_buffer(geometry, double precision, integer) KEEP: COMMENT FUNCTION st_buffer(geometry, double precision, text) KEEP: COMMENT FUNCTION st_count(rastertable text, rastercolumn text, exclude_nodata_value boolean) KEEP: COMMENT FUNCTION st_count(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean) KEEP: COMMENT FUNCTION st_coveredby(geography, geography) KEEP: COMMENT FUNCTION st_covers(geography, geography) KEEP: COMMENT FUNCTION st_distance(geography, geography, boolean) KEEP: COMMENT FUNCTION st_dwithin(geography, geography, double precision, boolean) KEEP: COMMENT FUNCTION st_histogram(rastertable text, rastercolumn text, nband integer, bins integer, "right" boolean, OUT min double precision, OUT max double precision, OUT count bigint, OUT percent double precision) KEEP: COMMENT FUNCTION st_histogram(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, bins integer, "right" boolean, OUT min double precision, OUT max double precision, OUT count bigint, OUT percent double precision) KEEP: COMMENT FUNCTION st_histogram(rastertable text, rastercolumn text, nband integer, bins integer, width double precision[], "right" boolean, OUT min double precision, OUT max double precision, OUT count bigint, OUT percent double precision) KEEP: COMMENT FUNCTION st_histogram(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, bins integer, width double precision[], "right" boolean, OUT min double precision, OUT max double precision, OUT count bigint, OUT percent double precision) KEEP: COMMENT FUNCTION st_intersects(geography, geography) KEEP: COMMENT FUNCTION st_isvaliddetail(geometry, integer) KEEP: COMMENT FUNCTION st_linecrossingdirection(geom1 geometry, geom2 geometry) KEEP: COMMENT FUNCTION st_orderingequals(geometrya geometry, geometryb geometry) KEEP: COMMENT FUNCTION st_quantile(rastertable text, rastercolumn text, nband integer, quantiles double precision[], OUT quantile double precision, OUT value double precision) KEEP: COMMENT FUNCTION st_quantile(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, quantiles double precision[], OUT quantile double precision, OUT value double precision) KEEP: COMMENT FUNCTION st_setsrid(geometry, integer) KEEP: COMMENT FUNCTION st_srid(geometry) KEEP: COMMENT FUNCTION st_summarystats(rastertable text, rastercolumn text, exclude_nodata_value boolean, OUT count bigint, OUT sum double precision, OUT mean double precision, OUT stddev double precision, OUT min double precision, OUT max double precision) KEEP: COMMENT FUNCTION st_summarystats(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, OUT count bigint, OUT sum double precision, OUT mean double precision, OUT stddev double precision, OUT min double precision, OUT max double precision) KEEP: COMMENT FUNCTION validatetopology(toponame character varying)

### comment:15 by , 12 months ago

Ticket #3078 deals with the enhancement of not keeping those function comments.

### comment:16 by , 12 months ago

With the changes in https://git.osgeo.org/gitea/postgis/postgis/pulls/160 the only left over signatures are:

KEEP: SHELLTYPE pgis_abs KEEP: FUNCTION pgis_abs_in(cstring) KEEP: FUNCTION pgis_abs_out(pgis_abs) KEEP: FUNCTION pgis_geometry_accum_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_accum_transfn(pgis_abs, geometry) KEEP: FUNCTION pgis_geometry_collect_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_makeline_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_polygonize_finalfn(pgis_abs) KEEP: FUNCTION pgis_geometry_union_finalfn(pgis_abs)

### comment:17 by , 12 months ago

### comment:22 by , 12 months ago

kesar: are you able by any chance to provide also a schema-only dump for us to test restoring ?

→

I found a way to test without having the dump:

OK, nice 👌

Thank you and have a nice rest of the week!

### comment:25 by , 12 months ago

Thanks kesar, feel free to try the newest postgis_restore in the stable-3.4 branch as I now expect that to give you NO error

### comment:26 by , 12 months ago

Hi, sorry for the late reply - the new version works great, thank you very much!

**Note:**See TracTickets for help on using tickets.

Can you share the manifest of your dump ? You obtain it with

`pg_restore -l <yourdumpfile>`