Backup restoration failing due to index on h3_lat_lng_to_cell

[ericsvg]

Hello,

Creating a dump using pg_dump and later restoring it fails due to the following error:

ERROR:  function h3_lat_lng_to_cell(point, integer) does not exist
LINE 1:  SELECT h3_lat_lng_to_cell($1::point, $2); 
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT h3_lat_lng_to_cell($1::point, $2); 
CONTEXT:  SQL function "h3_lat_lng_to_cell" during inlining

I believe this is due to the conjunction of two things:

• pg_dump setting search_path to an empty string in the dump it creates

[...]
SELECT pg_catalog.set_config('search_path', '', false);
[...]

• The definition of h3_lat_lng_to_cell not explicitly using the namespace of the point type (public)

CREATE OR REPLACE FUNCTION h3_lat_lng_to_cell(geometry, resolution integer) RETURNS h3index
    AS $$ SELECT h3_lat_lng_to_cell($1::point, $2); $$ IMMUTABLE STRICT PARALLEL SAFE LANGUAGE SQL;
COMMENT ON FUNCTION
    h3_lat_lng_to_cell(geometry, resolution integer)
IS 'Indexes the location at the specified resolution.';

This is not an issue when one can modify the created dump (adding SET search_path TO "$user", public, topology; fixes it), however this causes issues in RDS for a database upgrade.

Not quite sure how to confirm that changing to $1::public.point would fix the problem.

If that helps, here's a the full SQL file created with pg_dump for a reproducible example.

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

CREATE TABLE public.foo (
    bar public.geometry(Point,4326) NOT NULL
);


CREATE INDEX foo_h3_lat_lng_to_cell_idx ON public.foo USING btree (public.h3_lat_lng_to_cell(bar, 15));

Let me know if you need anything else!

[zachasme]

Possibly related to #165. I don't yet know what the proper solution here is. I will have to investigate further, or hope for someone with more knowledge about this to come along. :-)

[ericsvg]

Hi @zachasme , thanks for the feedback. It does seem to be related, and is a bit scary as we're upgrading to Postgres 17 too.

From what I gathered, and as suggested in the other issue as well, this should be fixable by explicitly mentioning the public namespace of any type mentioned in the PLSQL functions, for example:

CREATE OR REPLACE FUNCTION h3_lat_lng_to_cell(geometry, resolution integer) RETURNS h3index
    AS $$ SELECT h3_lat_lng_to_cell($1::public.point, $2); $$ IMMUTABLE STRICT PARALLEL SAFE LANGUAGE SQL;
COMMENT ON FUNCTION
    h3_lat_lng_to_cell(geometry, resolution integer)
IS 'Indexes the location at the specified resolution.';

Note the change to public.point in the cast on line two. It may also be needed for the geometry type, but I'm not sure.

I'm not familiar with how to build and install Postgres extensions locally. I'll try to find the time to dig into it if needed.

[ericsvg]

Small update: creating an index where the PostGIS geometry is cast to POINT works around the issue:

CREATE INDEX ON foo(bar::POINT, 15)

Creating a dump and restoring it works in this case. However this requires remembering to cast the geometry to a point in every query, which is easily forgotten so a fix would be still useful.

Edit: that makes me think the namespace should actually be explicitly written for the geometry type

[Komzpa]

Adding that we were bit by this too (field notes from upgrade: https://gist.github.com/Komzpa/994d5aaf340067ccec0e?permalink_comment_id=5579140#gistcomment-5579140) - we were lucky that we could just drop these indexes and not re-create.

[zachasme]

@Komzpa were the errors related to missing postgis types? I'm hoping this would be resolved if I can fix #165