CODEWITHSUNDEEP
hibernatepostgresqlplayframeworkjpa-2.0
aguyngueran
aguyngueran

Reputation: 1321

JPA Hibernate PostgreSQL current_date doesn't work

I have a PostgreSQL 9.3.6 database with the following query defined:

CREATE OR REPLACE VIEW calls_today AS
SELECT
    cc.*
FROM
    call_config cc
WHERE
    cc.created_at >= current_date;

The view is used on the web portal developed with Play Framework + Hibernate via JPA 2. Everything seems to work fine except the date related queries.

If the server is restarted today, the view looks like working but only today. Tomorrow, I will see on the web page all the calls from the last two days. The next day, another one more, so three days and so on. If I issue the query on the psql client, the results are fine, just the current day.

I must be missing something, it doesn't look like the query results were cached (as each day the result grows) but rather current_date was fixed at the day of the server restart. Like some kind of prepared statement, I really don't know.

The data is pulled from the database with the following JPA 2 API:

private static<T extends IOutgoingCallConfig> Result getCalls(Class<T> entityClass) {
Check.Argument.isNotNull(entityClass, "entityClass");

List<CallItem> calls = new ArrayList<>();

EntityManager em = JPA.em();

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<T> cq = cb.createQuery(entityClass);
Root<T> rootEntry = cq.from(entityClass);
CriteriaQuery<T> all = cq.select(rootEntry);
TypedQuery<T> allQuery = em.createQuery(all);

for(T entity: allQuery.getResultList()) {
   calls.add(new CallItem(entity));
}

    return jsonSuccess(calls);
}

I have tested via psql that the following prepared query:

prepare mystmt as select current_time;

doesn't suffer this problem. Each execution reveals the updated current server time:

execute mystmt;

Having a simple view like below:

CREATE OR REPLACE VIEW my_current_time as select current_time;

causes the same problems. One the query has been run, the return value is always the same :(

It might be related to the definition of the current_time and current_date. From PostgreSQL 9.4 documentation:

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.

Upvotes: 1

Views: 1029

Answers (1)

aguyngueran
aguyngueran

Reputation: 1321

It turned out to be transaction scope problem. My controller is annotated with:

@Transactional(readOnly=true)

as it only reads the data. For some reason, the transaction scope spans all future requests :( I'm a little bit surprised here. I would expect either having a transaction for each request or not having at all (auto commit transaction).

I have changed

current_date

with

date_trunc('day', clock_timestamp())

and now it runs fine even via Hibernate.

I guess I must read more about Hibernate and JPA 2 or I could run into similar problems if I don't understand the transaction scope clearly.

Upvotes: 1

Related Questions