document session variable usage for pgjdbc

[dikshant]

Dikshant Adhikari (dikshant) commented:

pgjdc supports specifying session variables through options param see:
https://jdbc.postgresql.org/documentation/use/

Docs:
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

For example you can do

postgresql://user@localhost:5433/mydb?options=-c%20unbounded_parallel_scans%3Don

Relevant Slack discussion: https://cockroachlabs.slack.com/archives/CHVV403F0/p1691598835018619

Might consider adding an example here as well.

https://www.cockroachlabs.com/docs/v23.1/build-a-java-app-with-cockroachdb

Jira Issue: DOC-8539

[fabiog1901]

I suggest we add the Java syntax, too, as in

ds.addDataSourceProperty("options", "-c unbounded_parallel_scans=true -c statement_timeout=30");

[bdarnell]

The URL form of this is supported by many (not quite all) drivers and is documented at https://www.cockroachlabs.com/docs/stable/connection-parameters#supported-options-parameters

[exalate-issue-sync]

Ian Evans (ianjevans) commented:
Fabio Ghirardello I don’t think {{addDataSourceProperty}} is correct. Maybe a Hikari method? In PostgreSQL JDBC it’s:

{code:java}Properties props = new Properties();
props.setProperty("options", "-c {session variable name}={session variable value}");
Connection conn = DriverManager.getConnection(url, props);{code}