Skip site navigation (1) Skip section navigation (2)

Bug #451: Multiple Inserts where should be unique

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #451: Multiple Inserts where should be unique
Date: 2001-09-14 08:56:38
Message-ID: 200109140856.f8E8uc496298@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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


pgsql-bugs by date

Next:From: Tom LaneDate: 2001-09-14 14:41:23
Subject: Re: Re: timestamps cannot be created without time zones
Previous:From: Rainer MagerDate: 2001-09-13 22:42:28
Subject: Re: Re: timestamps cannot be created without time zones

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group