Getting error during insertion of the data in postgresql 9.3

Question

currently i am using PostgreSQL Professional Advanced Server (PPAS) 9.3 (a modified version of PostgreSQL 9.3).

I'm trying to insert a row in to postgresql database and getting following error:

error log:
javax.ejb.EJBTransactionRolledbackException: [2] An exception has occurred: ERROR: current transaction is aborted, commands ignored until end of transaction
block
        at org.jboss.ejb3.tx.Ejb3TxPolicy.handleInCallerTx(Ejb3TxPolicy.java:87)
        at org.jboss.aspects.tx.TxPolicy.invokeInCallerTx(TxPolicy.java:130)
        at org.jboss.aspects.tx.TxInterceptor$Supports.invoke(TxInterceptor.java:148)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
        at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:110)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46)
        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:662)
Caused by: com.code.Exception: [2] An exception has occurred: ERROR: current transaction is aborted, commands ignored until end of transaction bl
ock
        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.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
        at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
        at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
        at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
        at org.jboss.aspects.tx.TxPolicy.invokeInCallerTx(TxPolicy.java:126)
        ... 75 more
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
     org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)

        ... 95 more

I tried to create begin transaction object first and then rollback if exception comes it didn't help me.

even I am using single insert statement. As i know that if previous statement fails to execute then this error comes but in my case i have only single statement only but also i am getting the same error.

this is the code which is not working in postgresql only,we are using the same code in oracle and sql server,which is working. yes i executed the same query on ppas, it's executing without any error. Before this we have executed on statement which give error but after that we have closed the connection

please help me?

Is this new code, or code that worked before? If it's new, reduce the code to the minimum needed to reproduce the error, edit your question, and paste it in. If it worked before, what changed? Can you execute the same query successfully in pgAdminIII or psql? — Mike Sherrill 'Cat Recall', Jun 30 '14 at 11:39

score 1 · Answer 1 · answered Jun 30 '14 at 12:49

1

This:

Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

indicates that a previous statement failed, and your code did not trap the exception and issue a ROLLBACK. So it's trying to continue to use a transaction that's in the aborted state.

PostgreSQL requires an explicit ROLLBACK after a failed statement when autocommit is off. It will never implicitly rollback and start a new transaction. It can't ignore statements with errors and continue with the transaction - the whole transaction must be aborted and retried (unless you manually use savepoints).

This is different to many databases, so it has probably exposed an underlying bug in the code where it does something that causes an ERROR, then ignores the error and carries on. Look at the PostgreSQL server error logs to see what the first error was, and what statement it was.

answered Jun 30 '14 at 12:49

Craig Ringer

307,061
76
688
778

I seem to recall that datagenic can (tries to?) exercise some control over rollback and checkpoint behavior, but I couldn't find the docs this morning. – Mike Sherrill 'Cat Recall' Jun 30 '14 at 13:29
@MikeSherrill'CatRecall' It'd be handy if PgJDBC supported automatic savepoints - though savepoints in PostgreSQL are kind of expensive. psqlODBC supports them though. – Craig Ringer Jun 30 '14 at 14:37
@Craig after adding conn.rollback(); in the code, the error is still remains.1st time of connection i am using normal jdbc and in 2nd time i am using HibernateUtil.currentSession().connection(). so could you please tell me the exact reason why is it happening. – Krishna Jul 14 '14 at 11:44

score 0 · Answer 2 · answered Jul 25 '14 at 13:29

After doing so much googling and R&D from more than one month finally i got success to fix the above described common error of postgresql 9.3 by setting the value of "edb_stmt_level_tx = off" to "on" in PPAS9.3 postgresql.config property file. For more information please visit below given link. for more information please visit this link

Getting error during insertion of the data in postgresql 9.3

2 Answers2