Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Philippe EMERIAUD <p(dot)emeriaud(at)ordirope(dot)fr>, List <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR UPDATE
Date: 2015-03-12 15:16:26
Message-ID: CADK3HHJBxqBPeFpoQVJvs=mSbV-nsESZ=9fxyjgA9+X+k7WxNw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-jdbc

Don't think we support 1.5 any more. This is way past EOL for java

About the only way you can compile this is to get an old build.xml from say
9.2 and use it to compile.

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 12 March 2015 at 11:08, Philippe EMERIAUD <p(dot)emeriaud(at)ordirope(dot)fr> wrote:

> Hi Dave,
>
> I'am trying to compile the pgjdbc driver with Ant and java 1.5 (for use
> JDBC 3), but the build failed.
> The problem comes from the library JNA.
>
> [javac]
> C:\Users\PEMERI.PACIFIC\Documents\GitHub\pgjdbc\org\postgresql\sspi\NTDSAPI.java:3:
> cannot access com.sun.jna.LastErrorException
> [javac] bad class file:
> C:\Users\PEMERI.PACIFIC\.m2\repository\net\java\dev\jna\jna\4.1.0\jna-4.1.0.jar(com/sun/jna/LastErrorException.class)
> [javac] class file has wrong version 50.0, should be 49.0
> (See my log in the attachment. for more details)
>
> It would seem that the library jna has be compiled with a jdk greater than
> 1.5.
> Can you confirm this and how can i fix it.
>
> Regards
> Philippe Emeriaud.
>
>
>
>
>
>
> De : Dave Cramer <pg(at)fastcrypt(dot)com>
> A : Philippe EMERIAUD <p(dot)emeriaud(at)ordirope(dot)fr>
> Date : 09/03/2015 12:26
> Objet : Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR
> UPDATE
> Envoyé par : davecramer(at)gmail(dot)com
> ------------------------------
>
>
>
> Philippe,
>
> Well, unfortunately this is not probable (read impossible) to implement in
> postgresql (or any other database besides Oracle).
>
> Candidly any attempt to force this is probably going to end badly; but let
> me know how it goes.
>
> Cheers
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> *http://www.credativ.ca* <http://www.credativ.ca/>
>
> On 9 March 2015 at 04:54, Philippe EMERIAUD <*p(dot)emeriaud(at)ordirope(dot)fr*
> <p(dot)emeriaud(at)ordirope(dot)fr>> wrote:
> Hi Dave,
> Thanks for your reply. My quote comes from the Java 1.5 specification (i
> know this is an old release !) but in the java 1.7 specification you can
> read the javadoc of setAutoCommit(boolean) method, it says :
>
> * "Sets this connection's auto-commit mode to the given state. If a
> connection is in auto-commit mode, then all its SQL statements will be
> executed and committed as individual transactions. Otherwise, its SQL
> statements are grouped into transactions that are terminated by a call to
> either the method commit or the method rollback. By default, new
> connections are in auto-commit mode.*
>
> *The commit occurs when the statement completes. The time when the
> statement completes depends on the type of SQL Statement:*
>
> - *For DML statements, such as Insert, Update or Delete, and DDL
> statements, the statement is complete as soon as it has finished executing.*
> - *For Select statements, the statement is complete when the
> associated result set is closed.*
> - *For CallableStatement objects or for statements that return
> multiple results, the statement is complete when all of the associated
> result sets have been closed, and all update counts and output parameters
> have been retrieved."*
>
>
> There is no easy solution for our problem, 2 ways :
> - We are testing the application with autocommit turned off.
> - We are testing a modified JDBC driver to force lock in AutoCommit on
> mode.
>
> Thanks again.
> Philippe Emeriaud.
>
>
>
> De : Dave Cramer <*pg(at)fastcrypt(dot)com* <pg(at)fastcrypt(dot)com>>
> A : Philippe EMERIAUD <*p(dot)emeriaud(at)ordirope(dot)fr*
> <p(dot)emeriaud(at)ordirope(dot)fr>>
> Cc : "*pgsql-general(at)postgresql(dot)org* <pgsql-general(at)postgresql(dot)org>"
> <*pgsql-general(at)postgresql(dot)org* <pgsql-general(at)postgresql(dot)org>>
> Date : 06/03/2015 18:54
> Objet : Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR
> UPDATE
> Envoyé par : *pgsql-general-owner(at)postgresql(dot)org*
> <pgsql-general-owner(at)postgresql(dot)org>
> ------------------------------
>
>
>
>
> Can you point me to the source of that quote ?
>
> The documentation here
> *http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html*
> <http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html> does
> not specify anything about ResultSet closing, or completion ?
>
> Short version is that the driver does not hold the lock past the return of
> the result set, so unfortunately not.
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> *http://www.credativ.ca* <http://www.credativ.ca/>
>
> On 6 March 2015 at 11:27, Philippe EMERIAUD <*p(dot)emeriaud(at)ordirope(dot)fr*
> <p(dot)emeriaud(at)ordirope(dot)fr>> wrote:
> Hi all,
> We have an application based on DB2 database, We are testing this same
> application on PostgreSQL database. By default we are in autocommit on mode.
> On DB2 (and Oracle), the query SELECT FOR UPDATE locks the row until the
> resultset is closed. On PostgreSQL database (all versions) this same query
> never locks (because we are in autocommit on mode). But this is a bad
> implementation of JDBC specification : "If a connection is in auto-commit
> mode, then all its SQL statements will be executed and committed as
> individual transactions.[...] The commit occurs when the statement
> completes or the next execute occurs, whichever comes first. In the case of
> statements returning a ResultSet object, the statement completes when the
> last row of the ResultSet object has been retrieved or the ResultSet object
> has been closed."
> Is it possible to lock row in autocommit on mode on PostgreSQL database ?
>
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Dara Unglaube 2015-03-12 21:05:20 Rebuild streaming replication
Previous Message Tomas Vondra 2015-03-12 13:15:48 Re: Benchmarking partitioning triggers and rules

Browse pgsql-jdbc by date

  From Date Subject
Next Message Lisa Woodring 2015-03-13 15:13:37 java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
Previous Message Thomas Kellerer 2015-03-06 18:31:36 Re: Problem JDBC, AutoCommit ON and SELECT FOR UPDATE