Broken index? (the revenge) - Mailing list pgsql-admin

I don't think this reached the list, so I thought I'd send it again...

On 2001.09.11 14:35 Paul Green wrote:
Hi again,

Ok, for those of you who have been following my troubles with my indexes on
my site, this may interest you. I'd also appreciate any help once again,
because now I am completely foxed. It looks like shortly I will be
upgrading to 7.1.3 in a hope that this error will go away, but I'd be
interested to know why this is happening in 7.0 and whether it still
happens in 7.1.3.

In response to advice to reconstruct the table and reindex, I did the
following in psql:

--

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);

--

I then ran the java program below using the postgres jdbc driver
(jdbc7.0-1.2.jar):

--

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();
    }
}

NOTE: doSQLUpdate fragment :=

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

--

The doSQLUpdate(String) simply asks the driver to use the current
connection and send the query returning true/false depending on whether it
was successful or unsuccessful. Unsuccessful attemps are usually to do with
the 'name' of the player already existing. After running this program, the
last output was:

--

177967 players inserted
611 players FAILED to be inserted

--

So, I thought the new index had done its job and kept duplicates out of the
system, until I executed these queries to check:

--

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

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

--

As you can see the numbers are *different*. I tried to drop the index and
reaply using:

--

create unique index player1_name_key on player1 using btree (name);
ERROR:  Cannot create unique index. Table contains non-unique values

--

But as you can see, I got an error message.

*ALL* of the records were inserted at the same time through the same
connection and these errors still occured, so there can't be a problem with
locale and such. I originaly thought that this error may be due to having
multiple non-unique inserts in a transaction, but each of these inserts was
added through its own transaction. Does anyone have any idea why this is
happening? Is it the JDBC driver, my installation, my version of pgsql? Any
help would be much appreciated!

Cheers!
--
Paul Green
Programmer