Обсуждение: How to debug extension update
Hi,
Updating extension fails:
ERROR: type does not exist
but i can describe it, ie search_path is correct.
pg version 15.10.
teglms=# \dx post*
List of installed extensions
Name | Version | Schema | Description
------------------+---------+----------+------------------------------------------------------------
postgis | 3.3.6 | postgis | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.3.6 | postgis | PostGIS raster types and functions
postgis_topology | 3.3.3 | topology | PostGIS topology spatial types and functions
(3 rows)
teglms=# alter extension postgis_topology update to "3.3.6";
ERROR: type "geometry" does not exist
teglms=# \dT+ geometry
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
---------+----------+---------------+------+----------+------------+-------------------+----------------------------------------------
postgis | geometry | geometry | var | | eglmsadmin | | postgis type: The type representing spatial .
| | | | | | |.features with planar coordinate systems.
(1 row)
teglms=# select user;
user
------------
eglmsadmin
(1 row)
teglms=# show search_path ;
search_path
-------------------
postgis, topology
(1 row)
Why update can't find an extension, while i can describe it and search_path seems to be correct?
br
Kaido
On Mon, Jan 6, 2025 at 11:49 AM kaido vaikla <kaido.vaikla@gmail.com> wrote:
Hi,
Updating extension fails:
ERROR: type does not exist
but i can describe it, ie search_path is correct.
pg version 15.10.
teglms=# \dx post*
List of installed extensions
Name | Version | Schema | Description
------------------+---------+----------+------------------------------------------------------------
postgis | 3.3.6 | postgis | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.3.6 | postgis | PostGIS raster types and functions
postgis_topology | 3.3.3 | topology | PostGIS topology spatial types and functions
(3 rows)
teglms=# alter extension postgis_topology update to "3.3.6";
ERROR: type "geometry" does not exist
teglms=# \dT+ geometry
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
---------+----------+---------------+------+----------+------------+-------------------+----------------------------------------------
postgis | geometry | geometry | var | | eglmsadmin | | postgis type: The type representing spatial .
| | | | | | |.features with planar coordinate systems.
(1 row)
teglms=# select user;
user
------------
eglmsadmin
(1 row)
teglms=# show search_path ;
search_path
-------------------
postgis, topology
(1 row)
Why update can't find an extension, while i can describe it and search_path seems to be correct?
Type geometry is owned by eglmsadmin, but there do not appear to be any Access privileges granted. Thus, maybe whatever user you connected as doesn't have privs to access it?
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
kaido vaikla <kaido.vaikla@gmail.com> writes:
> Why update can't find an extension, while i can describe it and search_path
> seems to be correct?
Extension scripts are not run with the session's prevailing
search_path, but with a search path built from the extension's
dependencies. In this case, schema postgis would be included
in that path only if extension postgis_topology specifies
requires = 'postgis'
in its postgis_topology.control file. It kinda sounds like that
might be missing? If it's present, then Ron's thought about
missing access privileges for the postgis schema might be the
answer.
regards, tom lane
Hello,
I am also getting the same error without the upgrade scenario.
postgres=# CREATE EXTENSION postgis SCHEMA postgis;
CREATE EXTENSION
postgres=# CREATE EXTENSION postgis_topology SCHEMA topology;
ERROR: type "geometry" does not exist
postgres=# show search_path ;
search_path
---------------------------
postgis, topology, public
(1 row)
postgres=# CREATE EXTENSION postgis_topology;
ERROR: type "geometry" does not exist
postgres=# SELECT current_schema();
current_schema
----------------
postgis
(1 row)
Regards,
Zaid
On Tue, Jan 7, 2025 at 12:58 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
kaido vaikla <kaido.vaikla@gmail.com> writes:
> Why update can't find an extension, while i can describe it and search_path
> seems to be correct?
Extension scripts are not run with the session's prevailing
search_path, but with a search path built from the extension's
dependencies. In this case, schema postgis would be included
in that path only if extension postgis_topology specifies
requires = 'postgis'
in its postgis_topology.control file. It kinda sounds like that
might be missing? If it's present, then Ron's thought about
missing access privileges for the postgis schema might be the
answer.
regards, tom lane
1) requires = 'postgis'
$ cat postgis_topology.control
# postgis topology extension
comment = 'PostGIS topology spatial types and functions'
default_version = '3.3.6'
relocatable = false
schema = topology
requires = postgis
So i changed it
requires = postgis -> requires = 'postgis'
but no help.
2) about missing access privileges
I checked different databases with
\dT+ *.*
data types "Access privileges" is everywhere empty.
As my subject is, is it there a way somehow debug "alter extension update" statement to figure out what is going on behind this?
br
Kaido
On Mon, 6 Jan 2025 at 22:05, Zaid Shabbir <zaidshabbir@gmail.com> wrote:
Hello,I am also getting the same error without the upgrade scenario.postgres=# CREATE EXTENSION postgis SCHEMA postgis;
CREATE EXTENSION
postgres=# CREATE EXTENSION postgis_topology SCHEMA topology;
ERROR: type "geometry" does not exist
postgres=# show search_path ;
search_path
---------------------------
postgis, topology, public
(1 row)
postgres=# CREATE EXTENSION postgis_topology;
ERROR: type "geometry" does not exist
postgres=# SELECT current_schema();
current_schema
----------------
postgis
(1 row)
Regards,ZaidOn Tue, Jan 7, 2025 at 12:58 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:kaido vaikla <kaido.vaikla@gmail.com> writes:
> Why update can't find an extension, while i can describe it and search_path
> seems to be correct?
Extension scripts are not run with the session's prevailing
search_path, but with a search path built from the extension's
dependencies. In this case, schema postgis would be included
in that path only if extension postgis_topology specifies
requires = 'postgis'
in its postgis_topology.control file. It kinda sounds like that
might be missing? If it's present, then Ron's thought about
missing access privileges for the postgis schema might be the
answer.
regards, tom lane
kaido vaikla <kaido.vaikla@gmail.com> writes:
> 2) about missing access privileges
> I checked different databases with
> \dT+ *.*
What I was suspicious of is whether you have "usage" privilege on the
schema that the postgis datatype is installed in. The above command
would not tell you that.
regards, tom lane
On Tue, 14 Jan 2025 at 16:27, Tom Lane <tgl@sss.pgh.pa.us> wrote:
What I was suspicious of is whether you have "usage" privilege on the
schema that the postgis datatype is installed in. The above command
would not tell you that.
regards, tom lane
usage privilege exists:
teglms=# \dT geometry
List of data types
Schema | Name | Description
---------+----------+--------------------------------------------------------------------------------------
postgis | geometry | postgis type: The type representing spatial features with planar coordinate systems.
(1 row)
teglms=# \dn+ postgis
List of schemas
Name | Owner | Access privileges | Description
---------+------------+------------------------------+-------------
postgis | eglmsadmin | eglmsadmin=UC/eglmsadmin +|
| | gr_postgis_r=U/eglmsadmin +|
| | gr_postgis_rw=U/eglmsadmin +|
| | gr_postgis_rwx=UC/eglmsadmin |
(1 row)
br
Kaido