Search code examples
postgresqlhibernatespring-bootpostgis

Default schema for native SQL queries (spring-boot + hibernate + postgresql + postgis)

I am introducing spring to the existing application (hibernate has already been there) and encountered a problem with native SQL queries.

A sample query:

SELECT ST_MAKEPOINT(cast(longitude as float), cast(latitude as float)) FROM 
OUR_TABLE;

OUR_TABLE is in OUR_SCHEMA.

When we connect to the db to OUR_SCHEMA:

spring.datasource.url: jdbc:postgresql://host:port/db_name?currentSchema=OUR_SCHEMA

the query fails because function ST_MAKEPOINT is not found - the function is located in schema: PUBLIC.

When we connect to the db without specifying the schema, ST_MAKEPOINT is found and runs correctly, though schema name needs to be added to the table name in the query.

As we are talking about thousands of such queries and all the tables are located in OUR_SCHEMA, is there a chance to anyhow specify the default schema, so still functions from PUBLIC schema were visible?

So far, I have tried the following springboot properties - with no success: 

spring.jpa.properties.hibernate.default_schema: OUR_SCHEMA
spring.datasource.tomcat.initSQL: ALTER SESSION SET CURRENT_SCHEMA=OUR_SCHEMA
spring.datasource.initSQL: ALTER SESSION SET CURRENT_SCHEMA=OUR_SCHEMA

Also, it worked before switching to springboot config - specifying hibernate.default-schema = OUR_SCHEMA in persistence.xml was enough.

Stack:

spring-boot: 2.0.6

hibernate: 5.3.1.Final

postgresql: 42.2.5

postgis: 2.2.1

Solution

  • JDBC param currentSchema explicitly allows specifying several schemas separating them by commas:

    jdbc:postgresql://postgres-cert:5432/db?currentSchema=my,public&connectTimeout=4&ApplicationName=my-app

    From https://jdbc.postgresql.org/documentation/head/connect.html

    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.

    Note you probably need Postgres 9.6 or better for currentSchema support.

    PS Probably better solution is to set search_path per user:

    ALTER USER myuser SET search_path TO mydb,pg_catalog;