Relation does not exist When using Hibernate EntityManager.createNativeQuery() and PgBouncer

Question

I'm quite new to Postgres and PgBouncer as well, not a complete newbie, but still lacking experience.

I'm using Postgres + PgBouncer, spring and hibernate as tech stack.

We have multiple microservices working under different schemas, sometimes even shared ones. All these microservices are connecting to db via PgBouncer.

Sometimes I'm receiving the following exception when running queries:

"Caused by: org.postgresql.util.PSQLException: ERROR: relation "XXX" does not exist"

For queries defined in Repositories it's quite easy to fix it by applying {h-schema} before table names like "SELECT * FROM {h-schema}XXX"

but I also have ~100 queries stored in db which I create using Query query = EntityManager.createNativeQuery(..); return query.getResultList(); and I would like to avoid updating the statements of these queries.

Queries are readonly but they are supposed to be invoked sometimes sequentially in a single transaction (isolation level = Repeatable Read), so it's wrapped in spring @Transactional service.

From what I understand PgBouncer sometimes returns connections from pool which are pointing to wrong schema / search_path. How can I enforce my queries to use the correct schema without modifying them?

Shall it be a simple append:

entityManager.createNativeQuery("set search_path to 'schema';" + queryStr, Foo.class)

Since @Transactional implies every query is run in the same connection (obtained once from PgBouncer) it will guarantee Repeatable Read, am I correct?

I have tried multiple things but since the error is not easily reproducible, I have doubts and want to learn the correct way.

It should be sufficient to set the search path as the first action in the transaction. There's no need to set it before each individual query. — JohnH
– JohnH, Commented Mar 1 at 18:17
@JohnH thanks for a suggestion, seems like it will work for my usecase (transactional mode for PgBouncer). — Mikhail Shulga
– Mikhail Shulga, Commented Mar 3 at 17:59

Mikhail Shulga · Accepted Answer · 2025-03-03 18:01:54Z

0

As @JohnH suggested, seems like having SET SEARCH_PATH TO XXX at the beginning of each transaction is enough for PgBouncer operating in transactional mode.

answered Mar 3 at 18:01

Mikhail Shulga

236 bronze badges

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

1 Comment

Luca Basso Ricci Mar 5 at 8:38

Please accept your own answer if it solves the problem

Collectives™ on Stack Overflow

Relation does not exist When using Hibernate EntityManager.createNativeQuery() and PgBouncer

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