前缀为"_“的PGRouting函数未找到

Question

这里是新的邮政/后/灌浆星系。我必须创建Posgres服务器的docker实例。它上安装了几个postgres扩展，提供了公共模式中可用的函数，其中一些以下划线为前缀。构建容器运行良好，但是当我试图将现有的DB Schema导入到容器中时，它会失败，出现以下错误：

2020-07-31 08:15:40.623 UTC [90] ERROR:  function public._pgr_dijkstra(text, anyarray, anyarray, boolean, boolean, boolean) does not exist
2020-07-31 08:15:40.623 UTC [90] STATEMENT:  GRANT ALL ON FUNCTION public._pgr_dijkstra(edges_sql text, start_vids anyarray, end_vids anyarray, directed boolean, only_cost boolean, normal boolean, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision) TO owenzek;
psql:/scripts/04_mis_db_dev_schema.sql:23572: ERROR:  function public._pgr_dijkstra(text, anyarray, anyarray, boolean, boolean, boolean) does not exist

很明显，PG路由功能是不可用的。

如果我用\dx检查容器实例中安装的扩展，PGrouting似乎就在这里：

                                                                    List of installed extensions
          Name          | Version |   Schema   |                                                     Description                                                     
------------------------+---------+------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer   | 3.0.1   | public     | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 fuzzystrmatch          | 1.1     | public     | determine similarities and distance between strings
 ogr_fdw                | 1.0     | public     | foreign-data wrapper for GIS data access
 pgrouting              | 3.0.2   | public     | pgRouting Extension
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 plr                    | 8.4     | public     | load R interpreter and execute R script from within a database
 pointcloud             | 1.2.1   | public     | data type for lidar point clouds
 pointcloud_postgis     | 1.2.1   | public     | integration for pointcloud LIDAR data and PostGIS geometry data
 postgis                | 3.0.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_raster         | 3.0.1   | public     | PostGIS raster types and functions
 postgis_sfcgal         | 3.0.1   | public     | PostGIS SFCGAL functions
 postgis_tiger_geocoder | 3.0.1   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.0.1   | topology   | PostGIS topology spatial types and functions
 tablefunc              | 1.0     | public     | functions that manipulate whole tables, including crosstab

\df public._pgr_dijkstra返回以下内容：

\df public._pgr_dijkstra
List of functions
 Schema |     Name      | Result data type |                                                                                                                                                                    Argument data types                                                                                                                                                                     | Type 
--------+---------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
 public | _pgr_dijkstra | SETOF record     | edges_sql text, start_vids anyarray, end_vids anyarray, directed boolean DEFAULT true, only_cost boolean DEFAULT false, normal boolean DEFAULT true, n_goals bigint DEFAULT 0, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision | func
(1 row)

所以，到目前为止，我对自己做错了什么感到有点困惑:/有什么线索我应该检查吗？

下面:这些文件到目前为止都用光了。

文档:

FROM postgres:11.2
ADD sql/*.sql /scripts/
LABEL maintainer="TRALALA"

ENV POSTGRES_VERSION 11
ENV POSTGIS_VERSION 3

# GIS Extensions installation
RUN apt-get update \
    && apt-get -y --no-install-recommends install \
        postgresql-common \
        postgis \
        postgresql-${POSTGRES_VERSION}-postgis-${POSTGIS_VERSION} \
        postgresql-${POSTGRES_VERSION}-postgis-${POSTGIS_VERSION}-scripts \
        postgresql-${POSTGRES_VERSION}-ogr-fdw \
        postgresql-${POSTGRES_VERSION}-cron \
        postgresql-plpython3-${POSTGRES_VERSION} \
        postgresql-${POSTGRES_VERSION}-pgrouting \
        postgresql-${POSTGRES_VERSION}-pgrouting-scripts \
        # postgresql-${POSTGRES_VERSION}-plr \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get update && apt-get autoremove -y

# Pointcloud install
RUN apt-get update \
    && apt-get install -y --no-install-recommends \
        git \
        ca-certificates \
        build-essential \
        autoconf \
        automake \
        cmake \
        zlib1g-dev \
        postgresql-server-dev-all \
        libxml2-dev \
        ## PGRouting librairies
        libblkid-dev \
        e2fslibs-dev \
        libboost-all-dev \
        libaudit-dev \
    && rm -rf /var/lib/apt/lists/* \
    ## PGRouting install
    && git clone git://github.com/pgRouting/pgrouting.git \
    && cd pgrouting \
    && git checkout v3.0.2 \
    && mkdir build \
    && cd build \
    && cmake  .. \
    && make \
    && make install \
    && cd ../.. \
    && rm -r pgrouting \
    ## Laz-Perf install (pgpointcloud)
    && git clone https://github.com/verma/laz-perf.git \
    && cd laz-perf \
    && cmake . \
    && make \
    && make install \
    && cd .. \
    && rm -r laz-perf \
    ## PGPointcloud install
    && git clone https://github.com/pgpointcloud/pointcloud \
    && cd pointcloud \
    && ./autogen.sh \
    && ./configure --with-lazperf=/usr/local --with-pgconfig=/usr/lib/postgresql/${POSTGRES_VERSION}/bin/pg_config CFLAGS="-Wall -Werror -O2 -g" \
    && make \
    && make install \
    && apt-get purge -y --auto-remove \
        git \
        ca-certificates \
        build-essential \
        autoconf \
        automake \
        cmake \
        zlib1g-dev \
        postgresql-server-dev-all \
        libxml2-dev

# Somehow plr has to be installed after Pointcloud otherwise it gets uninstalled
RUN apt-get update \
    && apt-get -y --no-install-recommends install \
        postgresql-${POSTGRES_VERSION}-plr \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get update && apt-get autoremove -y

EXPOSE 5433

ADD pointcloud/initdb-pgpointcloud.sh /docker-entrypoint-initdb.d/01_pgpointcloud.sh

initdb-pgpointcloud.sh

#!/bin/sh

##### Script based on initialization script from postgis-docker #####

set -e

# Perform all actions as $POSTGRES_USER
export PGUSER="$POSTGRES_USER"


# shellcheck disable=SC2002
"${psql[@]}" --dbname="$POSTGRES_DB" -f "/scripts/01_extensions.sql"
"${psql[@]}" --dbname="$POSTGRES_DB" -f "/scripts/02_globals.sql"
#"${psql[@]}" --dbname="$POSTGRES_DB" -f "/scripts/03_custom_functions.sql"
"${psql[@]}" --dbname="$POSTGRES_DB" -f "/scripts/04_mis_db_dev_schema.sql"

01_extensions.sql :

CREATE EXTENSION IF NOT EXISTS plpgsql CASCADE;
CREATE EXTENSION IF NOT EXISTS ogr_fdw CASCADE;
CREATE EXTENSION IF NOT EXISTS plr CASCADE;
CREATE EXTENSION IF NOT EXISTS tablefunc CASCADE;
CREATE EXTENSION IF NOT EXISTS postgis CASCADE;
CREATE EXTENSION IF NOT EXISTS postgis_raster CASCADE;
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal CASCADE;
-- CREATE EXTENSION IF NOT EXISTS postgis_topology CASCADE;
-- CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder CASCADE;
CREATE EXTENSION IF NOT EXISTS address_standardizer CASCADE;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch CASCADE;
CREATE EXTENSION IF NOT EXISTS pgrouting CASCADE;

Answer 1

一般来说，在您的应用程序中使用pgRouting函数并不是一个好主意，因为它是从_开始的。这些函数应该在内部使用，但是它们没有文档化，并且可能会在以后的版本中更改而无需通知。