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

ISVs that support a wide range of RDBMS cannot support Postgres with their existing code.
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).
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.
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


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

Download a trial of the 32-bit or 64-bit DataDirect Connect for ODBC PostgreSQL Wire Protocol driver.
Download a trial of the DataDirect Connect for JDBC PostgreSQL driver
Call 1-800-876-3101 to learn more about Postgres ODBC and JDBC connectivity, along with professional tuning services available from our team of Systems Engineers.