"Undefined function" error when SEARCH_PATH is different than the schema PostGIS is installed in

[dece]

Hi,

If your PostgreSQL role has a different SEARCH_PATH than public or the one PostGIS has been installed in with the CREATE EXTENSION postgis SCHEMA xxx, retrieving entities from database will fail with the following error:

SQLSTATE[42883]: Undefined function: 7 ERROR:  function st_asewkt(public.geometry) does not exist
LINE 2: ...o.src AS src, o.cd_nom AS cd_nom, o.date AS date, ST_AsEWKT(...
                                                             ^
Hint: No function matches the given name and argument types.
You might need to add explicit type casts`

Altering the role to use public as search path makes the problem disappear. The problem can also be fixed by specifying the function schema directly in PostGISType::convertToPHPValueSQL.

-       return sprintf('ST_AsEWKT(%s)', $sqlExpr);
+       return sprintf('public.ST_AsEWKT(%s)', $sqlExpr);

I am not sure what the fix should be because I am not sure what are the guarantees that I left behind when I altered the search path for this role, but being able to specify the schema where PostGIS has been installed might be nice?

[maxhelias]

Hi!

Several possibilities:

1. Configure the search_path at the Doctrine connection level

# config/packages/doctrine.yaml
doctrine:
    dbal:
        options:
            # PostgreSQL-specific options
            1002: "SET search_path TO public,extensions,postgis"  # PDO::PGSQL_ATTR_INIT_COMMAND

2. If it work, we can consider to add it in the driver with something like that :

public function connect(array $params): DBAL\Driver\Connection
{
    $connection = $this->decorated->connect($params);
    if (null !== $this->schemas) {
        $connection->exec("SET search_path TO ".$this->schemas);
    }
    return $connection;
}

3. Or more hybride, add a PostGISConfiguration class

class PostGISConfiguration
{
    private array $searchPath = ['public'];
    private bool $autoSetSearchPath = true;
    private ?string $postgisSchema = null;
    
    public function setSearchPath(array $schemas): self
    {
        $this->searchPath = $schemas;
        return $this;
    }
    
    public function setPostGISSchema(?string $schema): self
    {
        $this->postgisSchema = $schema;
        return $this;
    }
    
    public function shouldAutoSetSearchPath(): bool
    {
        return $this->autoSetSearchPath;
    }
    
    public function detectPostGISSchema(Connection $connection): ?string
    {
        if ($this->postgisSchema !== null) {
            return $this->postgisSchema;
        }
        
        $schema = $connection->fetchOne(
            "SELECT n.nspname 
             FROM pg_proc p 
             JOIN pg_namespace n ON p.pronamespace = n.oid 
             WHERE p.proname = 'postgis_version' 
             LIMIT 1"
        );
        
        $this->postgisSchema = $schema ?: 'public';
        return $this->postgisSchema;
    }
}

And in the connect driver :

public function connect(array $params): DBAL\Driver\Connection
{
    $connection = $this->decorated->connect($params);
    if ($this->config->shouldAutoSetSearchPath()) {
        $postgisSchema = $this->config->detectPostGISSchema($connection);

        $searchPath = array_unique([
            ...$this->config->getSearchPath(),
            $postgisSchema
        ]);
                    
        $connection->exec(
            'SET search_path TO ' . implode(',', $searchPath)
        );
    }
    return $connection;
}

[dece]

Hi Maxime and thank you for the input. Unfortunately the project this feature was needed on has been completely reworked since to use another stack so I cannot quickly replicate the initial context. I'm going to keep this open if you don't mind so that if time allows it I'll work on it, but if you don't get news before the end of the year maybe consider closing it.