org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode
I am using Spring , JPA, Hibernate, Postgresql. I can upload/insert a file to the database. But I got the error when tried to access the file.
EVERE: Servlet.service() for servlet default threw exception
org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.
at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:200)
at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:172)
at org.postgresql.jdbc2.AbstractJdbc2BlobClob.<init>(AbstractJdbc2BlobClob.java:47)
at org.postgresql.jdbc2.AbstractJdbc2Blob.<init>(AbstractJdbc2Blob.java:21)
at org.postgresql.jdbc3.AbstractJdbc3Blob.<init>(AbstractJdbc3Blob.java:19)
at org.postgresql.jdbc4.AbstractJdbc4Blob.<init>(AbstractJdbc4Blob.java:20)
at org.postgresql.jdbc4.Jdbc4Blob.<init>(Jdbc4Blob.java:20)
at org.postgresql.jdbc4.Jdbc4ResultSet.getBlob(Jdbc4ResultSet.java:52)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:335)
at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:527)
at org.hibernate.type.ByteArrayBlobType.get(ByteArrayBlobType.java:112)
at org.hibernate.type.AbstractLobType.nullSafeGet(AbstractLobType.java:68)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2267)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1423)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1351)
at org.hibernate.loader.Loader.getRow(Loader.java:1251)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:619)
at org.hibernate.loader.Loader.doQuery(Loader.java:745)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.loadCollection(Loader.java:2062)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:62)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:628)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:83)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1853)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:366)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:108)
at org.hibernate.collection.PersistentBag.iterator(PersistentBag.java:272)
at org.apache.taglibs.standard.tag.common.core.ForEachSupport.toForEachIterator(ForEachSupport.java:382)
at org.apache.taglibs.standard.tag.common.core.ForEachSupport.supportedTypeForEachIterator(ForEachSupport.java:258)
at org.apache.taglibs.standard.tag.common.core.ForEachSupport.prepare(ForEachSupport.java:189)
at javax.servlet.jsp.jstl.core.LoopTagSupport.doStartTag(LoopTagSupport.java:287)
at org.apache.jsp.WEB_002dINF.jsp.purchaseOrder.editForm_jsp._jspx_meth_c_005fforEach_005f4(editForm_jsp.java:1884)
at org.apache.jsp.WEB_002dINF.jsp.purchaseOrder.editForm_jsp._jspService(editForm_jsp.java:131)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:551)
at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:488)
at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:968)
at org.apache.jasper.runtime.PageContextImpl.doInclude(PageContextImpl.java:650)
at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:644)
at org.apache.tiles.jsp.context.JspTilesRequestContext.include(JspTilesRequestContext.java:103)
at org.apache.tiles.jsp.context.JspTilesRequestContext.dispatch(JspTilesRequestContext.java:96)
at org.apache.tiles.renderer.impl.TemplateAttributeRenderer.write(TemplateAttributeRenderer.java:44)
at org.apache.tiles.renderer.impl.AbstractBaseAttributeRenderer.render(AbstractBaseAttributeRenderer.java:106)
at org.apache.tiles.renderer.impl.ChainedDelegateAttributeRenderer.write(ChainedDelegateAttributeRenderer.java:76)
at org.apache.tiles.renderer.impl.AbstractBaseAttributeRenderer.render(AbstractBaseAttributeRenderer.java:106)
at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:670)
at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:336)
at org.apache.tiles.template.InsertAttributeModel.renderAttribute(InsertAttributeModel.java:210)
at org.apache.tiles.template.InsertAttributeModel.end(InsertAttributeModel.java:126)
at org.apache.tiles.jsp.taglib.InsertAttributeTag.doTag(InsertAttributeTag.java:311)
at org.apache.jsp.WEB_002dINF.templates.main_jsp._jspx_meth_tiles_005finsertAttribute_005f2(main_jsp.java:619)
at org.apache.jsp.WEB_002dINF.templates.main_jsp._jspService(main_jsp.java:178)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
at org.apache.tiles.servlet.context.ServletTilesRequestContext.forward(ServletTilesRequestContext.java:241)
at org.apache.tiles.servlet.context.ServletTilesRequestContext.dispatch(ServletTilesRequestContext.java:222)
at org.apache.tiles.renderer.impl.TemplateAttributeRenderer.write(TemplateAttributeRenderer.java:44)
at org.apache.tiles.renderer.impl.AbstractBaseAttributeRenderer.render(AbstractBaseAttributeRenderer.java:106)
at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:670)
at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:690)
at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:644)
at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:627)
at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:321)
at org.springbyexample.web.servlet.view.tiles2.DynamicTilesViewProcessor.renderMergedOutputModel(DynamicTilesViewProcessor.java:106)
at org.springbyexample.web.servlet.view.tiles2.DynamicTilesView.renderMergedOutputModel(DynamicTilesView.java:104)
at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:250)
at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1060)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:798)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
at org.tuckey.web.filters.urlrewrite.NormalRewrittenUrl.doRewrite(NormalRewrittenUrl.java:213)
at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:171)
at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:381)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:96)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:113)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
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:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Unknown Source)
When I remove the piece code from the JSP file, it is fine.
<c:forEach items="${purchaseOrder.purchaseOrderQuotes}" var="document">
<tr>
<td>
${document.name}
</td>
<td>
${document.description}
</td>
<td align="left">
${document.filename}
</td>
<td>
${document.created}
</td>
</td>
</tr>
</c:forEach>
the method that I retrieve the purchaseOrder:
@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
public PurchaseOrder findById(Integer id)
{
log.debug((new StringBuilder("getting PurchaseOrder instance with id: ")).append(id).toString());
try
{
PurchaseOrder instance = entityManager.find(PurchaseOrder.class, id);
// if(instance.getProjectFundingYears() != null)
// log.debug("get successful");
// log.debug("get successful");
return instance;
}
catch(RuntimeException re)
{
log.error("get failed", re);
throw re;
}
}
Here is the relationship of two Entities:
PurchaseOrder
@Entity
@Table(name = "purchase_order", schema = "pta")
public class PurchaseOrder implements java.io.Serializable {
............
private List<PurchaseOrderQuotes> purchaseOrderQuotes = ShrinkableLazyList
.decorate(new ArrayList(), FactoryUtils
.instantiateFactory(PurchaseOrderQuotes.class));
.............................................................
public PurchaseOrder() {
}
....................................................................
@OneToMany(cascade = CascadeType.REFRESH,fetch=FetchType.LAZY, mappedBy="purchaseOrder")
public List<PurchaseOrderQuotes> getPurchaseOrderQuotes() {
return purchaseOrderQuotes;
}
public void setPurchaseOrderQuotes(List<PurchaseOrderQuotes> purchaseOrderQuotes) {
this.purchaseOrderQuotes = purchaseOrderQuotes;
}
............................................................................
}
PurchaseOrderQuotes
@Entity
@Table(name = "purchase_order_quotes", schema = "pta")
public class PurchaseOrderQuotes implements java.io.Serializable {
private Integer id;
private String name;
private String description;
private String filename;
private byte[] content;
private String contentType;
private Date created;
private PurchaseOrder purchaseOrder;
public PurchaseOrderQuotes() {
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "purchase_order_quotes_id", unique = true, nullable = false)
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@Column(name="name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Column(name="description")
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
@Column(name="filename")
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
@Lob
@Type(type="org.hibernate.type.PrimitiveByteArrayBlobType")
@Basic(fetch = FetchType.LAZY)
@Column(name="content")
public byte[] getContent() {
return content;
}
public void setContent(byte[] content) {
this.content = content;
}
@Column(name="content_type")
public String getContentType() {
return contentType;
}
public void setContentType(String contentType) {
this.contentType = contentType;
}
@Temporal(TemporalType.TIMESTAMP)
@Column(name="created")
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
@ManyToOne
@JoinColumn(name = "purchase_order_id", referencedColumnName = "purchase_order_id")
public PurchaseOrder getPurchaseOrder() {
return purchaseOrder;
}
public void setPurchaseOrder(PurchaseOrder purchaseOrder) {
this.purchaseOrder = purchaseOrder;
}
I am not clear . Sorry. Here I dont access the BLOB file directly, I get its parent object.
I was able to upload the file and retrieve one purchaseOrder without any problem. It only reports error when I add this piece code in the JSP file. But it should not try to access the BLOB field since I set FetchType.LAZY. I get confused.
<c:forEach items="${purchaseOrder.purchaseOrderQuotes}" var="document">
<tr>
<td>
${document.name}
</td>
<td>
${document.description}
</td>
<td align="left">
${document.filename}
</td>
<td>
${document.created}
</td>
</td>
</tr>
</c:forEach>
Answer
Since you have defined your Spring transactions via @Transactional, you are by default running inside of an auto-commit transaction. As per this other thread, you need to create a second session factory which runs in autocommit = false to retrieve the file.
Additionally, the DAO for the retrieval should be annotated with @Qualifier so that it knows which session factory to use. Example:
@Autowired
public MyDAOImpl(@Qualifier("someSessionFactory") SessionFactory sessionFactory) {
setSessionFactory(sessionFactory);
}