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

Question

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

gavenkoa · Accepted Answer · 2022-03-09 12:28:21Z

4

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;

edited Mar 9, 2022 at 12:28

answered Jan 6, 2020 at 12:01

gavenkoa

49.7k28 gold badges274 silver badges340 bronze badges

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

Comments

Shay Rojansky · Accepted Answer · 2018-12-21 15:14:42Z

3

You're probably looking for the PostgreSQL search_path variable, which controls which schemas are checked when trying to resolve database object names. The path accepts several schema names, which are checked in order. So you can use the following

SET search_path=our_schema,public;

This will make PostgreSQL look for your tables (and functions!) first in our_schema, and then in public. Your JDBC driver may or may not support multiple schemas in its current_schema parameter.

Another option is to install the PostGIS extension (which provides the make_point() function) in the our_schema schema:

CREATE EXTENSION postgis SCHEMA our_schema;

This way you only have to have one schema in your search path.

answered Dec 21, 2018 at 15:14

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Comments

Vladimir Gurevich · Accepted Answer · 2018-12-21 21:27:41Z

2

if you use hibernate.default_schema, then for native queries, you need to provide the {h-schema} placeholder, something like that

SELECT ST_MAKEPOINT(cast(longitude as float), cast(latitude as float)) FROM {h-schema}OUR_TABLE;

answered Dec 21, 2018 at 21:27

Vladimir Gurevich

1711 silver badge4 bronze badges

1 Comment

gavenkoa Over a year ago

vladmihalcea.com/…

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related