Support search_path JDBC URL parameter

With introduction of user-defined schemas it would be great to have support of search_path in JDBC URL parameters. Currently in v 22.2.2 it is silently ignored; neither it is listed in documentation as supported one.
There are some actions one could take in their apps like spring.datasource.hikari.schema=custom_schema in but they are obviously very specific (“using springboot+hikari” in the example).

Hi! I’m interested to learn about what you are seeing. This works for me when I test with the command line tool. Can you provide steps to reproduce what you saw?

❯ cockroach sql --url 'postgres://root@localhost:26257?sslmode=disable&search_path=public,my_schema'
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
# Server version: CockroachDB CCL v20.2.2 (x86_64-apple-darwin14, built 2020/11/25 14:50:01, go1.13.14) (same version as client)
# Cluster ID: 61537a12-4784-4840-9163-83aae9a1d186
# Enter \? for a brief introduction.
root@localhost:26257/defaultdb> show search_path;
(1 row)

I see “org.postgresql.util.PSQLException: ERROR: relation “person” does not exist” trying to select from person table without any qualifiers.
It is from my custom springboot/jdbc/mybatis app, not from the cockroach command line tool.
So in this (springboot) connection search_path does not work for me:
And exactly the same search_path approach worked for PostgreSQL.

@aehrlich sorry for the delayed reply. Could you try setting the currentSchema option instead of search_path. I think when you connect with PGJDBC, that might be required, as specified here: Connecting to the Database

We also have issue #59404 for supporting the options parameter, so the search_path can be passed through there. But that issue is not resolved yet.

@rafiss Setting currentSchema in the connection URL is an acceptable workaround for my specific case, it works, thank you. It is worth noting that currentSchema can contain a list of schemas, actually.
It is interesting, however, why/how did your example of cockroach sql --url 'postgres://root@localhost:26257?sslmode=disable&search_path=public,my_schema' work then.

Actually, I believe the PGJDBC currentSchema setting also can be a comma-separated list. The docs I linked to say:

Specify the schema (or several schema separated by commas) to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.

The reason my example of using the cockroach sql CLI worked is because PGJDBC has some special logic that only allows certain URL parameters to be used. Here is the PGJDBC code. But using a different client (like the CLI tool) would allow the URL to be passed through to CockroachDB unchanged.