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

[rmrodriguez at carto.com]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200702/2d8bb3c8/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/20200702/2d8bb3c8/attachment.png>