Home Services Partners Company
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

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

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.