Bug #451: Multiple Inserts where should be unique

Paul Green (traktion(at)webleicester(dot)co(dot)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 seem to 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 it completely breaks my database as my data becomes corrupt, my indexes become broken and vacuum starts to take *days* to complete, if at all.

The problem occurs when inserting a large (around 180,000) records consecutively. I am finding that duplicates are creeping through the unique index. If I then drop the index and attempt to recreate it, I then get the standard message saying there 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 lead to duplicates slipping through even though *some* were now rejected. Out of the 180,000 records, around 500-1000 often are 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 posgresql engine, 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