[frs-375] Transactions in PostgreSQL

While working on the QueryPlan feature in FusionReactor 6.0.0 we discovered that there is some odd behaviour from PostgreSQL when compared to the other popular database systems (MSSQL, MySQL, Oracle). Consider the following code sample:

Insert duplicate primary keys
  final Connection conn = DriverManager.getConnection( url, userName, password );
  conn.setAutoCommit(false);
  try
  {
    final Statement statement = conn.createStatement();
    statement.execute("insert into names (id, name) values (1,'Ben')");
    try
    {
      statement.execute("insert into names (id, name) values (1,'Ben')");
    }
    catch(Exception e)
    {
    }
  }
  finally
  {
    conn.commit();
  }

We would expect that although the first insert is fine, the second would cause a constraint violation as the id column is the primary key. However as this is inside the try/catch block, the error is handled, and the when the commit is called, the first insert is persisted to the database. This is indeed the case when using MySQL, MSSQL, Oracle; however while we where testing the QueryPlan feature it came to our attention that this is NOT the case when using PostgreSQL.

When using PostgreSQL when the error is found during the transaction, namely the duplicate primary key, this then causes the commit to fail. This seems to be due to a feature in the PostgreSQL database; which causes the driver to roll back the transaction on error, even if the commit command is called.

So let me explain.

FusionReactor attempts to run the QueryPlan on the PostgreSQL server using the original command EXPLAIN ANALYZE VERBOSE {0}, where the {0} is the query being explained. The ANALYZE keyword would re-run the query, in our case and insert; which breaks on duplicate key, causing the whole transaction to fail.

If you have the following code block:

Your Code
    final Connection conn = DriverManager.getConnection( url, userName, password );
    conn.setAutoCommit(false);
    final Statement statement = conn.createStatement();
    statement.execute("insert into names (id, name) values (1,'Ben')");
    conn.commit();

… and this statement triggers FusionReactor to run the QueryPlan, then FusionReactor would run the following:

FusionReactor Code
    final Statement statement = conn.createStatement();
    statement.execute("EXPLAIN ANALYZE VERBOSE insert into names (id, name) values (1,'Ben')");

This statement would then cause a duplicate primary key error, and throw an exception. Of course we wrap this whole block with catches and other safeguards to ensure we do not break your query. Unfortunately, unknown to us, the error is still stored on the connection, and when you call commit this fails and rolls back the whole transaction. The effect of this is that your insert has not run at all.

Solution

We have improved the code in the FusionReactor QueryPlan executor so that the command used for the QueryPlan is EXPLAIN VERBOSE removing the ANALYZE keyword. This stops the command from executing inside the QueryPlan execution. We also implemented a JDBC save point around the explain execution due to the PostgreSQL behavior with the exceptions. This has secured the execution of the QueryPlan for PostgreSQL and stops FusionReactor from preventing the original JDBC call that you where making.

As additional security measures we have implemented several settings that allow you to control FusionReactors behaviour when it executes QueryPlan statements. These are:

Name Value Default Description
__fusionreactor_explainEnabled true or false true Explicitly enable or disable the query plan for this connection
__fusionreactor_explainTemplate URL encoded string for the explain template The default template is different for each supported database and can be seen here This is the SQL statement that is executed to get the QueryPlan information.
__fusionreactor_explainThreshold integer From configuration see documentation This is the threshold to use for this connection, if queries run longer than this then the QueryPlan is performed.
__fusionreactor_explainSavepoint true or false true This setting tells FusionReactor to wrap the explain call with a JDBC save point. This is only used when dealing with PostgreSQL connections.

Additional Reading

http://www.postgresql.org/docs/9.1/static/sql-explain.html

Issue Details

Type: Technote
Issue Number: FRS-375
Components: JDBC
Environment:
Resolution: Fixed
Last Updated: 15/Dec/15 10:44 AM
Affects Version:
Fixed Version: 6.0.2
Server:
Platform:
Related Issues:

Comments are closed.