Postgresql ACID bug? - Mailing list pgsql-bugs

Introduction
I wrote small java program which performs some selects in one transaction (and thread) and one delete in another transaction and thread on the same table holding one record initially.

Environment
OS: Centos 5.5 kernel 2.6.18-194.32.1.el5
Postgresql: 9.0.4
jdbc driver: 9.0-801 JDBC 4

Results and expectations:
The program starts with a select (SELECT transaction) and prints one record ('id' and 'address' from RelationAddresses table)
The program now deletes the record in another transaction (DELETE TRANSACTION)
The program now selects and prints the records from the SELECT transaction. (result as expected one record printed)
The DELETE transaction commits.
The program now selects and prints the records from the SELECT transaction. (0 records printed, we expected one record since we are still within the SELECT transaction which started while this record was still available)

[code]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class TransactionTest {
    /**/
    private static final String driver = "org.postgresql.Driver";
    private static final String connection = "jdbc:postgresql://192.168.0.1/sampledb";
    private static final String username = "postgres";
    private static final String password = "secret";
    private static final String escapeChar = "\"";
    /**/
   
    /*
    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String connection = "jdbc:mysql://192.168.0.1/sampledb";
    private static final String username = "root";
    private static final String password = "secret";
    private static final String escapeChar = "`";
    /**/
   
    private TransactionTest(){
       
        Select select = new Select();
        select.start();
       
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) { /*IGNORE*/ }
       
        Delete delete = new Delete();
        delete.start();
    }
   
    public static void main(String[] args) {
        new TransactionTest();
    }
   
    Object block = new Object();
    class Select extends Thread{
        @Override
        public void run(){
            try {
                Class.forName(driver).newInstance();
                Connection conn = DriverManager.getConnection(connection, username, password);
                conn.setAutoCommit(false);
               
                System.out.println("------------");
                System.out.println("SELECT TRANSACTION: List all addresses:");
                String query = "SELECT " + escapeChar +  "relationAddressesId" + escapeChar +  ", " + escapeChar +  "address" + escapeChar +  " FROM " + escapeChar +  "RelationAddresses" + escapeChar +  "";
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(query);
                while(rs.next()){
                    System.out.println("relationAddressesId: " + rs.getInt(1) + " - address: " + rs.getString(2));
                }
                rs.close(); stmt.close();
                System.out.println("------------");
               
                synchronized(block){
                    try {
                        block.wait();
                    } catch (InterruptedException e) { /*IGNORE*/}
                }
               
                System.out.println("------------");
                System.out.println("SELECT TRANSACTION: List all addresses before DELETE TRANSACTION commit():");
                query = "SELECT " + escapeChar +  "relationAddressesId" + escapeChar +  ", " + escapeChar +  "address" + escapeChar +  " FROM " + escapeChar +  "RelationAddresses" + escapeChar +  "";
                stmt = conn.createStatement();
                rs = stmt.executeQuery(query);
                while(rs.next()){
                    System.out.println("relationAddressesId: " + rs.getInt(1) + " - address: " + rs.getString(2));
                }
                rs.close(); stmt.close();
                System.out.println("------------");
               
                synchronized(block){
                    try {
                        block.wait();
                    } catch (InterruptedException e) { /*IGNORE*/}
                }
               
                System.out.println("------------");
                System.out.println("SELECT TRANSACTION: List all addresses after DELETE TRANSACTION commit():");
                query = "SELECT " + escapeChar +  "relationAddressesId" + escapeChar +  ", " + escapeChar +  "address" + escapeChar +  " FROM " + escapeChar +  "RelationAddresses" + escapeChar +  "";
                stmt = conn.createStatement();
                rs = stmt.executeQuery(query);
                while(rs.next()){
                    System.out.println("relationAddressesId: " + rs.getInt(1) + " - address: " + rs.getString(2));
                }
                rs.close(); stmt.close();
                System.out.println("------------");
               
                conn.commit();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }
   
    class Delete extends Thread{
        @Override
        public void run(){
            try {
                Class.forName(driver).newInstance();
                Connection conn = DriverManager.getConnection(connection, username, password);
                conn.setAutoCommit(false);
               
                System.out.println("");
                System.out.println("DELETE TRANSACTION: Deleting addresses....");
                System.out.println("");
                String query = "DELETE FROM " + escapeChar +  "RelationAddresses" + escapeChar +  "";
                Statement stmt = conn.createStatement();
                stmt.executeUpdate(query);
                stmt.close();
               
                synchronized(block){
                    block.notifyAll();
                }
               
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {/*IGNORE*/}
                conn.commit();
               
                synchronized(block){
                    block.notifyAll();
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }
}
[/code]

Expected output:
------------
SELECT TRANSACTION: List all addresses:
relationAddressesId: 1 - address: Some address
------------

DELETE TRANSACTION: Deleting addresses....

------------
SELECT TRANSACTION: List all addresses before DELETE TRANSACTION commit():
relationAddressesId: 1 - address: Some address
------------
------------
SELECT TRANSACTION: List all addresses after DELETE TRANSACTION commit():
relationAddressesId: 1 - address: Some address
------------

Actual output:
------------
SELECT TRANSACTION: List all addresses:
relationAddressesId: 1 - address: Some address
------------

DELETE TRANSACTION: Deleting addresses....

------------
SELECT TRANSACTION: List all addresses before DELETE TRANSACTION commit():
relationAddressesId: 1 - address: Some address
------------
------------
SELECT TRANSACTION: List all addresses after DELETE TRANSACTION commit():
------------