Searchpath with Postgres does not work with Spring/DBCP datasource

Question

Postgres extensions are installed in public schema. Set the search path for the app-specific schema on the DBCP datasource the following way:

  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" primary="true">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://myhost:myport/${db.gos_app.database}?searchpath=mySchema,public;?ApplicationName=${app.name}"/>
    <property name="connectionProperties" value="currentSchema=mySchema;"/>
    <property name="username" value="user"/>
    <property name="password" value="pw"/>
    <property name="defaultAutoCommit" value="false"/>
    <property name="maxActive" value="6" />
</bean>

But somehow I cannot use extensions installed in this public schema without qualifying them like "public.hstore".

Strinder · Accepted Answer · 2016-04-04 10:27:43Z

2

Found the solution - JDBC driver does not know the searchpath property in the URL. But this has been nowhere reported :-( Instead currentSchema is given to driver which is then mapped to native Postgres searchpath (thus probably overwriting the default one there with public included). Not intuitive!

So the solution looks like this:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" primary="true">
<property name="driverClassName" value="org.postgresql.Driver"/>
<property name="url" value="jdbc:postgresql://myhost:myport/${db.gos_app.database}?ApplicationName=${app.name}"/>
<property name="connectionProperties" value="currentSchema=mySchema,public;"/>
<property name="username" value="user"/>
<property name="password" value="pw"/>
<property name="defaultAutoCommit" value="false"/>
<property name="maxActive" value="6" />

answered Apr 4, 2016 at 10:27

Strinder

2,3302 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Why don't you just change the search path for the database user? alter user set search_path = ...?

Collectives™ on Stack Overflow

Searchpath with Postgres does not work with Spring/DBCP datasource

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related