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:
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
andALTER 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.