persistence.xml for multiple persistence units

Question

I'm trying to persist the same entity to both MySQL and Postgres databases (this is primarily to identify any inconsistencies, and work out the details of any issues doing the dual-write -- which I've run into here). The articles I've found have all described solutions that depend on additional frameworks. I'm trying to solve this using Glassfish 4.0 out-of-the-box, JPA 2.1 with EclipseLink 2.5 as the JPA provider. I'm using Eclipse, and realize that the IDE doesn't support configuring multiple persistence units in the persistence.xml file, so I'm writing the XML for that directly.

I was expecting to do something like this in the code (in the same method):

@PersistenceContext(name = "MyAppMySQLPU")
EntityManager emMySQL;
@PersistenceContext(name = "MyAppPostgresPU")
EntityManager emPostgres;
//...etc...
MyThing thing = new MyThing();
//...etc...
emMySQL.persist(thing);
emPostgres.persist(thing);

and use a persistence.xml file containing this:

  <persistence-unit name="MyAppPostgresPU">
    <jta-data-source>jdbc/PostgresPool_test</jta-data-source>
    <class>model.MyThing</class>
  </persistence-unit>

  <persistence-unit name="MyAppMySQLPU">
    <jta-data-source>jdbc/MySQLPool_test</jta-data-source>
    <class>model.MyThing</class>
  </persistence-unit>

When I do this, I get the following error:

SEVERE: Exception while invoking class org.glassfish.persistence.jpa.JPADeployer prepare method
SEVERE: Exception while preparing the app
SEVERE: Exception while preparing the app : Could not resolve a persistence unit corresponding to the persistence-context-ref-name [MyAppPostgresPU] in the scope of the module called [MyApp]. Please verify your application.

But, If I include only one of the <persistence-unit> phrases (doesn't matter which one), the entity is persisted to the associated database -- I just can't figure out how to get it to work with both at the same time (without leveraging persistence functionality in additional frameworks).

Answer 1

Got it working; several things had to be done. It seems a key part of this is that in order to use the multiple databases with the approach I was taking, the connection pool type needs to be set to use distributed transactions. Since this is essentially an experiment, both db persists didn't need to be in the same transaction, but it's not a problem for it to be that way. (This article was helpful in identifying that from the error messages). It was also necessary to change the Postgres parameters as described here, to enable prepared transactions.

---

This got it all going:

(1) In Glassfish:
In the JDBC Connection pools, change the resource type for both db's to javax.sql.XADataSource. Change Datasource Classname for Postgres to org.postgresql.xa.PGXADataSource; change Datasource Classname for MySQL to com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.

(2) In Posgres configuration (postgresql.config):
Enable max_prepared_transactions and set it to be 1 greater than max_connections. (I had to play around with both parameters to find something that didn't blow out all available shared memory; but since this is only an experiment, decreasing number of db connections vs increasing shared memory is OK)

(3) In the code:
Change @PersistenceContext(name="...") to @PersistenceContext(unitName="...")

---

Caveat on this "answer" -- Most of this is new for me, so this may not be the most elegant way to handle this. If anyone can provide a "best practice" to solve this, I would be very interested to know.