4

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.

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
spoonboy
  • 2,264
  • 4
  • 29
  • 52
  • 1
    You do `SHOW search_path;` to figure what the current search path is. Can you try to do `SET search_path TO 'app1,public'` followed by `SHOW search_path;` to see if it worked? – redneb Sep 13 '16 at 00:14
  • When I connect without currentSchema parameter in the URL, the SHOW search_path returns "app1, public". Then, I disconnect, add currentSchema=app1, and connect again. Now SHOW search_path returns "app1". – spoonboy Sep 13 '16 at 00:37

2 Answers2

11

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
  • 2
    Thanks this works. However, I had to remove quotes (otherwise it doesn't work).So, the URL should be like this: jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public. May be you can correct your answer as it took me a few minutes before I figure out the quotes issue. – spoonboy Sep 13 '16 at 00:49
0

Using python/psycopg2/SQLAlchemy, I had to use

postgresql://localhost:5432/tst?options=-c%20search_path=app1,public
Holger
  • 1