Solution to "current transaction is aborted" error for PostgreSQL compatibility with ODBC/JDBC

Solution to "current transaction is aborted" error for PostgreSQL compatibility with ODBC/JDBC

Posted on July 02, 2013 0 Comments

ERROR: current transaction is aborted, commands ignored until end of transaction block

Developers either love or hate this PostgreSQL, or Postgres, error.  For developers new to Postgres or expanding Postgres support to their applications, the perspective is a little different since they find out that Postgres handles transactions differently than common databases such as Oracle, SQL Server, or MySQL.

Here's a very simple example:

> start a transaction

> DROP SEQUENCE FOO; ==>"42P01: Error while executing the query; ERROR: sequence "FOO" does not exist"

> SELECT * FROM T1; ==>ERROR: current transaction is aborted, commands ignored until end of transaction block> The SELECT and subsequent statements are expected to succeed against Oracle and SQL Server. This transaction failure in Postgres can also be caused by syntax errors or failed existence checks within a transaction.   I think it's a reasonable interpretation of an atomic transaction, but the rest of the RDBMS world behaves differently causing headaches for shops adopting Postgres and ISVs adding Postgres support.

Many Problems

  1. ISVs that support a wide range of RDBMS cannot support Postgres with their existing code.
  2. Applications cannot consistently support a wide range of Postgres versions.  This issue often surfaces during an upgrade and I've even seen it go away once (8.01 to 8.02).
  3. Existing applications such as Oracle Warehouse Builder, SAP Business Objects, Oracle Database Gateway for ODBC expect this transaction behavior to match Oracle and SQL Server.  I have even seen experimental branches of the open source Postgres driver built to workaround it.
  4. Different flavors of Postgres may have different behavior.  I have seen reports of this behavior from Amazon Redshift shops.  However EnterpriseDB added configuration parameters such as "edb_stmt_level_tx" to change the behavior.

One Solution

DataDirect has been building "standard and interoperable" connectivity for decades and we have a proven approach for this type of scenario which is one of hundreds we have solved to date.  For this particular behavior, we introduced a connection option in the Connect for ODBC Postgres driver and Connect for JDBC Postgres driver so your application(s) will work, without code changes, with any supported version of Postgres:

Transaction Error Behavior

Determines how the driver handles errors that occur within a transaction. When an error occurs in a transaction, the PostgreSQL server does not allow any operations on the connection except for rolling back the transaction.

Valid Values 0 | 1 | 2

If set to 0 (None), the driver does not roll back the transaction when an error occurs. The application must handle the error and roll back the transaction. Any operation on the statement other than a rollback results in an error.

If set to 1 (Rollback Transaction), the driver rolls back the transaction when an error occurs. In addition to the original error message, the driver posts an error message indicating that the transaction has been rolled back.

If set to 2 (Rollback Savepoint), the driver rolls back the transaction to the last savepoint when an error is detected. In manual commit mode, the driver automatically sets a savepoint after each statement issued. This value makes transaction behavior resemble that of most other database system types, but uses more resources on the database server and may incur a slight performance penalty.

Other options for Postgres compatibility with applications that support multiple databases

  • Enable Keyset Cursors
  • Extended Column Metadata
  • Fetch Ref Cursors
  • Fetch TWFS as Time
  • Max Char Size
  • Max Long Varchar Size
  • XML Describe Type

Complete details can be found in the ODBC user's guide and JDBC user's guide, along with dozens of workaround properties ensuring compatibility across all applications.

High performance to PostgreSQL is included

Increased throughput and efficient resource utilization (memory and CPU) across common workloads against the competing driver

postgres-performance

postgres-memory-cpus

Note: DataDirect Performance Lab results are not intended to substitute testing in a production like environment.

Getting Started

Sumit Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments

Comments are disabled in preview mode.
Topics

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation