AWS Database Blog

Up and running with Apache OFBiz and Amazon Aurora DSQL

When designing Amazon Aurora DSQL, we spent a lot of time trying to get the right balance between innovation and improvement compared to ease of use. We didn’t want to upset familiarity for existing PostgreSQL users unless there were very compelling reasons to do so. Two areas that fall into this category are Optimistic concurrency control (OCC) behavior and the authentication model, where the behavior of the system might feel unfamiliar to application developers that are used to regular PostgreSQL.

In this post, we show you a worked example of taking an existing application that works on PostgreSQL databases and adapting it to work with an Aurora DSQL database. In addition to adapting for the previously mentioned aspects, we also address some data type incompatibilities and work around some limits that currently exist in Aurora DSQL.

The application

To make this example as representative as possible, we selected an open source application that offered significant functional diversity and complexity representative of real-world applications. The application is Apache OFBiz, and the source code can be downloaded from the GitHub repository. The version used in this proof of concept was 18.12.16.

The OFBiz application is a well featured ERP and CRM package, and makes a good example case for our objectives here:

  • Complex schema: 837 tables, 4161 indexes
  • Abstracted entity engine (Java Persistence, Java Transactions, custom ORM)
  • Already supports PostgreSQL
  • Demo data and built-in loader functionality
  • Largely core relational in nature, which aligns well with the initial compatibility focus of Aurora DSQL

The intention of this post is to combine a narrative of our internal proof of concept with some concrete code samples of how to approach Aurora DSQL authentication in a connection pool-based Java application. Although we provide as much context as possible, we don’t aim to provide an exhaustive set of commands and code to recreate the same journey.

Aurora DSQL

Aurora DSQL is a PostgreSQL-Compatible database, but has a few semantic differences that aim to improve the scalability and security of the service compared to community PostgreSQL. Some of these aspects, notably the breadth of functional compatibility with PostgreSQL features, will rapidly evolve over time, but a small number are fundamental to the system. Those are the main focus of the changes made in the OFBiz application and shown in this post. Those aspects are:

  • Aurora DSQL authentication model – How to connect to, and stay connected, to the database.
  • Transaction size limits – Aurora DSQL doesn’t support unbounded transaction sizes.
  • Isolation level – Aurora DSQL only supports snapshot isolation, equivalent to repeatable read in PostgreSQL.
  • Optimistic concurrency control – Aurora DSQL doesn’t take out locks during the build phase of a transaction; it enforces consistency at commit time. This means that commits can fail and must be retried from the beginning.
  • Data type limits in keys – There are currently limitations in which types (and sizes) are supported in primary and secondary keys. Data type limitations in Aurora DSQL will reduce over time as additional type support is added to the product. The mitigations documented in this post reflect the limits in place during the Preview phase of Aurora DSQL.

Create the data source configuration for Aurora DSQL

The first thing we needed to do was define a new data source configuration for the Aurora DSQL database. We added a new data source named localdsql in framework/entity/config/entityengine.xml and selected it as the active data source. The data source parameters were copied from the existing localpostgres data source, with the underlined settings changed as shown:

<group-map group-name="org.apache.ofbiz" datasource-name="localdsql"/>
...
</datasource>
<datasource name="localdsql"
helper-class="org.apache.ofbiz.entity.datasource.GenericHelperDAO"
schema-name="ofbiz"
field-type-name="postgres"
check-on-start="true"
add-missing-on-start="true"
use-fk-initially-deferred="false"
alias-view-columns="false"
join-style="ansi"
use-binary-type-for-blob="true"
use-order-by-nulls="true"
result-fetch-size="50">
<read-data reader-name="tenant"/>
<read-data reader-name="seed"/>
<read-data reader-name="seed-initial"/>
<read-data reader-name="demo"/>
<read-data reader-name="ext"/>
<read-data reader-name="ext-test"/>
<read-data reader-name="ext-demo"/>
<inline-jdbc
jdbc-driver="org.postgresql.Driver"
jdbc-uri="jdbc:postgresql://<Aurora DSQL_URL>/postgres?ssl=require"
jdbc-username="ofbiz"
jdbc-password="IAM_AUTH"
isolation-level="RepeatableRead"
pool-minsize="10"
pool-maxsize="250"
time-between-eviction-runs-millis="600000"/>
</datasource>
XML

In the preceding example configuration, we have redacted the endpoint portion of the jdbc-uri—if you’re trying this on your own cluster, don’t forget to update the <Aurora DSQL_URL> value to reflect the endpoint of your Aurora DSQL cluster.

We set the jdbc-password to a magic constant value of IAM_AUTH. OFBiz assumes that we will use regular password authentication to connect to the database, but Aurora DSQL requires AWS Identity and Access Management (IAM) based authentication, providing a more integrated and secure environment. We add support for this in the code during the next step.

We also changed the isolation level to be RepeatableRead (which is the PostgreSQL equivalent behavior to Aurora DSQL’s snapshot isolation level) and increased the minimum pool size (connections are cheap to maintain in Aurora DSQL, but encrypted connections are relatively slow to create). Aurora DSQL only supports encrypted connections, so we added that parameter to the JDBC URI. We also deployed the latest PG JDBC driver jar (42.7.4 at time of writing) to make sure it was a certified revision for Aurora DSQL.

Implement Aurora DSQL connection logic

There are three considerations when building connection logic for Aurora DSQL:

  • Passwords are actually dynamic authentication tokens, generated on demand when IAM authentication is validated.
  • The authentication tokens expire—they are only valid for the period specified at generation time. This can be up to a week, which is good for using development tools, but we recommend a much lower expiration for application connections to limit any exposure to unauthorized access. We selected 3 hours in this case.
  • Connections also expire, currently every hour. The interaction between the authentication token and connection expiry can be confusing at first to new Aurora DSQL users, so keep in mind that authentication tokens only apply when making a connection (the token must still be valid at time of the new connection), and connection expiry only affects existing connections (no consideration of token expiry occurs after the connection is established).

To implement support for this scheme, we first modified framework/entity/src/main/java/org/apache/ofbiz/entity/config/model/EntityConfig.java to support an Aurora DSQL-specific authentication flow, adding the following logic:

    public static String getJdbcPassword(InlineJdbc inlineJdbcElement) throws GenericEntityConfException {
        String jdbcPassword = inlineJdbcElement.getJdbcPassword();
+       if (!jdbcPassword.isEmpty() && ! jdbcPassword.equals("IAM_AUTH")) {
              return jdbcPassword;
        }
+       if (jdbcPassword.equals("IAM_AUTH")) {
+            return DSQLAuth.generateToken(inlineJdbcElement.getJdbcUri().split("/")[2], 
+                Region.US_EAST_1,
Java

Then, we modified framework/entity/src/main/java/org/apache/ofbiz/entity/connection/DBCPConnectionFactory.java to use an Aurora DSQL-specific ConnectionFactory when the data source is localdsql. We also set the maximum connection lifetime in the pool to be slightly less than the maximum supported connection time in Aurora DSQL.

         // create the connection factory
+        org.apache.commons.dbcp2.ConnectionFactory cf = null;
+        if (cacheKey.equals("localdsql")) {
+           cf = new DSQLConnectionFactory(jdbcDriver, jdbcUri, cfProps);
+        } else {
+           cf = new DriverConnectionFactory(jdbcDriver, jdbcUri, cfProps);
+        }

         // wrap it with a LocalXAConnectionFactory
         XAConnectionFactory xacf = new LocalXAConnectionFactory(txMgr, cf);

         // create the pool object factory
         PoolableConnectionFactory factory = new PoolableManagedConnectionFactory(xacf, null);
+        ((PoolableManagedConnectionFactory)factory).setMaxConnLifetimeMillis((long)55*60*1000);
         factory.setValidationQuery(jdbcElement.getPoolJdbcTestStmt());
Java

Finally, we added the core logic to manage the connections, including adding the Aurora DSQL SessionId to the connection properties for better diagnostics. When contacting AWS support for matters that involve Aurora DSQL, it is helpful to be able to supply the session ID for the specific connection that is affected.

We implemented the following concrete classes:

Modify the schema

We made minimal changes to the underlying schema in order to conform to the limits currently applicable to Aurora DSQL. The majority of the schema was already compatible with Aurora DSQL, but we could have gone further if we didn’t want to use the existing demo data (more on this in the next sections.).

The limits that we needed to address were all related to limits applicable to the types that Aurora DSQL supports in a key:

  • NUMERIC types are not currently supported in keys:
    • We modified applications/datamodel/entitydef/product-entitymodel.xml and changed the type of minimumOrderQuantity to be a floating-point (float) instead of fixed-point (NUMERIC(18,6)). In general, these two types are not logically interchangeable, but some of the demo data was expressed as decimal data. A better change here would have been to modify the demo data, in retrospect, but the logic did seem to work correctly (because it is using greater than tests).
    • We modified framework/entity/fieldtype/fieldtypepostgres.xml to change the definition of <field-type-def type="numeric" sql-type="NUMERIC(20,0)" java-type="Long"/> to <field-type-def type="numeric" sql-type="bigint" java-type="Long"/>. The use of bigint is actually more appropriate than NUMERIC(20,0).
  • VARCHAR types are supported in keys, but with a more modest maximum size than non-key VARCHAR:
    • We modified framework/entity/fieldtype/fieldtypepostgres.xml to change the definition of <field-type-def type="email" sql-type="VARCHAR(320)" java-type="String"/> to <field-type-def type="email" sql-type="VARCHAR(220)" java-type="String"/>.

After these changes were made, the schema could be created successfully as part of the data loading step (see the next section). We explicitly used the -l drop-constraints option in the OFBIZ loader utility to make sure that no table creations were attempted using foreign key constraints, which are not currently supported in Aurora DSQL.

As mentioned earlier, we could have elected to go further in optimizing the schema. One specific change we didn’t need to make was to change the type of ID fields to use the UUID type, which we would normally expect in ORM-based applications. Because we were using demo data, we would have needed to modify all the keys (and references) in the demo data in order to adopt UUID types. Instead, we left the current use of VARCHAR-based keys and loaded the demo data unchanged.

Load the demo data

The OFBiz data loader (see OFBiz Technical Setup Guide) initially performed large numbers of inserts between commits, which were exceeding the transaction size limits in Aurora DSQL for some cases. To remedy this, we modified the storeAll() method in framework/entity/src/main/java/org/apache/ofbiz/entity/GenericDelegator.java to batch bulk data loading into 1,000 row increments:

                         numberChanged += this.store(toStore);
                     }
                 }
+                // commit every 1000 rows to conform to DSQL transaction limits
+                if (numberChanged%1000 == 0) {
+                    if (Debug.verboseOn()) Debug.logVerbose("Committing at 1000 row threshold. beganTransaction=" + beganTransaction, module);
+                    TransactionUtil.commit();
+                    beganTransaction = TransactionUtil.begin();
+                }
             }
             TransactionUtil.commit(beganTransaction);
             return numberChanged;
Java

Note: The transaction size limit in Aurora DSQL is larger than 1,000 rows, but we selected this lower number to more easily observe the batching behavior of the logic change.

We also fixed another problem that was quite interesting to track down. Part of the data load logic would call findKey() in framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityCrypto.java to validate the existence of a crypto key in the database. If the key didn’t exist where it was expected to exist, the data load would stop with an error. When testing with Aurora DSQL, this invariant would be triggered every time. After some digging, we determined that there is a generic bug in the logic here, which results in different behavior for read committed isolation compared to repeatable read. The findKey()method was being called without starting a new transaction, so it was only able to see data as of the snapshot time of the current transaction. Because this snapshot time was prior to the commit time of the corresponding INSERT, findKey() was failing to observe the newly inserted row. In the read committed isolation level, the findKey() call succeeds, because the session can observe data that is committed since the transaction began. To fix this, we simply start a new transaction every time we enter findKey():

         GenericValue keyValue = null;
         try {
+            try {
+                keyValue = TransactionUtil.doNewTransaction(() -> {
+                    return EntityQuery.use(delegator).from("EntityKeyStore").where("keyName", hashedKeyName).queryOne();
+                }, "checking encrypted key", 0, true);
+            } catch (GenericEntityException e) {
+                throw new EntityCryptoException(e);
+            }
         } catch (GenericEntityException e) {
             throw new EntityCryptoException(e);
         }
Java

We loaded the seed and demo data as documented in the OFBiz Technical Setup Guide.

Handle OCC aborts in transaction command pattern

The OFBiz application submits transactions to the database using a command pattern (with the Callable interface), which makes the handling of Aurora DSQL transactions straightforward. Other (non-OFBiz) applications that wrap complete transactions in this way will be similarly manageable, because the transaction boundaries are clearly defined for all cases. All that is required here, at least for the purposes of demonstration, is a retry loop around the transaction execution, which is found in framework/entity/src/main/java/org/apache/ofbiz/entity/transaction/TransactionUtil.java, in the InTransaction.call() method:

        protected InTransaction(Callable<V> callable, String ifErrorMessage, int timeout, boolean printException) {
            this.callable = callable;
            this.ifErrorMessage = ifErrorMessage;
            this.timeout = timeout;
            this.printException = printException;
        }

        public V call() throws GenericEntityException {
+           boolean transactionComplete = false;
+           while (!transactionComplete) {
                boolean tx = TransactionUtil.begin(timeout);
                Throwable transactionAbortCause = null;
                try {
                    try {
                        return callable.call();
                    } catch (Throwable t) {
                        while (t.getCause() != null) {
                            t = t.getCause();
                        }
                        throw t;
                    }
                } catch (Error | RuntimeException e) {
                    transactionAbortCause = e;
                    throw e;
                } catch (Throwable t) {
                    transactionAbortCause = t;
+                   if (t instanceof SQLException && (((SQLException) t).getSQLState().startsWith("OC")
+                           || ((SQLException) t).getSQLState().equals("40001"))) {
+                       if (Debug.verboseOn()) {
+                           Debug.logVerbose("Transaction abort, reason: " + t, module);
+                           Debug.logVerbose("Retrying on OCC conflict", module);
+                       }
+                       // Rollback to recover transaction state and loop again
+                       TransactionUtil.rollback(tx, ifErrorMessage, transactionAbortCause);
+                       continue;
+                   } else {
                        throw new GenericEntityException(t);
+                   }
                } finally {
                    if (transactionAbortCause == null) {
                        TransactionUtil.commit(tx);
+                       transactionComplete = true;
                    } else {
                        if (printException) {
                            Debug.logError(transactionAbortCause, module);
                        }
                        TransactionUtil.rollback(tx, ifErrorMessage, transactionAbortCause);
+                       transactionComplete = true;
+                   }
                }
            }
+           throw new GenericEntityException("Unexpected state in transaction handler");
        }
    }

    public static Connection getCursorConnection(GenericHelperInfo helperInfo, Connection con) {
        Datasource datasourceInfo = EntityConfig.getDatasource(helperInfo.getHelperBaseName());
        if (datasourceInfo == null) {
            Debug.logWarning("Could not find configuration for " + helperInfo.getHelperBaseName() + " datasource.", module);
            return con;
Java

Note: For production applications, we would recommend also implementing logic to provide exponential backoff and jitter, which provides improved system behavior under high contention conditions.

Existing applications that don’t formally model transactions in this manner are likely to require more in-depth analysis to determine what is required to handle OCC aborts correctly. We plan to write a future post on this topic after we learn more about our customers’ experiences and can determine the most common patterns.

Conclusion

In this post, we walked you through the various changes we made to an existing complex application. We made minor modifications to the schema to use supported data types, created new logic to interact properly with the Aurora DSQL authentication mechanisms, implemented commit batching for data load, and made the transaction handler behave correctly for OCC semantics.

Although this proof of concept focused on technical feasibility rather than production readiness, it successfully demonstrated that Aurora DSQL can support complex database applications like OFBiz with reasonable migration effort. The exercise validated that even applications with complex schemas can be adapted to work with Aurora DSQL, including enhanced security and scalability features, through targeted modifications to authentication, transaction handling, and schema design.

The same aspects are the main areas of focus when planning your own migrations to Aurora DSQL:

  1. Use supported datatypes in your schema, including keys
  2. Implement the IAM-based authentication logic required for Aurora DSQL
  3. Ensure transaction sizes are within the Aurora DSQL limits
  4. Implement idempotent and retryable transactions

If you are working on existing applications with Aurora DSQL, we invite you to leave comments in the comment section with your experiences and observations!

About the Author

James Morle is a Principal Engineer at Amazon Web Services and been deeply involved in the design and delivery of Aurora DSQL since the very start. He’s been designing and building very large-scale databases since the early 1990s.