or with get-connection

Can you clarify what you mean by "session variables"? Isn't a "session" just the lifetime of the Connection?

in Postgres these would be like what you get with SET foo=...

Have you tried just call execute! with that statement on the Connection?

of course I can execute such a statement at the start of a connection separately, but I would like to avoid the extra round-trip if possible

since this would happen with every single connection in the design I'm considering

It's up to the driver whether it makes one or multiple round-trips for multiple statements (`;`-separated) in a single execute() call I think so I don't know that you can guarantee avoiding a round-trip for setting a variable?

I think you can set it via the URL, but I'm not sure how to do that when passing a source with :dbname etc https://stackoverflow.com/questions/59060477/can-i-set-session-variables-for-postgres-from-the-jdbc-connection

Just pass it as an extra key in the db-spec map.

Anything in the db-spec hash map that isn't a core attribute to create the basic JDBC URL is added as part of the "query string".

interesting, I'll try that. If that doesn't work out, I had considered jamming the SET statements at the beginning of my normal SQL statement, but when I do that with jdbc/execute! I always get [#:next.jdbc{:update-count 0}] as a return value, even for SELECTs

This part of the docs talks about extra keys in the db-spec https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.847/doc/all-the-options (although it talks about being passed via Properties in the JDBC get connection call so it may not be obvious that they end up in the JDBC URL).

ah, I need :multi-rs

Re: "jamming ... statements at the beginning" -- not all drivers support multiple statements in a single execute() call. Some accept them if you provide some per-connection setting.

Re :multi-rs -- see the various caveats in the docs about that.

(databases are all such "special" individuals, unfortunately)

luckily I only care about Postgres

for :multi-rs, I don't see any caveats in the docs for execute!. Am I looking in the wrong place?

looks like I need to pass :options "-c rls.user=foo" in the dbspec to get the equivalent of SET rls.user='foo' in Postgres, for future reference

Interesting. I'm kind of shocked that works with a space in it 🙂 Re: :multi-rs there are some DB-specific caveats on the Tips & Tricks page but not for PostgreSQL it seems.

thanks for all the help. I really appreciate how responsive you are!

I try to respond quickly if I'm actually around (California business hours mostly) 🙂