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.
- 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.
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
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.
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.
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.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.Learn More
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
You can also ask us not to share your Personal Information to third parties here: Do Not Sell or Share My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.