OHDSI Home | Forums | Wiki | Github

Specify @Transactional on JPA repository for operations involving @Lob fields

Developers
#1

@alfranke,
I’m testing the latest build of WebAPI and Circe on postgres. The error we’re receiving in the logs is this:

SQL Error: 0, SQLState: 25P01
2015-03-03 22:42:22.149 ERROR http-nio-8080-exec-453 org.hibernate.engine.jdbc.spi.SqlExceptionHelper -  - Large Objects may not be used in auto-commit mode.

I’m getting this when we read a definition going to the path /cohortdefinition/{id}

This is the code that i’m using to load the record:

CohortDefinition d = this.cohortDefinitionRepository.findById(id);
return cohortDefinitionToDTO(d);

This is using a Spring-Data constructed repository interface such that it parses the function name to produce the query. I did this (as opposed to findOne) so that I could specify the entity graph to fetch the object:

  @EntityGraph(value = "CohortDefinition.withDetail", type = EntityGraph.EntityGraphType.LOAD)
  CohortDefinition findById(Integer id);

Do you know if i can specify @Transactional on this method defined in the interface? I’m wondering if I need to just build a data access object by hand instead of leveraging extending CRUDRepository? Have you any experience with this behavior?

-Chris

#2

Is the full log in Jenkins? I have not heard of using @Transactional for finders. With that said, I have not needed to use EntityGraph before. I’ll try and take a look at the code this morning.

#3

I agree, it sucks. This is what I’ve found about it in SO:

A large object can be stored in several records, that’s why you have to use a transaction. All records are correct or nothing at all.

http://www.postgresql.org/docs/8.4/interactive/largeobjects.html

I believe this is due to @Lob annotation that is required for CLOB on oracle. I do not need @lob for Text fields on Postgres, but I don’t think I can have dialect specific annotations.

-Chris

#4

Can you confirm that you are talking about using @Transactional for a finder vs saving (insert/update). @Transactional (or TransactionTemplate) should be used for inserts/updates. Just want to make sure we are still talking about your findById usage in GET /cohortdefinition/{id}

#5

Reviewing some of my code for another project, where we use Postgres, I found I added the following to my entity. Give that a shot.

     @Column(nullable = false, length = 2147483647)
     @Lob
     @Type(type="org.hibernate.type.StringClobType")
     private String results;
#6

The current master (that works with postgres and mssql) has this as the annotation (very similar to yours):

  @Lob
  @Type(type = "org.hibernate.type.TextType")  
  private String expression;

I think that this would mean that we don’t need to wrap anything in transactions anymore because the data is serialized right into the row. What I mean by this is before I had the @Type annotation, the value in the expression would be an ‘int’ which was some kind of reference to a LOB storage in postgres that you would have to jump through some complicated hoops (that the jdbc driver hides from you) to get to the actual content. With the @Type annotation, you can see the string content right in the select * from cohort_definition_details query.

I do not know if this works with CLOB data type on oracle, tho, so if someone could confirm a save/load using the WebAPI who has oracle set up, that’d be helpful.

-Chris

#7

Yes, confirmed: the jdbc JPA provider throws an exception ‘Large Objects may not be used in auto-commit mode.’ even on queries because under the covers when using the Postgres Large Object capabilities (indicated by the @Lob annotation), it will throw this exception if it is not in a transaction. Maybe this is why in the Hibernate transactional annotation you can specify the transaction as ‘read only’?. In any case the current master branch has the annotations that work on postgres. Just need to know if they also work on Oracle.

#8

I removed the @Transactional annotation from the repository and confirmed that it works for Oracle. I would vote for testing without @Transactional because I just think that looks weird… My two cents…

#9

I will try it without @transactional but with the @Type annotation to see if it is still required.

#10

Ok, it continues to work on Postgres. I pushed to master removing the @Transactional attribute.

t