postgresqlsupabase

Supabase Postgis not working. Topology not in search_path | Change supabase search_path


I initially ran a command which gave me the error error: type "geometry" does not exist'

After some investigation, I found out that there isn't access to the postgis extension. I proceeded to run SELECT postgis_version(); in the supabase SQL editor, and got a no function found error.

I ran the following

SET search_path TO public, extensions, topology;
SELECT postgis_version();

And got the postgis version as response. I ran SHOW search_path; and indeed, topology was missing.

I ran ALTER DATABASE my_accurate_database_name SET search_path TO "/$user",extensions,topology, public;

This didn't change my response from SHOW search_path;

My question then, is how do I add topology to my search path with supabase?

Other things I've tried:

  1. Removing the extension and reinstalling it
  2. Manually adding the extension to one of the schemes included in the existing search path, only to have the 'must in inside topology' rule enforced

Solution

  • ALTER DATABASE..SET doesn't affect ongoing sessions - they need to reconnect to use the new default.

    Other levels can also override that default setting, e.g. you can alter system..set and that loses to alter database..set, which loses to alter role..set, and session-level set overrides that, which can be further changed by a set local in a transaction. Only the last two can affect the setting in a live session, and only from the inside.

    Quoting 19.1.3. Parameter Interaction via SQL:

    Values set with ALTER DATABASE and ALTER ROLE are applied only when starting a fresh database session. They override values obtained from the configuration files or server command line, and constitute defaults for the rest of the session.

    There is no way to change them from outside the session, other than pg_terminate_backend() to kick them and force to reconnect, applying newly set defaults for their role, the database or whole cluster they connect to.
    demo at db<>fiddle

    create schema s1; create table s1.t1(id int);
    create schema s2; create table s2.t2(id int);
    
    show search_path;
    
    search_path
    public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis
    alter system set search_path=s1;
    show search_path;--command above didn't even change it for the current session
    
    search_path
    public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis
    alter database postgres set search_path=s1;
    show search_path;--still, didn't affect this or any other ongoing session
    
    search_path
    public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis
    alter role current_user set search_path=s1;
    show search_path;--still no change
    
    search_path
    public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis
    set search_path=s1;
    show search_path;
    
    search_path
    s1
    set local search_path=s2;
    show search_path;--only lasts until a `commit`|`rollback`|`prepare transaction`
    
    search_path
    s2

    In autocommit mode the last example may seem like it doesn't work at all because there will be an immediate, implicit commit right after set local, which sets it back to what it was session-wide.