search_path doesn't work as expected with currentSchema in URL

Question

My SQL commands have issues finding objects from the public schema (which is in the default DB search_path) when specifying the currentSchema parameter in the DB connection URL.

How could this be fixed?

The long story:

1. I have an application schema app1.
2. The DB has the Postgis extension installed in the public schema (and we want to keep it there).

3. The DB search_path is configured like this:

   ALTER DATABASE tst SET search_path = "$user", public
   

4. When connecting to the DB without specifying current schema in URL, the default schema is public and so it finds all the geo functions and objects. But I have to specify the app1 schema prefix when addressing objects from app1, e.g.:

   select st_asgeojson(geometry,15,4) from app1.shapes limit 5
   

5. This is not convenient. So I added "app1" as a current schema parameter to the connection URL like this:

   jdbc:postgresql://localhost:5432/tst?currentSchema=app1
   

6. Now, when I connect to the DB, I don't have to specify the app1 prefix when addressing objects from app1 schema. However, requests that involve Postgis objects don't work anymore and fail with:

   ERROR: function st_asgeojson(public.geometry, integer) does not exist

My understanding is that it should search for the objects in the search_path and find them in the public schema but it doesn't happen for some reason. I've tried specifying search path on a user level as well but it still didn't work.

Answer 1

The parameter name currentSchema is a bit misleading. It takes the whole search_path, not just the "current schema". The documentation:

• currentSchema = String

Specify the schema to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.

So try:

jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public

Or, if you connect with a particular user, you can set the search_path for the user in the DB (or for this user in this database). Then you don't need anything in the connection string.

Or, if your user name happens to be app1, then the search path setting "$user", public resolves to app1, public automatically, and you don't need to do anything extra.

• How does the search_path influence identifier resolution and the "current schema"