I'm enhancing a Java application to store some BLOB like data.
The RDBMS is a PostgreSQL 8.4.
During the database schema creation for the BLOB things I do this (I've found it on the web):

"CREATE DOMAIN lo AS pg_catalog.oid;

--
-- For backwards compatibility, define a function named lo_oid.
--
-- The other functions that formerly existed are not needed because
-- the implicit casts between a domain and its underlying type handle them.
--
CREATE OR REPLACE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS
'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE;

-- This is used in triggers
CREATE OR REPLACE FUNCTION lo_manage()
RETURNS pg_catalog.trigger
AS '$libdir/lo'
LANGUAGE C;"

After that when I create the actual table:
"CREATE TABLE person (
person_id BIGINT NOT NULL,
...
bcustom1 lo,
bcustom2 lo,
...
) WITHOUT OIDS;"

The important things are the columns which has the type "lo" (large object), which is actually an OID.

On the Java code side I use byte[] field in the entity class, and I mark it with a @Lob annotation for Hibernate (plus I specify @Basic(fetch=FetchType.LAZY):

"@Entity
@Table(name = "person")
@SequenceGenerator(name="person_seq", sequenceName="public.person_seq")
public class Person extends BaseObject implements java.io.Serializable
{
private static final long serialVersionUID = -6061320465621019356L;

private Long personId;
...
@Lob @Basic(fetch=FetchType.LAZY)
private byte[] bcustom1;
@Lob @Basic(fetch=FetchType.LAZY)
private byte[] bcustom2;
...
}

public byte[] getBcustom1() {
return bcustom1;
}

public void setBcustom1(byte[] bcustom1) {
this.bcustom1 = bcustom1;
}

public byte[] getBcustom2() {
return bcustom2;
}

public void setBcustom2(byte[] bcustom2) {
this.bcustom2 = bcustom2;
}

The application is an enterprise level app, it uses Spring and Struts too, I don't know how this can affect my problem.
When I try to save a class, I get an exception.

"public void addPerson(Person person) {
log.debug("Saving person record: " + person);
...
getHibernateTemplate().saveOrUpdate(person);
getHibernateTemplate().flush();
...
log.debug("Finished saving the person.");
}"

"http-localhost%2F127.0.0.1-8080-1 - ERROR - org.openhie.openempi.loader.AbstractFileLoader.loadPersonRemote(63) loader.AbstractFileLoader - Failed while adding person_remote entry to the system. Error: org.springframework.dao.InvalidDataAccessResourceUsageException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
org.springframework.dao.InvalidDataAccessResourceUsageException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:886)
at org.openhie.openempi.dao.hibernate.PersonRemoteDaoHibernate.addPerson(PersonRemoteDaoHibernate.java:48)
at org.openhie.openempi.service.impl.PersonManagerRemoteServiceImpl.savePerson(PersonManagerRemoteServiceImpl.java:136)
at org.openhie.openempi.service.impl.PersonManagerRemoteServiceImpl.addPerson(PersonManagerRemoteServiceImpl.java:56)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy93.addPerson(Unknown Source)
at org.openhie.openempi.loader.SpringPersonLoaderManagerRemote.addPersonRemote(SpringPersonLoaderManagerRemote.java:51)
at org.openhie.openempi.loader.AbstractFileLoader.loadPersonRemote(AbstractFileLoader.java:61)
at org.openhie.openempi.loader.AbstractFileLoader.parseFile(AbstractFileLoader.java:139)
at org.openhie.openempi.loader.FileLoaderManagerRemote.loadFile(FileLoaderManagerRemote.java:55)
at org.openempi.webapp.server.PersonDataServiceImpl.importFileEntry(PersonDataServiceImpl.java:177)
at org.openempi.webapp.server.PersonDataServiceImpl.importUserFile(PersonDataServiceImpl.java:160)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:527)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:166)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.doPost(RemoteServiceServlet.java:86)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.springframework.orm.hibernate3.HibernateTemplate$28.doInHibernate(HibernateTemplate.java:888)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
... 49 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into person_remote (address1, address2, address_type_cd, bcustom1, bcustom10, bcustom11, bcustom12, bcustom13, bcustom14, bcustom15, bcustom16, bcustom17, bcustom18, bcustom19, bcustom2, bcustom20, bcustom3, bcustom4, bcustom5, bcustom6, bcustom7, bcustom8, bcustom9, birth_order, birth_place, city, country, country_code, custom1, custom10, custom11, custom12, custom13, custom14, custom15, custom16, custom17, custom18, custom19, custom2, custom20, custom3, custom4, custom5, custom6, custom7, custom8, custom9, date_changed, date_created, date_of_birth, date_voided, death_ind, death_time, degree, email, ethnic_group_cd, family_name, family_name2, gender_cd, given_name, language_cd, marital_status_code, middle_name, mothers_maiden_name, multiple_birth_ind, name_type_cd, nationality_cd, phone_area_code, phone_country_code, phone_ext, phone_number, postal_code, prefix, race_cd, religion_cd, ssn, state, suffix, changed_by_id, creator_id, voided_by_id, person_id) values (HAUSS RIGE, RD, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, , VALDESE, NULL, NULL, AMJN, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2010-07-01 12:18:21.815000 -0500, 2010-07-01 12:18:21.815000 -0500, NULL, NULL, NULL, NULL, NULL, NULL, NULL, IMOGENE, NULL, NULL, BERRY, NULL, NULL, Z, NULL, NULL, NULL, NULL, Y, F, , 12000000008608, NULL, NULL, NULL, NULL, NULL, NC, NULL, 1, 1, NULL, 150) was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2512)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1310)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:347)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2574)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
... 56 more"

I don't know what is the problem with the actual SQL command, but if I remove the Lob fields from the class everything works. I've learned that PostgreSQL provides two types of BLOB storage. One is bytea column type, and the other one is the OID (lo) type. Since I wan to store binary data, I want to use the OID method, I want to represent the data with a byte array anyway. Should I stream somehow? How does that work with Hibernate? What kind of get/set methods should I write? What is wrong?

Thanks for your help in advance!
Csaba

After lots of reading I have some things to try:
1. @Type(type = "org.hibernate.type.BinaryType") annotation to the byte[] array.
Here's a topic about that: https://forum.hibernate.org/viewtopic.php?f=10&t=997454&p=2414508&hilit=postgresql+LOB#p2414508
But I'm a little suspicious about that, because in theory and according to the documentation if I annotate byte[] with a @Lob, it should be mapped to the OID BLOB type, if someone annotate String or character type, it will be mapped to bytea. (Of course I'm talking only about PostgreSQL)
2. Change byte[] to java.sql.Blob
I would prefer to stay with byte[], but if Blob type will work I'll be still happy to some extent
3. I can't remember right now.

I've also read different things about the lazy initializations. The attributes I want to store in a blob currently will only be 1-2KB in size, but I don't want to go towards the bytea direction, because it adds overhead, extra encoding layer, and speed will be critical. Lazyness is not as important right now yet.

Later I'll have fields which can take up 150KB in size, with this lazyness can be much more important. I might go towards a Lazy connected table with that? How can I access portions of the 150KB field without fetching the whole shebang into memory? What kind of streaming interface will I encounter?

It seems that the solution will be to use java.sql.Blob type instead of byte[].

I managed to solve this issue.
So I use java.sql.Blob data type in my Java entity. I specify @Lob annotation, and also annotations for lazy fetch.
For populating a Blob field I use (the source of the data is byte[])
Blob blob = Hibernate.createBlob(byteArray);
then I can set this blob variable into the entity.

The persisting started to work, but introducing Blob broke my queries:

The essence of the exception:
Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.

log:
16:55:10,845 INFO [STDOUT] Hibernate: select this_.person_id as person1_92_5_, this_.address1 as address2_92_5_, this_.address2 as address3_92_5_, this_.address_type_cd as address73_92_5_, this_.bcustom1 as bcustom4_92_5_, this_.bcustom10 as bcustom5_92_5_, this_.bcustom11 as bcustom6_92_5_, this_.bcustom12 as bcustom7_92_5_, this_.bcustom13 as bcustom8_92_5_, this_.bcustom14 as bcustom9_92_5_, this_.bcustom15 as bcustom10_92_5_, this_.bcustom16 as bcustom11_92_5_, this_.bcustom17 as bcustom12_92_5_, this_.bcustom18 as bcustom13_92_5_, this_.bcustom19 as bcustom14_92_5_, this_.bcustom2 as bcustom15_92_5_, this_.bcustom20 as bcustom16_92_5_, this_.bcustom3 as bcustom17_92_5_, this_.bcustom4 as bcustom18_92_5_, this_.bcustom5 as bcustom19_92_5_, this_.bcustom6 as bcustom20_92_5_, this_.bcustom7 as bcustom21_92_5_, this_.bcustom8 as bcustom22_92_5_, this_.bcustom9 as bcustom23_92_5_, this_.birth_order as birth24_92_5_, this_.birth_place as birth25_92_5_, this_.city as city92_5_, this_.country as country92_5_, this_.country_code as country28_92_5_, this_.custom1 as custom29_92_5_, this_.custom10 as custom30_92_5_, this_.custom11 as custom31_92_5_, this_.custom12 as custom32_92_5_, this_.custom13 as custom33_92_5_, this_.custom14 as custom34_92_5_, this_.custom15 as custom35_92_5_, this_.custom16 as custom36_92_5_, this_.custom17 as custom37_92_5_, this_.custom18 as custom38_92_5_, this_.custom19 as custom39_92_5_, this_.custom2 as custom40_92_5_, this_.custom20 as custom41_92_5_, this_.custom3 as custom42_92_5_, this_.custom4 as custom43_92_5_, this_.custom5 as custom44_92_5_, this_.custom6 as custom45_92_5_, this_.custom7 as custom46_92_5_, this_.custom8 as custom47_92_5_, this_.custom9 as custom48_92_5_, this_.date_changed as date49_92_5_, this_.date_created as date50_92_5_, this_.date_of_birth as date51_92_5_, this_.date_voided as date52_92_5_, this_.death_ind as death53_92_5_, this_.death_time as death54_92_5_, this_.degree as degree92_5_, this_.email as email92_5_, this_.ethnic_group_cd as ethnic75_92_5_, this_.family_name as family57_92_5_, this_.family_name2 as family58_92_5_, this_.gender_cd as gender83_92_5_, this_.given_name as given59_92_5_, this_.language_cd as language76_92_5_, this_.marital_status_code as marital60_92_5_, this_.middle_name as middle61_92_5_, this_.mothers_maiden_name as mothers62_92_5_, this_.multiple_birth_ind as multiple63_92_5_, this_.name_type_cd as name78_92_5_, this_.nationality_cd as nationa82_92_5_, this_.phone_area_code as phone64_92_5_, this_.phone_country_code as phone65_92_5_, this_.phone_ext as phone66_92_5_, this_.phone_number as phone67_92_5_, this_.postal_code as postal68_92_5_, this_.prefix as prefix92_5_, this_.race_cd as race74_92_5_, this_.religion_cd as religion77_92_5_, this_.ssn as ssn92_5_, this_.state as state92_5_, this_.suffix as suffix92_5_, this_.changed_by_id as changed79_92_5_, this_.creator_id as creator81_92_5_, this_.voided_by_id as voided80_92_5_, personiden2_.person_id as person8_7_, personiden2_.person_identifier_id as person1_7_, personiden2_.person_identifier_id as person1_94_0_, personiden2_.date_created as date2_94_0_, personiden2_.date_voided as date3_94_0_, personiden2_.identifier as identifier94_0_, personiden2_.identifier_domain_id as identifier5_94_0_, personiden2_.person_id as person8_94_0_, personiden2_.creator_id as creator7_94_0_, personiden2_.voided_by_id as voided6_94_0_, identifier3_.identifier_domain_id as identifier1_87_1_, identifier3_.date_created as date2_87_1_, identifier3_.namespace_identifier as namespace3_87_1_, identifier3_.universal_identifier as universal4_87_1_, identifier3_.universal_identifier_type_code as universal5_87_1_, identifier3_.creator_id as creator6_87_1_, user4_.id as id99_2_, user4_.account_expired as account2_99_2_, user4_.account_locked as account3_99_2_, user4_.address as address99_2_, user4_.city as city99_2_, user4_.country as country99_2_, user4_.postal_code as postal7_99_2_, user4_.province as province99_2_, user4_.credentials_expired as credenti9_99_2_, user4_.email as email99_2_, user4_.account_enabled as account11_99_2_, user4_.first_name as first12_99_2_, user4_.last_name as last13_99_2_, user4_.password as password99_2_, user4_.password_hint as password15_99_2_, user4_.phone_number as phone16_99_2_, user4_.username as username99_2_, user4_.version as version99_2_, user4_.website as website99_2_, user5_.id as id99_3_, user5_.account_expired as account2_99_3_, user5_.account_locked as account3_99_3_, user5_.address as address99_3_, user5_.city as city99_3_, user5_.country as country99_3_, user5_.postal_code as postal7_99_3_, user5_.province as province99_3_, user5_.credentials_expired as credenti9_99_3_, user5_.email as email99_3_, user5_.account_enabled as account11_99_3_, user5_.first_name as first12_99_3_, user5_.last_name as last13_99_3_, user5_.password as password99_3_, user5_.password_hint as password15_99_3_, user5_.phone_number as phone16_99_3_, user5_.username as username99_3_, user5_.version as version99_3_, user5_.website as website99_3_, user6_.id as id99_4_, user6_.account_expired as account2_99_4_, user6_.account_locked as account3_99_4_, user6_.address as address99_4_, user6_.city as city99_4_, user6_.country as country99_4_, user6_.postal_code as postal7_99_4_, user6_.province as province99_4_, user6_.credentials_expired as credenti9_99_4_, user6_.email as email99_4_, user6_.account_enabled as account11_99_4_, user6_.first_name as first12_99_4_, user6_.last_name as last13_99_4_, user6_.password as password99_4_, user6_.password_hint as password15_99_4_, user6_.phone_number as phone16_99_4_, user6_.username as username99_4_, user6_.version as version99_4_, user6_.website as website99_4_ from person this_ left outer join person_identifier personiden2_ on this_.person_id=personiden2_.person_id left outer join identifier_domain identifier3_ on personiden2_.identifier_domain_id=identifier3_.identifier_domain_id left outer join app_user user4_ on identifier3_.creator_id=user4_.id left outer join app_user user5_ on personiden2_.creator_id=user5_.id left outer join app_user user6_ on personiden2_.voided_by_id=user6_.id where this_.date_voided is null and this_.custom5=?
16:55:12,055 ERROR [STDERR] org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select this_.person_id as person1_92_5_, this_.address1 as address2_92_5_, this_.address2 as address3_92_5_, this_.address_type_cd as address73_92_5_, this_.bcustom1 as bcustom4_92_5_, this_.bcustom10 as bcustom5_92_5_, this_.bcustom11 as bcustom6_92_5_, this_.bcustom12 as bcustom7_92_5_, this_.bcustom13 as bcustom8_92_5_, this_.bcustom14 as bcustom9_92_5_, this_.bcustom15 as bcustom10_92_5_, this_.bcustom16 as bcustom11_92_5_, this_.bcustom17 as bcustom12_92_5_, this_.bcustom18 as bcustom13_92_5_, this_.bcustom19 as bcustom14_92_5_, this_.bcustom2 as bcustom15_92_5_, this_.bcustom20 as bcustom16_92_5_, this_.bcustom3 as bcustom17_92_5_, this_.bcustom4 as bcustom18_92_5_, this_.bcustom5 as bcustom19_92_5_, this_.bcustom6 as bcustom20_92_5_, this_.bcustom7 as bcustom21_92_5_, this_.bcustom8 as bcustom22_92_5_, this_.bcustom9 as bcustom23_92_5_, this_.birth_order as birth24_92_5_, this_.birth_place as birth25_92_5_, this_.city as city92_5_, this_.country as country92_5_, this_.country_code as country28_92_5_, this_.custom1 as custom29_92_5_, this_.custom10 as custom30_92_5_, this_.custom11 as custom31_92_5_, this_.custom12 as custom32_92_5_, this_.custom13 as custom33_92_5_, this_.custom14 as custom34_92_5_, this_.custom15 as custom35_92_5_, this_.custom16 as custom36_92_5_, this_.custom17 as custom37_92_5_, this_.custom18 as custom38_92_5_, this_.custom19 as custom39_92_5_, this_.custom2 as custom40_92_5_, this_.custom20 as custom41_92_5_, this_.custom3 as custom42_92_5_, this_.custom4 as custom43_92_5_, this_.custom5 as custom44_92_5_, this_.custom6 as custom45_92_5_, this_.custom7 as custom46_92_5_, this_.custom8 as custom47_92_5_, this_.custom9 as custom48_92_5_, this_.date_changed as date49_92_5_, this_.date_created as date50_92_5_, this_.date_of_birth as date51_92_5_, this_.date_voided as date52_92_5_, this_.death_ind as death53_92_5_, this_.death_time as death54_92_5_, this_.degree as degree92_5_, this_.email as email92_5_, this_.ethnic_group_cd as ethnic75_92_5_, this_.family_name as family57_92_5_, this_.family_name2 as family58_92_5_, this_.gender_cd as gender83_92_5_, this_.given_name as given59_92_5_, this_.language_cd as language76_92_5_, this_.marital_status_code as marital60_92_5_, this_.middle_name as middle61_92_5_, this_.mothers_maiden_name as mothers62_92_5_, this_.multiple_birth_ind as multiple63_92_5_, this_.name_type_cd as name78_92_5_, this_.nationality_cd as nationa82_92_5_, this_.phone_area_code as phone64_92_5_, this_.phone_country_code as phone65_92_5_, this_.phone_ext as phone66_92_5_, this_.phone_number as phone67_92_5_, this_.postal_code as postal68_92_5_, this_.prefix as prefix92_5_, this_.race_cd as race74_92_5_, this_.religion_cd as religion77_92_5_, this_.ssn as ssn92_5_, this_.state as state92_5_, this_.suffix as suffix92_5_, this_.changed_by_id as changed79_92_5_, this_.creator_id as creator81_92_5_, this_.voided_by_id as voided80_92_5_, personiden2_.person_id as person8_7_, personiden2_.person_identifier_id as person1_7_, personiden2_.person_identifier_id as person1_94_0_, personiden2_.date_created as date2_94_0_, personiden2_.date_voided as date3_94_0_, personiden2_.identifier as identifier94_0_, personiden2_.identifier_domain_id as identifier5_94_0_, personiden2_.person_id as person8_94_0_, personiden2_.creator_id as creator7_94_0_, personiden2_.voided_by_id as voided6_94_0_, identifier3_.identifier_domain_id as identifier1_87_1_, identifier3_.date_created as date2_87_1_, identifier3_.namespace_identifier as namespace3_87_1_, identifier3_.universal_identifier as universal4_87_1_, identifier3_.universal_identifier_type_code as universal5_87_1_, identifier3_.creator_id as creator6_87_1_, user4_.id as id99_2_, user4_.account_expired as account2_99_2_, user4_.account_locked as account3_99_2_, user4_.address as address99_2_, user4_.city as city99_2_, user4_.country as country99_2_, user4_.postal_code as postal7_99_2_, user4_.province as province99_2_, user4_.credentials_expired as credenti9_99_2_, user4_.email as email99_2_, user4_.account_enabled as account11_99_2_, user4_.first_name as first12_99_2_, user4_.last_name as last13_99_2_, user4_.password as password99_2_, user4_.password_hint as password15_99_2_, user4_.phone_number as phone16_99_2_, user4_.username as username99_2_, user4_.version as version99_2_, user4_.website as website99_2_, user5_.id as id99_3_, user5_.account_expired as account2_99_3_, user5_.account_locked as account3_99_3_, user5_.address as address99_3_, user5_.city as city99_3_, user5_.country as country99_3_, user5_.postal_code as postal7_99_3_, user5_.province as province99_3_, user5_.credentials_expired as credenti9_99_3_, user5_.email as email99_3_, user5_.account_enabled as account11_99_3_, user5_.first_name as first12_99_3_, user5_.last_name as last13_99_3_, user5_.password as password99_3_, user5_.password_hint as password15_99_3_, user5_.phone_number as phone16_99_3_, user5_.username as username99_3_, user5_.version as version99_3_, user5_.website as website99_3_, user6_.id as id99_4_, user6_.account_expired as account2_99_4_, user6_.account_locked as account3_99_4_, user6_.address as address99_4_, user6_.city as city99_4_, user6_.country as country99_4_, user6_.postal_code as postal7_99_4_, user6_.province as province99_4_, user6_.credentials_expired as credenti9_99_4_, user6_.email as email99_4_, user6_.account_enabled as account11_99_4_, user6_.first_name as first12_99_4_, user6_.last_name as last13_99_4_, user6_.password as password99_4_, user6_.password_hint as password15_99_4_, user6_.phone_number as phone16_99_4_, user6_.username as username99_4_, user6_.version as version99_4_, user6_.website as website99_4_ from person this_ left outer join person_identifier personiden2_ on this_.person_id=personiden2_.person_id left outer join identifier_domain identifier3_ on personiden2_.identifier_domain_id=identifier3_.identifier_domain_id left outer join app_user user4_ on identifier3_.creator_id=user4_.id left outer join app_user user5_ on personiden2_.creator_id=user5_.id left outer join app_user user6_ on personiden2_.voided_by_id=user6_.id where this_.date_voided is null and this_.custom5=?]; SQL state [25P01]; error code [0]; Large Objects may not be used in auto-commit mode.; nested exception is org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.
16:55:12,069 ERROR [STDERR] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
16:55:12,069 ERROR [STDERR] at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
16:55:12,070 ERROR [STDERR] at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
16:55:12,074 ERROR [STDERR] at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
16:55:12,075 ERROR [STDERR] at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
16:55:12,076 ERROR [STDERR] at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
16:55:12,077 ERROR [STDERR] at org.openhie.openempi.dao.hibernate.PersonDaoHibernate.getPersons(PersonDaoHibernate.java:145)
16:55:12,077 ERROR [STDERR] at org.openhie.openempi.service.impl.PersonQueryServiceImpl.findPersonsByAttributes(PersonQueryServiceImpl.java:101)
16:55:12,078 ERROR [STDERR] at org.openhie.openempi.blocking.impl.BlockingBypassServiceImpl.getRecordPairs(BlockingBypassServiceImpl.java:44)
16:55:12,079 ERROR [STDERR] at org.openhie.openempi.matching.fellegisunter.ProbabilisticMatchingService.getAllRecordPairs(ProbabilisticMatchingService.java:144)
16:55:12,080 ERROR [STDERR] at org.openempi.webapp.server.PersonDataServiceImpl.testScorePairs(PersonDataServiceImpl.java:236)
16:55:12,081 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
16:55:12,081 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
16:55:12,082 ERROR [STDERR] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
16:55:12,083 ERROR [STDERR] at java.lang.reflect.Method.invoke(Method.java:597)
16:55:12,083 ERROR [STDERR] at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:527)
16:55:12,084 ERROR [STDERR] at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:166)
16:55:12,085 ERROR [STDERR] at com.google.gwt.user.server.rpc.RemoteServiceServlet.doPost(RemoteServiceServlet.java:86)
16:55:12,085 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
16:55:12,086 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
16:55:12,087 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
16:55:12,088 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
16:55:12,088 ERROR [STDERR] at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
16:55:12,089 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
16:55:12,090 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
16:55:12,090 ERROR [STDERR] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
16:55:12,091 ERROR [STDERR] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
16:55:12,092 ERROR [STDERR] at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)
16:55:12,093 ERROR [STDERR] at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
16:55:12,093 ERROR [STDERR] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
16:55:12,094 ERROR [STDERR] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
16:55:12,095 ERROR [STDERR] at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
16:55:12,096 ERROR [STDERR] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
16:55:12,096 ERROR [STDERR] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
16:55:12,097 ERROR [STDERR] at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
16:55:12,098 ERROR [STDERR] at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
16:55:12,098 ERROR [STDERR] at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
16:55:12,099 ERROR [STDERR] at java.lang.Thread.run(Thread.java:619)
16:55:12,100 ERROR [STDERR] Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.
16:55:12,101 ERROR [STDERR] at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:172)
16:55:12,102 ERROR [STDERR] at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:158)
16:55:12,102 ERROR [STDERR] at org.postgresql.jdbc2.AbstractJdbc2BlobClob.<init>(AbstractJdbc2BlobClob.java:39)
16:55:12,103 ERROR [STDERR] at org.postgresql.jdbc2.AbstractJdbc2Blob.<init>(AbstractJdbc2Blob.java:23)
16:55:12,104 ERROR [STDERR] at org.postgresql.jdbc3.AbstractJdbc3Blob.<init>(AbstractJdbc3Blob.java:22)
16:55:12,104 ERROR [STDERR] at org.postgresql.jdbc3.Jdbc3Blob.<init>(Jdbc3Blob.java:20)
16:55:12,105 ERROR [STDERR] at org.postgresql.jdbc3.Jdbc3ResultSet.getBlob(Jdbc3ResultSet.java:54)
16:55:12,106 ERROR [STDERR] at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:323)
16:55:12,106 ERROR [STDERR] at org.apache.commons.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:526)
16:55:12,107 ERROR [STDERR] at org.apache.commons.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:526)
16:55:12,108 ERROR [STDERR] at org.hibernate.type.BlobType.get(BlobType.java:57)
16:55:12,108 ERROR [STDERR] at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:111)
16:55:12,109 ERROR [STDERR] at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
16:55:12,110 ERROR [STDERR] at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2101)
16:55:12,110 ERROR [STDERR] at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
16:55:12,111 ERROR [STDERR] at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)
16:55:12,112 ERROR [STDERR] at org.hibernate.loader.Loader.getRow(Loader.java:1206)
16:55:12,112 ERROR [STDERR] at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
16:55:12,113 ERROR [STDERR] at org.hibernate.loader.Loader.doQuery(Loader.java:701)
16:55:12,113 ERROR [STDERR] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
16:55:12,114 ERROR [STDERR] at org.hibernate.loader.Loader.doList(Loader.java:2213)
16:55:12,115 ERROR [STDERR] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
16:55:12,115 ERROR [STDERR] at org.hibernate.loader.Loader.list(Loader.java:2099)
16:55:12,116 ERROR [STDERR] at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
16:55:12,117 ERROR [STDERR] at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
16:55:12,117 ERROR [STDERR] at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
16:55:12,118 ERROR [STDERR] at org.openhie.openempi.dao.hibernate.PersonDaoHibernate$4.doInHibernate(PersonDaoHibernate.java:149)
16:55:12,119 ERROR [STDERR] at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
16:55:12,119 ERROR [STDERR] ... 33 more

Many forums suggested to turn off the autocommit, while others reported that autocommit is off anyway.
I tried a different way to solve the problem, I don't want to modify my program's default behavior.
In one forum discussion a user advice was that the problematic queries should be placed into a transaction.

I nested the queries into a transaction, and now it works:

session.beginTransaction();
...// queries
session.getTransaction().commit();

Much better solution for my last post is to mark the appropriate function with @Transactional annotation.

The whole problem for me now with these lazy fetched blobs is that you can access your entities lazy Blobs only when the connection for the particular entity query is open. This means that you have to take care of the Blob fetch in advance, during the query, you cannot just get the Blob data from the entity, you'll get an exception.

What I do now is that I introduced a SerialBlob type field (marked with @Transient annotation) into my entity, and there are two types of queries. One doesn't fetch the blob, the other fetches the Blob during the query. The one which fetches basically just serializes the content of the Blob field into the SerialBlob field, what I can access later any time.