Postgres Pro
Downloads
Home   >   mailing lists

Thread: JPA + Postgres = autocommit?

JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  Hello,

 

  I am having a small but severe Problem with PostgreSQL. At our company, we are currently developing a business application in Java EE 7 running on a Payara (Glassfish fork) 4 server (with Eclipselink) and we are using Postgres 9.5 as our database and the postgresql-9.4.1208 JDBC driver. Now, it seems that the connection to the PostgreSQL database is in an autocommit mode. Transactions (both container and bean managed) cannot be rolled back, every single SQL command is executed and commited on flush().

 

  Here is my persistence.xml:

 

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"

             version="2.1">

 

      <persistence-unit name="tsms_pdm" transaction-type="JTA">

            <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

            <jta-data-source>jdbc/PDMDataSource</jta-data-source>

 

            <properties>

                  <property name="eclipselink.jdbc.cache-statements" value="true" />

                  <property name="eclipselink.cache.shared.default" value="false" />

                  <property name="eclipselink.cache.type.default" value="NONE" />

                  <property name="javax.persistence.query.timeout" value="300000" />

            </properties>

      </persistence-unit>

</persistence>

 

 

  And here is an excerpt from the Payara domain.xml:

 

<jdbc-connection-pool datasource-classname="org.postgresql.ds.PGSimpleDataSource" name="PDMDataSource" res-type="javax.sql.DataSource">

                  <property name="TargetServerType" value="any"/>

                  <property name="BinaryTransfer" value="true"/>

                  <property name="UnknownLength" value="2147483647"/>

                  <property name="DisableColumnSanitiser" value="false"/>

                  <property name="UseSpNego" value="false"/>

                  <property name="SspiServiceClass" value="POSTGRES"/>

                  <property name="ProtocolVersion" value="0"/>

                  <property name="LogLevel" value="0"/>

                  <property name="SendBufferSize" value="-1"/>

                  <property name="ReceiveBufferSize" value="-1"/>

                  <property name="LoadBalanceHosts" value="false"/>

                  <property name="ReadOnly" value="false"/>

                  <property name="LogUnclosedConnections" value="false"/>

                  <property name="DatabaseName" value="pdm"/>

                  <property name="GssLib" value="auto"/>

                  <property name="CurrentSchema" value="pdm"/>

                  <property name="PortNumber" value="5432"/>

                  <property name="DefaultRowFetchSize" value="0"/>

                  <property name="User" value="pdm_user"/>

                  <property name="Url" value="jdbc:postgresql://localhost/pdm?"/>

                  <property name="AllowEncodingChanges" value="false"/>

                  <property name="PrepareThreshold" value="5"/>

                  <property name="SocketTimeout" value="0"/>

                  <property name="HostRecheckSeconds" value="10"/>

                  <property name="Password" value="tssb"/>

                  <property name="ConnectTimeout" value="0"/>

                  <property name="PreparedStatementCacheQueries" value="256"/>

                  <property name="Compatible" value="9.4"/>

                  <property name="Ssl" value="false"/>

                  <property name="PreparedStatementCacheSizeMiB" value="5"/>

                  <property name="Loglevel" value="0"/>

                  <property name="LoginTimeout" value="0"/>

                  <property name="ServerName" value="localhost"/>

                  <property name="TcpKeepAlive" value="false"/>

            </jdbc-connection-pool>

            <jdbc-resource pool-name="PDMDataSource" jndi-name="jdbc/PDMDataSource"/>

 

  Am I missing something here? How do I disable the autocommit? Any help would be greatly appreciated.

 

  Best regards,

  Yuriy

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

Re: JPA + Postgres = autocommit?

From
Dennis Gesker
Date:
Hi Yuriy:

Not sure a lot of JPA is covered on this list. But, that being said I do believe that JPA has options for batch writing which I think will speed things up for you. I think this can be done both in the persistence.xml and as hints.
Also, if  the data your are writing is joined to another table you can cache the selected results (using a jpa hint) of the table to which you need to join which can speed things up as well.


Maybe in a simple class we'll call "Constant" and put something like....

    public static final String PERSISTENCEUNIT_PRIMARY = "PrimaryPersistenceUnit";

    @SuppressWarnings("unchecked")
    public static final Map getFastWriteEntityManagerFactoryMap() {
        Map map = new HashMap();

        map.put(PersistenceUnitProperties.LOGGING_LEVEL, SessionLog.FINE_LABEL);

        map.put(PersistenceUnitProperties.TARGET_SERVER, TargetServer.Glassfish);
        map.put(PersistenceUnitProperties.TARGET_DATABASE, TargetDatabase.PostgreSQL);
        map.put(PersistenceUnitProperties.JTA_DATASOURCE, DATASOURCE_PRIMARY);
        map.put(PersistenceUnitProperties.TRANSACTION_TYPE, "JTA");
        map.put(PersistenceUnitProperties.NATIVE_SQL, TRUE_STRING);
        map.put(PersistenceUnitProperties.JDBC_BIND_PARAMETERS, TRUE_STRING);
        map.put(PersistenceUnitProperties.WEAVING, TRUE_STRING);
        map.put(PersistenceUnitProperties.CACHE_STATEMENTS, TRUE_STRING);
        map.put(PersistenceUnitProperties.BATCH_WRITING, BatchWriting.JDBC);
        map.put(PersistenceUnitProperties.BATCH_WRITING_SIZE, "10000");
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_FLUSH_MODE, "commit");
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_CLOSE_ON_COMMIT, TRUE_STRING);
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_PERSIST_ON_COMMIT, FALSE_STRING);

//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_PRODUCT_NAME, TargetDatabase.PostgreSQL);
//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_MAJOR_VERSION, 9);
//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_MINOR_VERSION, 3);
//        map.put(PersistenceUnitProperties.SCHEMA_GENERATION_DATABASE_ACTION, "none");
//        
//        map.put(PersistenceUnitProperties.NATIVE_QUERY_UPPERCASE_COLUMNS, FALSE_STRING);
//        map.put(PersistenceUnitProperties.UPPERCASE_COLUMN_NAMES, FALSE_STRING);
//        map.put(PersistenceUnitProperties.FLUSH_CLEAR_CACHE, FlushClearCache.Drop);
//        map.put(PersistenceUnitProperties.CACHE_SHARED_DEFAULT, FALSE_STRING);
//        map.put("eclipselink.cache.shared.Project", TRUE_STRING);
//        map.put("eclipselink.cache.type.Project", "FULL");
//        map.put("eclipselink.cache.size.Project", 100000);
//        map.put(PersistenceUnitProperties.ORDER_UPDATES, TRUE_STRING);        
        return map;
    }



Then in your EJB use something like...

     // PersistenceUnit Style
        EntityManagerFactory emf;
        EntityManager em;


            emf = Persistence.createEntityManagerFactory(Constant.PERSISTENCEUNIT_PRIMARY);
            em = emf.createEntityManager(Constant.getFastWriteEntityManagerFactoryMap());
            em.joinTransaction();

Call em.persist() as often as you wish but JPA will/should use the properties in the map. The upside of the above is that probably most of your application can just use the standard settings in your persistence.xml but for the occassions where you need to write a bunch of data you can use some custom properties on the fly.

I hope that was helpful and addressed your question.


Cordially,
Dennis

On Mon, Jul 25, 2016 at 12:54 AM, Davygora, Yuriy <Yuriy.Davygora@sulzer.de> wrote:

 

  Hello,

 

  I am having a small but severe Problem with PostgreSQL. At our company, we are currently developing a business application in Java EE 7 running on a Payara (Glassfish fork) 4 server (with Eclipselink) and we are using Postgres 9.5 as our database and the postgresql-9.4.1208 JDBC driver. Now, it seems that the connection to the PostgreSQL database is in an autocommit mode. Transactions (both container and bean managed) cannot be rolled back, every single SQL command is executed and commited on flush().

 

  Here is my persistence.xml:

 

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"

             version="2.1">

 

      <persistence-unit name="tsms_pdm" transaction-type="JTA">

            <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

            <jta-data-source>jdbc/PDMDataSource</jta-data-source>

 

            <properties>

                  <property name="eclipselink.jdbc.cache-statements" value="true" />

                  <property name="eclipselink.cache.shared.default" value="false" />

                  <property name="eclipselink.cache.type.default" value="NONE" />

                  <property name="javax.persistence.query.timeout" value="300000" />

            </properties>

      </persistence-unit>

</persistence>

 

 

  And here is an excerpt from the Payara domain.xml:

 

<jdbc-connection-pool datasource-classname="org.postgresql.ds.PGSimpleDataSource" name="PDMDataSource" res-type="javax.sql.DataSource">

                  <property name="TargetServerType" value="any"/>

                  <property name="BinaryTransfer" value="true"/>

                  <property name="UnknownLength" value="2147483647"/>

                  <property name="DisableColumnSanitiser" value="false"/>

                  <property name="UseSpNego" value="false"/>

                  <property name="SspiServiceClass" value="POSTGRES"/>

                  <property name="ProtocolVersion" value="0"/>

                  <property name="LogLevel" value="0"/>

                  <property name="SendBufferSize" value="-1"/>

                  <property name="ReceiveBufferSize" value="-1"/>

                  <property name="LoadBalanceHosts" value="false"/>

                  <property name="ReadOnly" value="false"/>

                  <property name="LogUnclosedConnections" value="false"/>

                  <property name="DatabaseName" value="pdm"/>

                  <property name="GssLib" value="auto"/>

                  <property name="CurrentSchema" value="pdm"/>

                  <property name="PortNumber" value="5432"/>

                  <property name="DefaultRowFetchSize" value="0"/>

                  <property name="User" value="pdm_user"/>

                  <property name="Url" value="jdbc:postgresql://localhost/pdm?"/>

                  <property name="AllowEncodingChanges" value="false"/>

                  <property name="PrepareThreshold" value="5"/>

                  <property name="SocketTimeout" value="0"/>

                  <property name="HostRecheckSeconds" value="10"/>

                  <property name="Password" value="tssb"/>

                  <property name="ConnectTimeout" value="0"/>

                  <property name="PreparedStatementCacheQueries" value="256"/>

                  <property name="Compatible" value="9.4"/>

                  <property name="Ssl" value="false"/>

                  <property name="PreparedStatementCacheSizeMiB" value="5"/>

                  <property name="Loglevel" value="0"/>

                  <property name="LoginTimeout" value="0"/>

                  <property name="ServerName" value="localhost"/>

                  <property name="TcpKeepAlive" value="false"/>

            </jdbc-connection-pool>

            <jdbc-resource pool-name="PDMDataSource" jndi-name="jdbc/PDMDataSource"/>

 

  Am I missing something here? How do I disable the autocommit? Any help would be greatly appreciated.

 

  Best regards,

  Yuriy

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




--
LinkedIn Wordpress Facebook Twitter Family Home Page Public Encryption Key dennis@gesker.com
“Be without fear in the face of your enemies. Be brave and upright that God may love thee. Speak the truth always, even if it leads to your death. Safeguard the helpless and do no wrong – that is your oath.” -The Knight’s Oath (Kingdom of Heaven)

Re: JPA + Postgres = autocommit?

From
Dennis Gesker
Date:
Hi Yuriy:

Not sure a lot of JPA is covered on this list. But, that being said I do believe that JPA has options for batch writing which I think will speed things up for you. I think this can be done both in the persistence.xml and as hints.
Also, if  the data your are writing is joined to another table you can cache the selected results (using a jpa hint) of the table to which you need to join which can speed things up as well.


Maybe in a simple class we'll call "Constant" and put something like....

    public static final String PERSISTENCEUNIT_PRIMARY = "PrimaryPersistenceUnit";

    @SuppressWarnings("unchecked")
    public static final Map getFastWriteEntityManagerFactoryMap() {
        Map map = new HashMap();

        map.put(PersistenceUnitProperties.LOGGING_LEVEL, SessionLog.FINE_LABEL);

        map.put(PersistenceUnitProperties.TARGET_SERVER, TargetServer.Glassfish);
        map.put(PersistenceUnitProperties.TARGET_DATABASE, TargetDatabase.PostgreSQL);
        map.put(PersistenceUnitProperties.JTA_DATASOURCE, DATASOURCE_PRIMARY);
        map.put(PersistenceUnitProperties.TRANSACTION_TYPE, "JTA");
        map.put(PersistenceUnitProperties.NATIVE_SQL, TRUE_STRING);
        map.put(PersistenceUnitProperties.JDBC_BIND_PARAMETERS, TRUE_STRING);
        map.put(PersistenceUnitProperties.WEAVING, TRUE_STRING);
        map.put(PersistenceUnitProperties.CACHE_STATEMENTS, TRUE_STRING);
        map.put(PersistenceUnitProperties.BATCH_WRITING, BatchWriting.JDBC);
        map.put(PersistenceUnitProperties.BATCH_WRITING_SIZE, "10000");
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_FLUSH_MODE, "commit");
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_CLOSE_ON_COMMIT, TRUE_STRING);
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_PERSIST_ON_COMMIT, FALSE_STRING);

//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_PRODUCT_NAME, TargetDatabase.PostgreSQL);
//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_MAJOR_VERSION, 9);
//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_MINOR_VERSION, 3);
//        map.put(PersistenceUnitProperties.SCHEMA_GENERATION_DATABASE_ACTION, "none");
//        
//        map.put(PersistenceUnitProperties.NATIVE_QUERY_UPPERCASE_COLUMNS, FALSE_STRING);
//        map.put(PersistenceUnitProperties.UPPERCASE_COLUMN_NAMES, FALSE_STRING);
//        map.put(PersistenceUnitProperties.FLUSH_CLEAR_CACHE, FlushClearCache.Drop);
//        map.put(PersistenceUnitProperties.CACHE_SHARED_DEFAULT, FALSE_STRING);
//        map.put("eclipselink.cache.shared.Project", TRUE_STRING);
//        map.put("eclipselink.cache.type.Project", "FULL");
//        map.put("eclipselink.cache.size.Project", 100000);
//        map.put(PersistenceUnitProperties.ORDER_UPDATES, TRUE_STRING);        
        return map;
    }



Then in your EJB use something like...

     // PersistenceUnit Style
        EntityManagerFactory emf;
        EntityManager em;


            emf = Persistence.createEntityManagerFactory(Constant.PERSISTENCEUNIT_PRIMARY);
            em = emf.createEntityManager(Constant.getFastWriteEntityManagerFactoryMap());
            em.joinTransaction();

Call em.persist() as often as you wish but JPA will/should use the properties in the map. The upside of the above is that probably most of your application can just use the standard settings in your persistence.xml but for the occassions where you need to write a bunch of data you can use some custom properties on the fly.

I hope that was helpful and addressed your question.


Cordially,
Dennis

On Mon, Jul 25, 2016 at 12:54 AM, Davygora, Yuriy <Yuriy.Davygora@sulzer.de> wrote:

 

  Hello,

 

  I am having a small but severe Problem with PostgreSQL. At our company, we are currently developing a business application in Java EE 7 running on a Payara (Glassfish fork) 4 server (with Eclipselink) and we are using Postgres 9.5 as our database and the postgresql-9.4.1208 JDBC driver. Now, it seems that the connection to the PostgreSQL database is in an autocommit mode. Transactions (both container and bean managed) cannot be rolled back, every single SQL command is executed and commited on flush().

 

  Here is my persistence.xml:

 

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"

             version="2.1">

 

      <persistence-unit name="tsms_pdm" transaction-type="JTA">

            <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

            <jta-data-source>jdbc/PDMDataSource</jta-data-source>

 

            <properties>

                  <property name="eclipselink.jdbc.cache-statements" value="true" />

                  <property name="eclipselink.cache.shared.default" value="false" />

                  <property name="eclipselink.cache.type.default" value="NONE" />

                  <property name="javax.persistence.query.timeout" value="300000" />

            </properties>

      </persistence-unit>

</persistence>

 

 

  And here is an excerpt from the Payara domain.xml:

 

<jdbc-connection-pool datasource-classname="org.postgresql.ds.PGSimpleDataSource" name="PDMDataSource" res-type="javax.sql.DataSource">

                  <property name="TargetServerType" value="any"/>

                  <property name="BinaryTransfer" value="true"/>

                  <property name="UnknownLength" value="2147483647"/>

                  <property name="DisableColumnSanitiser" value="false"/>

                  <property name="UseSpNego" value="false"/>

                  <property name="SspiServiceClass" value="POSTGRES"/>

                  <property name="ProtocolVersion" value="0"/>

                  <property name="LogLevel" value="0"/>

                  <property name="SendBufferSize" value="-1"/>

                  <property name="ReceiveBufferSize" value="-1"/>

                  <property name="LoadBalanceHosts" value="false"/>

                  <property name="ReadOnly" value="false"/>

                  <property name="LogUnclosedConnections" value="false"/>

                  <property name="DatabaseName" value="pdm"/>

                  <property name="GssLib" value="auto"/>

                  <property name="CurrentSchema" value="pdm"/>

                  <property name="PortNumber" value="5432"/>

                  <property name="DefaultRowFetchSize" value="0"/>

                  <property name="User" value="pdm_user"/>

                  <property name="Url" value="jdbc:postgresql://localhost/pdm?"/>

                  <property name="AllowEncodingChanges" value="false"/>

                  <property name="PrepareThreshold" value="5"/>

                  <property name="SocketTimeout" value="0"/>

                  <property name="HostRecheckSeconds" value="10"/>

                  <property name="Password" value="tssb"/>

                  <property name="ConnectTimeout" value="0"/>

                  <property name="PreparedStatementCacheQueries" value="256"/>

                  <property name="Compatible" value="9.4"/>

                  <property name="Ssl" value="false"/>

                  <property name="PreparedStatementCacheSizeMiB" value="5"/>

                  <property name="Loglevel" value="0"/>

                  <property name="LoginTimeout" value="0"/>

                  <property name="ServerName" value="localhost"/>

                  <property name="TcpKeepAlive" value="false"/>

            </jdbc-connection-pool>

            <jdbc-resource pool-name="PDMDataSource" jndi-name="jdbc/PDMDataSource"/>

 

  Am I missing something here? How do I disable the autocommit? Any help would be greatly appreciated.

 

  Best regards,

  Yuriy

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




--
LinkedIn Wordpress Facebook Twitter Family Home Page Public Encryption Key dennis@gesker.com
“Be without fear in the face of your enemies. Be brave and upright that God may love thee. Speak the truth always, even if it leads to your death. Safeguard the helpless and do no wrong – that is your oath.” -The Knight’s Oath (Kingdom of Heaven)

Re: JPA + Postgres = autocommit?

From
rob stone
Date:
On Mon, 2016-07-25 at 06:54 +0000, Davygora, Yuriy wrote:
>  
>   Hello,
>  
>   I am having a small but severe Problem with PostgreSQL. At our
> company, we are currently developing a business application in Java
> EE 7 running on a Payara (Glassfish fork) 4 server (with Eclipselink)
> and we are using Postgres 9.5 as our database and the postgresql-
> 9.4.1208 JDBC driver. Now, it seems that the connection to the
> PostgreSQL database is in an autocommit mode. Transactions (both
> container and bean managed) cannot be rolled back, every single SQL
> command is executed and commited on flush().
>  
>  

Hello,

I know nothing about Payara, etc. but the "traditional" way of handling
this in an application is to:-

BEGIN;

Do your inserts/updates etc.

COMMIT; or if you caught any errors during the inserting/updating, then

ROLLBACK;

See

https://www.postgresql.org/docs/9.6/static/app-psql.html#APP-PSQL-VARIA
BLES

for more information about this setting in psql.



HTH,

Rob

Re: JPA + Postgres = autocommit?

From
John R Pierce
Date:
On 7/25/2016 2:48 PM, rob stone wrote:
> I know nothing about Payara, etc. but the "traditional" way of handling
> this in an application is to:-
>
> BEGIN;
>
> Do your inserts/updates etc.
>
> COMMIT; or if you caught any errors during the inserting/updating, then
>
> ROLLBACK;

Not in Java/JDBC applications.   Rather,  they have the concept of
autocommit on/off, you configure it on a per connection basis.
commit() and rollback() are API calls to the java database connection
object.

the real question here is how you convince the OP's stack of tools to
disable autocommit, and that I can't help with, but I'm sure its some
field in one of those XML files


--
john r pierce, recycling bits in santa cruz

Re: JPA + Postgres = autocommit?

From
rob stone
Date:
On Mon, 2016-07-25 at 14:53 -0700, John R Pierce wrote:
> On 7/25/2016 2:48 PM, rob stone wrote:
> > I know nothing about Payara, etc. but the "traditional" way of
> > handling
> > this in an application is to:-
> >
> > BEGIN;
> >
> > Do your inserts/updates etc.
> >
> > COMMIT; or if you caught any errors during the inserting/updating,
> > then
> >
> > ROLLBACK;
>
> Not in Java/JDBC applications.   Rather,  they have the concept of 
> autocommit on/off, you configure it on a per connection basis.    
> commit() and rollback() are API calls to the java database
> connection 
> object.
>

True, however issuing a BEGIN turns autocommit off.

> the real question here is how you convince the OP's stack of tools
> to 
> disable autocommit, and that I can't help with, but I'm sure its
> some 
> field in one of those XML files
>
>

Yes. I'd experiment with <property name="AutoCommit" value="false" />
or maybe value="off" and see if it makes a difference.



> -- 
> john r pierce, recycling bits in santa cruz
>
>
>

Re: JPA + Postgres = autocommit?

From
Dave Cramer
Date:



On 25 July 2016 at 18:48, rob stone <floriparob@gmail.com> wrote:
On Mon, 2016-07-25 at 14:53 -0700, John R Pierce wrote:
> On 7/25/2016 2:48 PM, rob stone wrote:
> > I know nothing about Payara, etc. but the "traditional" way of
> > handling
> > this in an application is to:-
> >
> > BEGIN;
> >
> > Do your inserts/updates etc.
> >
> > COMMIT; or if you caught any errors during the inserting/updating,
> > then
> >
> > ROLLBACK;
>
> Not in Java/JDBC applications.   Rather,  they have the concept of 
> autocommit on/off, you configure it on a per connection basis.    
> commit() and rollback() are API calls to the java database
> connection 
> object.
>

True, however issuing a BEGIN turns autocommit off.

Please don't do that in JDBC. This will not have the results you expect. The driver needs to know if it is in autocommit mode or not

Yes. I'd experiment with <property name="AutoCommit" value="false" />
or maybe value="off" and see if it makes a difference.


Yes

Re: JPA + Postgres = autocommit?

From
rob stone
Date:
On Mon, 2016-07-25 at 06:54 +0000, Davygora, Yuriy wrote:
>  
>   Hello,
>  
>   I am having a small but severe Problem with PostgreSQL. At our
> company, we are currently developing a business application in Java
> EE 7 running on a Payara (Glassfish fork) 4 server (with Eclipselink)
> and we are using Postgres 9.5 as our database and the postgresql-
> 9.4.1208 JDBC driver. Now, it seems that the connection to the
> PostgreSQL database is in an autocommit mode. Transactions (both
> container and bean managed) cannot be rolled back, every single SQL
> command is executed and commited on flush().
>  
>  

Hello,

I know nothing about Payara, etc. but the "traditional" way of handling
this in an application is to:-

BEGIN;

Do your inserts/updates etc.

COMMIT; or if you caught any errors during the inserting/updating, then

ROLLBACK;

See

https://www.postgresql.org/docs/9.6/static/app-psql.html#APP-PSQL-VARIA
BLES

for more information about this setting in psql.



HTH,

Rob

Re: JPA + Postgres = autocommit?

From
John R Pierce
Date:
On 7/25/2016 2:48 PM, rob stone wrote:
> I know nothing about Payara, etc. but the "traditional" way of handling
> this in an application is to:-
>
> BEGIN;
>
> Do your inserts/updates etc.
>
> COMMIT; or if you caught any errors during the inserting/updating, then
>
> ROLLBACK;

Not in Java/JDBC applications.   Rather,  they have the concept of
autocommit on/off, you configure it on a per connection basis.
commit() and rollback() are API calls to the java database connection
object.

the real question here is how you convince the OP's stack of tools to
disable autocommit, and that I can't help with, but I'm sure its some
field in one of those XML files


--
john r pierce, recycling bits in santa cruz

Re: JPA + Postgres = autocommit?

From
rob stone
Date:
On Mon, 2016-07-25 at 14:53 -0700, John R Pierce wrote:
> On 7/25/2016 2:48 PM, rob stone wrote:
> > I know nothing about Payara, etc. but the "traditional" way of
> > handling
> > this in an application is to:-
> >
> > BEGIN;
> >
> > Do your inserts/updates etc.
> >
> > COMMIT; or if you caught any errors during the inserting/updating,
> > then
> >
> > ROLLBACK;
>
> Not in Java/JDBC applications.   Rather,  they have the concept of 
> autocommit on/off, you configure it on a per connection basis.    
> commit() and rollback() are API calls to the java database
> connection 
> object.
>

True, however issuing a BEGIN turns autocommit off.

> the real question here is how you convince the OP's stack of tools
> to 
> disable autocommit, and that I can't help with, but I'm sure its
> some 
> field in one of those XML files
>
>

Yes. I'd experiment with <property name="AutoCommit" value="false" />
or maybe value="off" and see if it makes a difference.



> -- 
> john r pierce, recycling bits in santa cruz
>
>
>

Re: JPA + Postgres = autocommit?

From
Dave Cramer
Date:



On 25 July 2016 at 18:48, rob stone <floriparob@gmail.com> wrote:
On Mon, 2016-07-25 at 14:53 -0700, John R Pierce wrote:
> On 7/25/2016 2:48 PM, rob stone wrote:
> > I know nothing about Payara, etc. but the "traditional" way of
> > handling
> > this in an application is to:-
> >
> > BEGIN;
> >
> > Do your inserts/updates etc.
> >
> > COMMIT; or if you caught any errors during the inserting/updating,
> > then
> >
> > ROLLBACK;
>
> Not in Java/JDBC applications.   Rather,  they have the concept of 
> autocommit on/off, you configure it on a per connection basis.    
> commit() and rollback() are API calls to the java database
> connection 
> object.
>

True, however issuing a BEGIN turns autocommit off.

Please don't do that in JDBC. This will not have the results you expect. The driver needs to know if it is in autocommit mode or not

Yes. I'd experiment with <property name="AutoCommit" value="false" />
or maybe value="off" and see if it makes a difference.


Yes

Re: JPA + Postgres = autocommit?

From
Radoslav Petrov
Date:
On 25.07.2016 09:54, Davygora, Yuriy wrote:
 And here is an excerpt from the Payara domain.xml:

<jdbc-connection-pool datasource-classname="org.postgresql.ds.PGSimpleDataSource" name="PDMDataSource" res-type="javax.sql.DataSource"> Am I missing something here? How do I disable the autocommit? Any help would be greatly appreciated.
IMO this part in bold is your problem i.e. the setting of DataSource class name. I've checked the documentation for PGSimpleDataSource (https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/ds/PGSimpleDataSource.html) and there is the following explanation:
Simple DataSource which does not perform connection pooling.

Please note that I don't have experience with Payara/Eclipselink. Maybe you should use some of the other DataSource implementations in PostgreJDBC or configure dedicated connection pool like c3p0.
-- 
Поздрави,       | Best regards,
Радослав Петров | Radoslav Petrov

Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  Hello,

 

  thank you all for your replies.

 

  I did quite a lot of digging in the source code or the postgres jdbc-driver and eclipselink and found out the following:

 

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.

2.       Eclipselink calls the setAutoCommit method of a connection to turn it off and on, but only in the case when transactions are managed by Eclipselink itself. We are using a JTA data source and the transactions are managed by the JEE container, i.e. by the Payara server, which apparently never calls the setAutoCommit mehod. So it might actually be a bug in Payara: if Eclipselink manages the autocommit property, so should Payara too, I guess.

3.       In Eclipselink itself there is no option that could be read from the persistence.xml file and cause Connection.setAutoCommit(false).

4.       The connection properties in the Payara domain.xml file are routed via Eclipselink direcly to the Postgres Driver: the above mentioned PGProperty names. As already mentioned, there is no autocommit option there either.

 

  We have decided to patch the Postgres JDBC driver at least for our local testing environment, in order to include an “autocommit” option in the PGProperty and process it in the PgConnection constructor. Later, when we deploy our product to the servers of our client, if we have similar problems, we will contact the corresponding support.

 

Best regards,

Yuriy

 

 

Von: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] Im Auftrag von Dave Cramer
Gesendet: Dienstag, 26. Juli 2016 01:06
An: rob stone <floriparob@gmail.com>
Cc: John R Pierce <pierce@hogranch.com>; List <pgsql-jdbc@postgresql.org>
Betreff: Re: [JDBC] JPA + Postgres = autocommit?

 

 

 

On 25 July 2016 at 18:48, rob stone <floriparob@gmail.com> wrote:

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

On Mon, 2016-07-25 at 14:53 -0700, John R Pierce wrote:
> On 7/25/2016 2:48 PM, rob stone wrote:
> > I know nothing about Payara, etc. but the "traditional" way of
> > handling
> > this in an application is to:-
> >
> > BEGIN;
> >
> > Do your inserts/updates etc.
> >
> > COMMIT; or if you caught any errors during the inserting/updating,
> > then
> >
> > ROLLBACK;
>
> Not in Java/JDBC applications.   Rather,  they have the concept of 
> autocommit on/off, you configure it on a per connection basis.    
> commit() and rollback() are API calls to the java database
> connection 
> object.
>

True, however issuing a BEGIN turns autocommit off.

 

Please don't do that in JDBC. This will not have the results you expect. The driver needs to know if it is in autocommit mode or not


Yes. I'd experiment with <property name="AutoCommit" value="false" />
or maybe value="off" and see if it makes a difference.

 

 

Yes

 

Re: JPA + Postgres = autocommit?

From
Vladimir Sitnikov
Date:
Radoslav>Maybe you should use some of the other DataSource implementations in PostgreJDBC or configure dedicated connection pool like c3p0.

The advice to use "decent connection pooler" is good (I would recommend Hikari-CP), however it should not matter for "autocomittment" (how would you spell that? :)

Yuriy, can you share test project that reproduces the issue?
You might have missed @Transacted or something like that in your application code.

What is the exact error you are running into?

As far as I understand, the following should happen:
1) DataSource produces connection with any autocommit setting
2) JPA should call con.setAutocommit(false) when it senses the "start of transaction" at the application side

I bet there is no transaction created/enabled at application side.

Vladimir

Re: JPA + Postgres = autocommit?

From
Vladimir Sitnikov
Date:

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

Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  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.vladimir@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 13:22
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: List <pgsql-jdbc@postgresql.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

Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  Hello,

 

  thank you all for your replies.

 

  I did quite a lot of digging in the source code or the postgres jdbc-driver and eclipselink and found out the following:

 

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.

2.       Eclipselink calls the setAutoCommit method of a connection to turn it off and on, but only in the case when transactions are managed by Eclipselink itself. We are using a JTA data source and the transactions are managed by the JEE container, i.e. by the Payara server, which apparently never calls the setAutoCommit mehod. So it might actually be a bug in Payara: if Eclipselink manages the autocommit property, so should Payara too, I guess.

3.       In Eclipselink itself there is no option that could be read from the persistence.xml file and cause Connection.setAutoCommit(false).

4.       The connection properties in the Payara domain.xml file are routed via Eclipselink direcly to the Postgres Driver: the above mentioned PGProperty names. As already mentioned, there is no autocommit option there either.

 

  We have decided to patch the Postgres JDBC driver at least for our local testing environment, in order to include an “autocommit” option in the PGProperty and process it in the PgConnection constructor. Later, when we deploy our product to the servers of our client, if we have similar problems, we will contact the corresponding support.

 

Best regards,

Yuriy

 

 

Von: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] Im Auftrag von Dave Cramer
Gesendet: Dienstag, 26. Juli 2016 01:06
An: rob stone <floriparob@gmail.com>
Cc: John R Pierce <pierce@hogranch.com>; List <pgsql-jdbc@postgresql.org>
Betreff: Re: [JDBC] JPA + Postgres = autocommit?

 

 

 

On 25 July 2016 at 18:48, rob stone <floriparob@gmail.com> wrote:

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

On Mon, 2016-07-25 at 14:53 -0700, John R Pierce wrote:
> On 7/25/2016 2:48 PM, rob stone wrote:
> > I know nothing about Payara, etc. but the "traditional" way of
> > handling
> > this in an application is to:-
> >
> > BEGIN;
> >
> > Do your inserts/updates etc.
> >
> > COMMIT; or if you caught any errors during the inserting/updating,
> > then
> >
> > ROLLBACK;
>
> Not in Java/JDBC applications.   Rather,  they have the concept of 
> autocommit on/off, you configure it on a per connection basis.    
> commit() and rollback() are API calls to the java database
> connection 
> object.
>

True, however issuing a BEGIN turns autocommit off.

 

Please don't do that in JDBC. This will not have the results you expect. The driver needs to know if it is in autocommit mode or not


Yes. I'd experiment with <property name="AutoCommit" value="false" />
or maybe value="off" and see if it makes a difference.

 

 

Yes

 

Re: JPA + Postgres = autocommit?

From
Radoslav Petrov
Date:
On 25.07.2016 09:54, Davygora, Yuriy wrote:
 And here is an excerpt from the Payara domain.xml:

<jdbc-connection-pool datasource-classname="org.postgresql.ds.PGSimpleDataSource" name="PDMDataSource" res-type="javax.sql.DataSource"> Am I missing something here? How do I disable the autocommit? Any help would be greatly appreciated.
IMO this part in bold is your problem i.e. the setting of DataSource class name. I've checked the documentation for PGSimpleDataSource (https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/ds/PGSimpleDataSource.html) and there is the following explanation:
Simple DataSource which does not perform connection pooling.

Please note that I don't have experience with Payara/Eclipselink. Maybe you should use some of the other DataSource implementations in PostgreJDBC or configure dedicated connection pool like c3p0.
-- 
Поздрави,       | Best regards,
Радослав Петров | Radoslav Petrov

Re: JPA + Postgres = autocommit?

From
Vladimir Sitnikov
Date:
Radoslav>Maybe you should use some of the other DataSource implementations in PostgreJDBC or configure dedicated connection pool like c3p0.

The advice to use "decent connection pooler" is good (I would recommend Hikari-CP), however it should not matter for "autocomittment" (how would you spell that? :)

Yuriy, can you share test project that reproduces the issue?
You might have missed @Transacted or something like that in your application code.

What is the exact error you are running into?

As far as I understand, the following should happen:
1) DataSource produces connection with any autocommit setting
2) JPA should call con.setAutocommit(false) when it senses the "start of transaction" at the application side

I bet there is no transaction created/enabled at application side.

Vladimir

Re: JPA + Postgres = autocommit?

From
Vladimir Sitnikov
Date:

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

Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  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.vladimir@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 13:22
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: List <pgsql-jdbc@postgresql.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

Re: JPA + Postgres = autocommit?

From
Lachezar Dobrev
Date:
  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.Davygora@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.vladimir@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 13:22
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: List <pgsql-jdbc@postgresql.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


Re: JPA + Postgres = autocommit?

From
Lachezar Dobrev
Date:
  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.Davygora@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.vladimir@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 13:22
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: List <pgsql-jdbc@postgresql.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


Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  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.dobrev@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 19:49
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; List <pgsql-jdbc@postgresql.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.Davygora@sulzer.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.vladimir@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 13:22
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: List <pgsql-jdbc@postgresql.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

 

 

Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  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.dobrev@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 19:49
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; List <pgsql-jdbc@postgresql.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.Davygora@sulzer.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.vladimir@gmail.com]
Gesendet: Dienstag, 26. Juli 2016 13:22
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: List <pgsql-jdbc@postgresql.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

 

 

Re: JPA + Postgres = autocommit?

From
Craig Ringer
Date:
On 27 July 2016 at 16:50, Davygora, Yuriy <Yuriy.Davygora@sulzer.de> wrote:

 

  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.



IIRC with Glassfish back in the day I used org.postgresql.Driver and let Glassfish's connection pooler wrap its own DataSource around it.


 

  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.


Yeah, that's pretty suspect.


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: JPA + Postgres = autocommit?

From
Craig Ringer
Date:
On 27 July 2016 at 16:50, Davygora, Yuriy <Yuriy.Davygora@sulzer.de> wrote:

 

  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.



IIRC with Glassfish back in the day I used org.postgresql.Driver and let Glassfish's connection pooler wrap its own DataSource around it.


 

  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.


Yeah, that's pretty suspect.


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  Hello,

 

  once again I would like to thank everyone who responded. Most of your hints spawned either some source code investigation or reading quite a lot of manuals or both. At the end, I did find the mistake. As should have been expected, it was not a bug in any of the software.

 

  The bug was in my domain.xml, in the bit that I never shared with you. The Datasource configuration was OK, but apart from that one must also reference the jndi names in the server configuration. Which I even did, however with an error. All my jdni-names had a prefix “jdbc/” which I omitted in the <resource-ref> tags by mistake. After I typed in the correct jndi names and restarted the server, the transaction management immediately started working.

 

  Sorry for having bothered you all and thanks again!

 

  Best regards,

  Yuriy

 

 

Von: Craig Ringer [mailto:craig@2ndquadrant.com]
Gesendet: Freitag, 29. Juli 2016 05:45
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: Lachezar Dobrev <l.dobrev@gmail.com>; Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; List <pgsql-jdbc@postgresql.org>
Betreff: Re: [JDBC] JPA + Postgres = autocommit?

 

On 27 July 2016 at 16:50, Davygora, Yuriy <Yuriy.Davygora@sulzer.de> wrote:

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

 

  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.

 

 

IIRC with Glassfish back in the day I used org.postgresql.Driver and let Glassfish's connection pooler wrap its own DataSource around it.

 

 

 

  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.

 

Yeah, that's pretty suspect.


 

--

 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: JPA + Postgres = autocommit?

From
"Davygora, Yuriy"
Date:

 

  Hello,

 

  once again I would like to thank everyone who responded. Most of your hints spawned either some source code investigation or reading quite a lot of manuals or both. At the end, I did find the mistake. As should have been expected, it was not a bug in any of the software.

 

  The bug was in my domain.xml, in the bit that I never shared with you. The Datasource configuration was OK, but apart from that one must also reference the jndi names in the server configuration. Which I even did, however with an error. All my jdni-names had a prefix “jdbc/” which I omitted in the <resource-ref> tags by mistake. After I typed in the correct jndi names and restarted the server, the transaction management immediately started working.

 

  Sorry for having bothered you all and thanks again!

 

  Best regards,

  Yuriy

 

 

Von: Craig Ringer [mailto:craig@2ndquadrant.com]
Gesendet: Freitag, 29. Juli 2016 05:45
An: Davygora, Yuriy <Yuriy.Davygora@sulzer.de>
Cc: Lachezar Dobrev <l.dobrev@gmail.com>; Vladimir Sitnikov <sitnikov.vladimir@gmail.com>; List <pgsql-jdbc@postgresql.org>
Betreff: Re: [JDBC] JPA + Postgres = autocommit?

 

On 27 July 2016 at 16:50, Davygora, Yuriy <Yuriy.Davygora@sulzer.de> wrote:

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

 

  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.

 

 

IIRC with Glassfish back in the day I used org.postgresql.Driver and let Glassfish's connection pooler wrap its own DataSource around it.

 

 

 

  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.

 

Yeah, that's pretty suspect.


 

--

 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services