Bug #451: Multiple Inserts where should be unique - Mailing list pgsql-bugs

Paul Green (traktion@webleicester.co.uk) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Multiple Inserts where should be unique

Long Description
Hi,

I have been having problems with my indexes and I have discussed this with people on the mailing lists and they don't
seemto be able to help me find a solution, so I'm guessing that it must be a bug. I'm saying it is very severe because
itcompletely breaks my database as my data becomes corrupt, my indexes become broken and vacuum starts to take *days*
tocomplete, if at all. 

The problem occurs when inserting a large (around 180,000) records consecutively. I am finding that duplicates are
creepingthrough the unique index. If I then drop the index and attempt to recreate it, I then get the standard message
sayingthere are duplicates and the index cannot be created. Initially, there were thoughts that the index may be
corrupted,so I created a new table + index and attempted to insert all of the values of the previous table. This still
leadto duplicates slipping through even though *some* were now rejected. Out of the 180,000 records, around 500-1000
oftenare inserted as duplicates. 

System information:
O/S: Linux, Redhat 7.1, kernel 2.4.2-2
Hardware: AMD K6-2 300, 3 gig IDE disk, 128mb RAM.
PostgreSQL: Version 7.1.3 (was 7.0, but upgraded due to bug)
JDBC Driver: jdbc7.1-1.2.jar (also tried jdbc7.0-1.1.jar)

Below are the steps I made:

player_stats=> CREATE SEQUENCE player1_serial START 1;

player_stats=> create table player1 (id int4 default
nextval('player1_serial'::text) NOT NULL, name varchar(50) not null,
password varchar(50), icqnumber varchar(20), emailaddress varchar(255),
flatname varchar(50), PRIMARY KEY (id));

player_stats=> create unique index player1_name_key on player1 using btree
(name);
(NOTE: I also tried using UNIQUE (name) in the create table statement - no difference to result)

Then I ran the java program 'RecreatePlayer' (see code below). This returned at the end of output:

177967 players inserted
611 players FAILED to be inserted

So clearly the index is rejecting some of the names which have already been inserted. Then I tried this query:

player_stats=> select count(distinct name) from player1;
 count
--------
 176835
(1 row)

player_stats=> select count(name) from player1;
 count
--------
 177356
(1 row)

Clearly, there are differing opinions being returned. Droping + creating a new index returns:
create unique index player1_name_key on player1 using btree (name);
ERROR:  Cannot create unique index. Table contains non-unique values

This is all causing major problems for my database and I have no idea whether the bug is in the JDBC driver or the
posgresqlengine, but there definately seems to be a major problem here. 

Regards,
Paul Green

Sample Code
RecreatePlayer code:

import java.io.*;
import java.lang.*;
import java.sql.*;

public class RecreatePlayer {
    private final static boolean DEBUG=true;

    public static void main(String args[]) {
        try
        {
            DBConn db = new DBConn("/home/httpd/conf/DBConn.conf");

            String SQLString = "SELECT * FROM Player";
            ResultSet rs = db.doSQL(SQLString);

            int counter=0;
            int failed=0;
            while(rs.next()) {
                counter++;
                if(DEBUG) System.out.println("INSERTING
PLAYER "+counter+": "+rs.getString("name"));
                SQLString = "INSERT INTO player1 (id,name,flatname)
VALUES
("+rs.getString("id")+",'"+replaceChars(rs.getString("name")).trim()+"',LOWER('"+replaceChars(rs.getString("name")).trim()+"'))";
                if (!db.doSQLUpdate(SQLString))
                    failed++;
            }
            System.out.println(counter+" players inserted");
            System.out.println(failed+" players FAILED to be
inserted");
        }
        catch(SQLException e)
        {
            System.err.println("SQLException: "+e);
        }
    }

    private static String replaceChars(String input) {
        if (input == null)
            return "";
        StringBuffer tempString = new StringBuffer();
        int x=0;
        while(x < input.length()) {
            if(input.charAt(x) == '\'') tempString.append('\'');
            tempString.append(input.charAt(x));
            x++;
        }
        return tempString.toString().trim();
    }
}

DBConn fragment (NOTE: all statements sent through an existing, open connection, conn):

Statement stmt = conn.createStatement();
stmt.setQueryTimeout(120);
if(stmt.executeUpdate(sqlStr) > 0)
    return true;
else
    return false;

No file was uploaded with this report