This is a multi-part message in MIME format.
--------------010807090808020702070405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit




Dear PostgreSQL gurus,

I got a incorrect or unexpected behavior in concurrent environment.
Luckily, I was able to localize it and create an example:


My setup:

Postgres 8.2 ( with 8.1 the same effect )
Linux ( with Solaris the same effect )
java 1.5, JDBC driver 8.2-504.jdbc3 ( with earlier versions the same effect )


The table:

create table t_jtest (
ikey CHAR(36) primary key,
ivalue integer not null
);


The appication:

Client 1:

autocommit off

loop:
INSERT INTO t_jtest VALUES (?,?)
UPDATE t_jtest SET ivalue=? where ikay=?
commit


Client 2:
autocommit off
"SELECT COUNT(*) FROM t_jtest"

do nothing



The effect is that time, which is needed by Client 1 is growing, unless I add a
commit into client 2.




Is it normal behavior?

Both application attached. To run:

javac *.java

in terminal 1:

java -cp postgresql-8.2-504.jdbc3.jar DbIject


in terminal 2:

ava -cp postgresql-8.2-504.jdbc3.jar Spy



Regards,
Tigran.

__________________________________________________ ______________________
Tigran Mkrtchyan DESY, IT,
tigran.mkrtchyan (AT) desy (DOT) de Notkestrasse 85,
Tel: + 49 40 89983946 Hamburg 22607,
Fax: + 49 40 89984429 Germany.

--------------010807090808020702070405
Content-Type: text/x-java;
name="DbIject.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="DbIject.java"

import java.sql.*;
import java.util.UUID;

/*

create table t_jtest (
ikey CHAR(36) primary key,
ivalue integer not null
);

*/


public class DbIject {

/**
* @param args
*/
public static void main(String[] args) {

try {

Class.forName("org.postgresql.Driver");

Connection newConnection = null;


newConnection = DriverManager.getConnection("jdbcostgresql://localhost/jTest?prepareThreshold=3",
"postgres", "");
newConnection.setAutoCommit(false);


String firstId = null;

for(int i = 0; ; i++) {

PreparedStatement ps = newConnection.prepareStatement("INSERT INTO t_jtest VALUES(?,?)");

String id = UUID.randomUUID().toString();
long now = System.currentTimeMillis();



ps.setString(1, id );
ps.setInt(2, i);

ps.executeUpdate();

ps.close();

if(i == 0 ) {
firstId = id;
}else{

ps = newConnection.prepareStatement("UPDATE t_jtest SET ivalue=? WHERE ikey=?");

ps.setString(2, firstId );
ps.setInt(1, i);

ps.executeUpdate();
ps.close();
}


newConnection.commit();
System.out.println(i + " " + (System.currentTimeMillis() - now) );

}


} catch (Exception e) {
e.printStackTrace();
}

}

}

--------------010807090808020702070405
Content-Type: text/x-java;
name="Spy.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="Spy.java"

import java.sql.*;


public class Spy {
public static void main(String[] args) {

try {

Class.forName("org.postgresql.Driver");

Connection newConnection = null;


newConnection = DriverManager.getConnection("jdbcostgresql://localhost/jTest?prepareThreshold=3",
"postgres", "");
newConnection.setAutoCommit(false);


PreparedStatement ps = newConnection.prepareStatement("SELECT COUNT(*) FROM t_jtest");

ResultSet rs = ps.executeQuery();

rs.close();
ps.close();

// newConnection.commit();
Thread.sleep(3600000);

} catch (Exception e) {
e.printStackTrace();
}

}
}

--------------010807090808020702070405
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--------------010807090808020702070405--

Tigran Mkrtchyan <tigran.mkrtchyan (AT) desy (DOT) de> writes:
This is not a bug, nor even surprising. Since you haven't committed
the second transaction, there are a growing number of
dead-but-not-recyclable versions of the updated row. The active client
has to check each of these versions during its primary key uniqueness
check during each update.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Does it mean that I have to commit after each select statement?

Here what the manual says:

------------
Description

COMMIT commits the current transaction. All changes made by the transaction
become visible to others and are guaranteed to be durable if a crash occurs.
-----------

Does select produces some changes?


Regards,
Tigran.

Tom Lane wrote:

--
__________________________________________________ ______________________
Tigran Mkrtchyan DESY, IT,
tigran.mkrtchyan (AT) desy (DOT) de Notkestrasse 85,
Tel: + 49 40 89983946 Hamburg 22607,
Fax: + 49 40 89984429 Germany.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tigran Mkrtchyan wrote:
START TRANSACTION;
SELECT * from x;

and then leaving the transaction open. The way MVCC works is to write
new data into the heap when you insert/update. It doesn't replace the
data. As this select transaction is open, it may still need to read
data from before the update. So VACUUM can't clean that data out of the
table when it runs and the data still needs to be available to that
transaction.

So basically if you have autocommit off, the transaction stays open.
You can either set autocommit on, or you can COMMIT the transaction.

Russell.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org