Count records based on a criteria for each day in postgres

Suppose we have a table in the database, where the last login times of the users are stored, and we have some data in it, like the following table:

userid	last_login
651651	2013-01-05 21:39:45
894156	2013-01-04 11:31:00
132546	2013-01-04 15:01:49
654713	2013-01-04 01:05:16
654231	2013-01-02 16:40:57
687413	2012-12-31 23:40:57

We would like to know on each day, how many users logged in last, so wee need an SQL to list all days and select the count of users last logged in on that day.

The first task is to find an SQL with which consecutive days can be listed.
The query we are looking for is the following:

select i::date from 
 generate_series('2012-12-30', '2013-01-05', '1 day'::interval) i;

This produces the output:

day
2012-12-30
2012-12-31
2013-01-01
2013-01-02
2013-01-03
2013-01-04
2013-01-05

Great, now we need a criteria for the last_login for each day. The previous query generates timestamps with 00:00:00.000, so we need an interval with the current day as the beginning, and the next day as the end of the interval we are looking for.
Withe the help of the query

select (i::date + '1 day'::interval) from 
 generate_series('2012-12-30', '2013-01-05', '1 day'::interval) i;

we will be able to accomplish this task. Its output is quite similar with the previous one, but in this case the result set is shifted into the future with one day (and the precision changed, but this is not important for our point of view, because we could achieved the same precision with our first query selecting i:timestamp instead of i:date).

day
2012-12-31 00:00:00.000
2013-01-01 00:00:00.000
2013-01-02 00:00:00.000
2013-01-03 00:00:00.000
2013-01-04 00:00:00.000
2013-01-05 00:00:00.000
2013-01-06 00:00:00.000

Superb, we now only need to combine the queries to build our desired selection:

select to_char(i::date, 'YYYY.MM.DD') as last_login_day, 
count(u.userid) as users
from generate_series('2012-12-30', '2013-01-05', '1 day'::interval) i,
 users u
where (i::date + '1 day'::interval) > u.last_login 
 and i::date < u.last_login 
group by last_login_day order by last_login_day:

The result shows the correct values, but unfortunately, the days which none of the users chose to be their last login date are not show up in the result set:

last_login_day	users
2012-12-31	1
2013-01-02	1
2013-01-04	3
2013-01-05	1

This is actually not a big problem, and can be solved by using a union. We create an other selection for displaying all days with 0 user counts, and take the union of the two results by grouping by the days and summing up the users (of course the additional order by is needed as well):

select tmp.last_login_day, sum(tmp.users) from (
 (
 select to_char(i::date, 'YYYY.MM.DD') as last_login_day, 
   count(u.userid) as users
 from generate_series('2012-12-30', '2013-01-05', '1 day'::interval)
   i, users u
 where (i::date + '1 day'::interval) > u.last_login 
   and i::date < u.last_login
 group by last_login_day order by last_login_day
 ) union (
 select to_char(i::date, 'YYYY.MM.DD') as last_login_day,
   0 as users
 from generate_series('2012-12-30', '2013-01-05', '1 day'::interval) i
 )
) as tmp group by 1 order by 1

The results are what we wanted to achieve in the first place:

last_login_day	users
2012-12-30	0
2012-12-31	1
2013-01-01	0
2013-01-02	1
2013-01-03	0
2013-01-04	3
2013-01-05	1

Postgres random in subquery

There was a use-case in a project, where huge number of test data was required, but it had to meet a constraint for one property. The database schema looked like the following:

item:

item_name	price	color	store
Item 1	32	red	null
Item 2	3	blue	null
Item 3	7	yellow	null

store:

store_id	name	coordinate
1	Store 1	12°34′56″N 12°34′56″E
2	Store 2	12°34′56″N 12°34′56″W
3	Store 3	12°34′56″S 12°34′56″E
4	Store 4	12°34′56″S 12°34′56″W

The requirement was that the tables need to be populated with approximately 1 million items, and these items should be randomly associated with the stores. The stores were previously defined, and the store table has already been populated with the data. The items were partially defined, only the store association was null.

The database was Postgres 8.3.

The first SQL attempt was the following:

update item set store = (select store_id from store order by random() limit 1) where item.store is null;

The result was:

item_name	price	color	store
Item 1	32	red	2
Item 2	3	blue	2
Item 3	7	yellow	2

All items received the same store id. It seemed as if the evaluation of the random selection only happened once, and was cached for every record. This can happen because the inner selection does not have any dependencies, so it can be treated as a completely separate query.

The solution is that it has to depend on the outer query, even if the dependency is quite silly from the developer point of view:

update item t1 set t1.store  = (select t2.store_id from store where t1=t1 order by random() limit 1) item.store is null;

The dependency here is the where t1=t1 part. Using this query, the result was exactly what we wanted to achieve:

item_name	price	color	store
Item 1	32	red	2
Item 2	3	blue	4
Item 3	7	yellow	1

Some random JPA pitfalls

There are some custom errors that are usually made by even people who are not quite novice in building JPA 2.0 queries.

One of the most common mistake is the use of isNull() and isNotNull() functions.
The other day I spent a considerable amount of time for debugging a code that contained the following:

...
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Foo> criteriaQuery = cb.createQuery(Foo.class);
Root<Foo> root = criteriaQuery.from(Foo.class);
Predicate predicate = cb.conjunction();
predicate=cb.and(predicate, cb.isNull(root.get(Foo_.amount)));
...
cb.where(predicate);
...

In this case, the generated SQL did not include the part "AND foo.amount = null". This is because this expression should be used in other cases, such as a CASE expression, and preferably contains constants.

The correct form of the predicate is this:

predicate=cb.and(predicate, root.get(Foo_.amount).isNull());

The resulting SQL now contains the foo.amount=null part, so the query finally works. The hard part is it is not so trivial (at least for me) from looking at just the source code, but if the debug is available, and the generated SQL can be tracked, then this is not a big deal to point out the mistake.

This applies to isNotNull() and in(Collection<e>) functions as well. 

Another common pitfall is to try to make two different queries with the same predicate, in a way that one creates a predicate for a query, executes the query, and tries to do another select with the previously created predicate instance. A typical use-case is a count and a select query, where the same filters are applied.

Unfortunately this cannot be achieved for predicates that have different root instances.

The third is not quite a mistake, but can easily be one. This is listed here basically for prevention.

What is the difference in the following queries (which can go wrong):

TypedQuery<Long> randomSelectForSingleResult = em.createQuery(cq);

int result = randomSelectForSingleResult.getSingleResult();

TypedQuery<Long> randomSelectForSingleResult = em.createQuery(criteriaQuery);

List<Long> resultList = randomSelectForSingleResult.getResultList();

int result = 0;

if(resultList.size() != 0)

    result = resultList.get(0);

The problem is that if the first type of selection does not contain EXACTLY one element, then it throws an exception. Although this may not happen if the selection is a count, otherwise it may occur, so if there is the tiniest, slightest chance that the query returns more or less than one results, the second attempt should be used.

received message with Unknown Transaction ID -1: ignoring message

Recently I ran into an interesting problem. In a pretty complex architecture (several Glassfish servers, multiple Liferay nodes) which ran without a problem for over a year, we had to implement a minor improvement. It was a pretty standard JMS message to be sent from one Glassfish instance to an other. We had several JMS messages cruising in the system, so this seemed a quite straightforward task.

Some key words about the setup: Spring JTA transaction management (spring framework ver. 3.0), Glassfish (3.0.1)  built in JMS implementation, Liferay 6.0.6. The database had read-committed isolation, and  used XA transaction, the JMS supported local transactions.

Previously we did not encounter any problem regarding the transactions and the JMS message send, as long as the service implementation was annotated with @org.springframework.transaction.annotation.Transactional(readonly = false)

The required change was that a file should be uploaded to the Lifery Document Library at the end of the method, the a JMS message to be sent to broadcast the event. After implementing the method, the JMS failed with the following exception:

java.lang.RuntimeException: com.sun.messaging.jms.JMSException: [SEND_REPLY(9)] [C4036]: A broker error occurred. :[500] transaction failed: Unexpected Broker Exception: [received message with Unknown Transaction ID -1: ignoring message] user=admin, broker=localhost:2076(50283)

It was clear after a while, if the Liferay file upload is not in the function, everything works as expected. The transaction was active for the method, the stack trace was clear about it. It was no use to debug the Liferay part, because if we want to upgrade to a newer version, it might need to be patched again for our code to work.

So it seemed as if the Liferay terminates (commits) the transaction during file upload. So we need to exit the current transaction while leaving it open, then return to it after the file upload completed, and continue as previously. To do that, we had to start a new thread to do the dirty work. The java language had the built-in solution:

ExecutorService executorService = Executors.newSingleThreadExecutor();
Future future = executorService.submit(new Callable() {
  @Override
  public Object call() throws Exception {
    try {
      // do file upload
      DLFileEntryLocalServiceUtil.
        addFileEntry(userId, groupId, folderId, finalFilename,
        finalFilename, description, (String) null, null, 
        finalBytes, serviceContext);
    } catch (final SystemException e) {
      return e;
    } catch (final PortalException e) {
      return e;
    }
      return Boolean.TRUE;
    }
});

Object object = future.get();
if (object instanceof Boolean) {
  if (((Boolean) object).booleanValue()) {
    // everything OK
  } else {
    // something went wrong
  }
} else if (object instanceof Exception) {
  // something went wrong
}

This implementation certainly has its drawbacks: extra care should be taken if the transaction fails after the file upload happened to rollback it. In our case, this was not an issue: it was OK if the resulting file was a result of a transaction that rolled back afterwards.