Fork me on GitHub
#sql
<
2021-09-21
>
naxels17:09:25

Hi everyone, does someone know how to change the PostgreSQL/Redshift schema using next.jdbc? Within my company I cannot use the default public schema and need to use something else, however can’t find how to set it as a config, or execute SET search_path = "other_schema" (which would then be used in all future queries) thank you!

dharrigan17:09:16

Would it not be possible to alter the user so that the search path is already set, i.e., alter user foo set search_path = 'other_schema, "$user", public';?

dharrigan17:09:20

That's what I would do

naxels18:09:25

unfortunately this is all I got from the IT department, I’m not sure if they would help me by executing that statement

naxels18:09:55

in DataGrip I see the SET search_path statement executed as soon as I start querying the db ha

dharrigan18:09:00

Aren't they there to support you? I mean, if you raise whatever ticket in your company to get them to action something for user on the db, they should honour the request?

naxels18:09:00

it only runs 1ce

dharrigan18:09:03

What version of the postgresql jdbc driver are you using?

naxels18:09:17

latest, 42.2.23

naxels18:09:35

same as for redshift, 2.1.0.1

dharrigan18:09:45

currentSchema = String

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.

naxels18:09:14

that is very interesting!

naxels18:09:23

do you know how I can use this with next.jdbc?

naxels18:09:33

still figuring it all out

naxels18:09:25

i see that next.jdbc translates my Map of :dbtype etc into the jdbc:…… statement

naxels18:09:41

but don’t know how to add these custom parameters without switching to :jdbcUrl ……

naxels18:09:08

I tried adding :currentSchema “schemaname” to the Map, but that didn’t work

naxels18:09:35

as it doesn’t change the jdbc/get-datasource final jdbc:…… statement

naxels18:09:17

yeah, that is what I saw when I was hoping that the key/value would be submitted to the db

lukasz18:09:46

alternatively, you can assemble the URL yourself, using the java.net.URI etc

naxels18:09:52

never mind, it actually worked 🙂

naxels18:09:44

just didn’t show up in the (def ds (jdbc/get-datasource dbmap)) ds

dharrigan18:09:29

I would set it on the jdbc connection string directly

dharrigan18:09:51

Are you using a connection pooler too?

dharrigan18:09:57

I would highly recommend hikaricp

naxels18:09:42

That is what I will be working on next

naxels18:09:00

Am currently dealing with very slow Oracle query results though and don't understand why

naxels18:09:48

Using DataGrip the query is fast, < 1s

naxels19:09:02

But running the same query with next.jdbc is ~ 11s

naxels19:09:29

And Sean helped me check it by using reduce 0 over jdbc/plan, but still ~ 10s

naxels19:09:12

DataGrip also uses the same jdbc 11 21.1.x version that I use with Clojure

naxels19:09:16

This while the exact same query running on Redshift is around ~1s again

naxels19:09:13

It's about 5000+ records and in order to make it work for Clojure, i add a fetch of 100 to reduce the time it takes for output