We are using pgPool in our Java project to do load balancing in our Postgres database.
pgPool sends read only queries to slave servers and write queries to the master. That is ok.
But there are very specific cases in our application when we need the query to be executed in master server only.
pgPool provides the following mechanism:
/*NO LOAD BALANCE*/ SELECT * FROM user;
The query above will always be executed in the master server.
I searched a lot but while it is possible to set a comment in a manual query (there is a setComment() in Query class), I couldn't find a way to do that using a Hibernate repository with queries created from method names.
Example:
public interface UserRepository extends Repository<User, Long> {
List<User> findByEmailAddressAndLastname(String emailAddress, String lastname);
}
How to put the comment in this query?
Well, this not answers the original question, but I've solved the problem using a different approach.
I've found the solution by reading this section of pgPool documentation: http://www.pgpool.net/docs/latest/pgpool-en.html#condition_for_load_balance
So, annotating a Java method or class with @Transactional achieves the desired result of routing the queries to master server.
When using this annotation, pgPool will route all queries made after a WRITE query (including) to the Postgres master server.
If you don't have a WRITE query, you can achieve the result by annotating with @Transactional(isolation=Isolation.SERIALIZABLE) - if using Spring. But be aware that this isolation level is the strictest available.