[postgis-users] Fwd: type "geometry" does not exist, but it exists

[pham lan]

Hi Raúl Marín Rodríguez,

Thank you for your answer in this topic. You are right, I actually checked
the content of the backup file and saw that pg_dump set the search_path to
empty, that explains why it was working when I tried to run the query
manually but not during the restoration.
SELECT pg_catalog. set_config('search_path', '', false);

After discovering that, I changed the function from "geog::geometry" to
"geog::public.geometry" and it works.

Best regards

On Thu, Jul 2, 2020 at 7:04 PM <rmrodriguez at carto.com> wrote:

> I  don't remember the exact details, but restoring functions require
> extension types to be fully qualified.
>
> Can you try changing the definition of the _togeom function from
> "geog::geometry" to "geog::public.geometry"?
>
> On Thu, Jul 2, 2020 at 6:36 PM pham lan <phamlankt at gmail.com> wrote:
>
>>
>> Hello all,
>>
>> I would like to ask for your help in understanding a postgres issue that
>> I could not find out till now.
>> So I have a postgres dump file of 159G created by pg_dump that I want to
>> restore to my database. It is running well till it throws me the following
>> error:
>>
>> $ psql -a -U postgres -d mydb < /opt/postgres_restore_files/db_mydb_2020-06-11_18-00.dmp
>> .....
>> CREATE INDEX
>> CREATE INDEX
>> CREATE INDEX
>> ERROR:  type "geometry" does not exist
>> LINE 1: SELECT geog::geometry
>>                      ^
>> QUERY:  SELECT geog::geometry
>> CONTEXT:  PL/pgSQL function public._togeom(public.geography) line 3 at RETURN
>> STATEMENT:  CREATE INDEX my_index ON mydb.geo_zone USING gist (public._togeom(geom));
>> CREATE INDEX
>> CREATE INDEX
>> CREATE INDEX
>> CREATE INDEX
>>
>> I already looked and tried the comment on similar issue in the following links but it does not help:
>> https://stackoverflow.com/questions/6850500/postgis-installation-type-geometry-does-not-exist
>>
>>
>>  Below are the definitions:
>> CREATE FUNCTION public._togeom(geog public.geography) RETURNS public.geometry
>>     LANGUAGE plpgsql IMMUTABLE
>>     AS $$
>>         BEGIN
>>                 RETURN geog::geometry;
>>         END;
>> $$;
>>
>> ALTER FUNCTION public._togeom(geog public.geography) OWNER TO postgres;
>>
>> CREATE TABLE mydb.geo_zone (
>>     ...
>>     geom public.geography,
>>     ...
>> );
>> ALTER TABLE mydb.geo_zone OWNER TO my_user;
>> ....
>> CREATE INDEX my_index ON mydb.geo_zone USING gist (public._togeom(geom));
>>
>> I can see type geometry exists in public schema after creating extension
>> postgis in mydb, not sure why it throws that error during the restore
>> procedure. Also I tried afterward to run the CREATE INDEX command again
>> manually and it works without any error and I can see the index "my_index"
>> created. But of course i don't want this manual fix, just try to understand
>> what causes the issue.
>>
>> Any idea from you is highly appreciated! Thanks.
>> [image: image.png]
>>
>>
>> Best regards,
>> Lan Pham
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> --
> Raúl Marín Rodríguez
> carto.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200706/00d9644d/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 24153 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200706/00d9644d/attachment.png>