Re: JPA + Postgres = autocommit?

Hello Lachezar,

thank you for your reply.

First of all, let me list some of the things I had tried before I started digging into the source code of the Postgres JDBC driver and Eclipselink.

- switching PGConnectionPoolDataSource.

- both container managed and bean managed transactions.

- REQUIRED and REQUIRES_NEW for container managed transactions.

None of this worked.

Particularly, in my tests with the bean managed transaction I have observed the following behavior: whenever I call flush(), all previous queries are executed, each one seemingly in its own transaction, because the changes appeared in the database before calling tx.commit();

The first that I did when I downloaded the sources of the JDBC driver, was to set breakpoints on getAutoCommit and setAutoCommit and it was not long before I found out that the setter was never called and the getter always returned true.

Now, as I wrote in the previous mail, if I take my entity manager inside a transaction, get the connection out of it, and set the autocommit manually to false, then the transaction works as expected, so there is no problem with the code and the annotations at all. And anyway, I don’t think I could show it to you, without asking my superiors and our clients.

I ruled out the possibility of a bug in the Postgres JDBC driver and in EclipseLink. Only Payara remains. As said, for some reason, setAutoCommit is never called, which is the job of the transaction manager.
I am currently digging into Payara sources and I hope that I fill find some clue there.

Best regards,
Yuriy

Von: Lachezar Dobrev [mailto:l(dot)dobrev(at)gmail(dot)com]
Gesendet: Dienstag, 26. Juli 2016 19:49
An: Davygora, Yuriy <Yuriy(dot)Davygora(at)sulzer(dot)de>
Cc: Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>; List <pgsql-jdbc(at)postgresql(dot)org>
Betreff: Re: [JDBC] JPA + Postgres = autocommit?

We've been using Glassfish 3 and 4 with PostgreSQL with JPA (Eclipse-Link or Hibernate) successfully for years.
When declaring the data source we declare a JDBC Connection Pool, and specify org.postgresql.ds.PGConnectionPoolDataSource as the Datasource Classname.
The persistence.xml specifies transaction-type="JTA" and has <jta-data-source> with some well-known name (that you use to declare the JDBC Resource), no properties commonly.

Also check if your session EJBs are annotated with the @TransactionManagement(TransactionManagementType.CONTAINER) and have proper @TransactionAttribute(TransactionAttributeType.REQUIRED) on the methods that perform changes in the data base. Defaults or not, I've learned not to assume the defaults…
It will not hurt if you show code, although I can understand the NDA/Customer/Corporate code limitations.

2016-07-26 14:51 GMT+03:00 Davygora, Yuriy <Yuriy(dot)Davygora(at)sulzer(dot)de<mailto:Yuriy(dot)Davygora(at)sulzer(dot)de>>:
________________________________

Sulzer GmbH
Geschäftsführende Gesellschafter: Dr. Johann Sulzer, Albert Euba, Thomas Kahabka
Geschäftsführer: Angelika Rudolph, Harald Lothspeich
Sitz und Registergericht: Stuttgart HRB 7608
http://www.sulzer.de

Hello Vladimir,

we do use a Payara data source, in that we configure the connection in Payara’s domain.xml. And no, it does not work, at least in the release that we are using (4.1.1.161). If I, however, in my code do the following:

Connection con = entityManager.unwrap(Connection.class);
con.setAutoCommit(false);

then everything works fine.

This is why I said in my mail, that this might be actually a Payara bug.

Best regards,
Yuriy

Von: Vladimir Sitnikov [mailto:sitnikov(dot)vladimir(at)gmail(dot)com<mailto:sitnikov(dot)vladimir(at)gmail(dot)com>]
Gesendet: Dienstag, 26. Juli 2016 13:22
An: Davygora, Yuriy <Yuriy(dot)Davygora(at)sulzer(dot)de<mailto:Yuriy(dot)Davygora(at)sulzer(dot)de>>
Cc: List <pgsql-jdbc(at)postgresql(dot)org<mailto:pgsql-jdbc(at)postgresql(dot)org>>
Betreff: Re: [JDBC] JPA + Postgres = autocommit?

Yuriy>1. In PgConnection.java, the autocommit is initially set to true in the declaration (private boolean autocommit = true). In the constructor of the PgConnection class, several options are checked (PGProperty), but there is none that has to do with autocommit. Thus, unless, the client explicitely calls setAutoCommit(false), it will remain true.

Just for the reference, the specification (see Connection#setAutoCommit javadoc) states that "By default, new connections are in auto-commit mode"

Yuriy>We are using a JTA data source and the transactions are managed by the JEE container

Why don't you use Payara's data source then?
It should solve the problem as Payara seems to have some setAutoCommit logic: https://github.com/payara/Payara/search?utf8=%E2%9C%93&q=setautocommit

Vladimir
________________________________

Sulzer GmbH
Geschäftsführende Gesellschafter: Dr. Johann Sulzer, Albert Euba, Thomas Kahabka
Geschäftsführer: Angelika Rudolph, Harald Lothspeich
Sitz und Registergericht: Stuttgart HRB 7608
http://www.sulzer.de