Обсуждение: Broken index? (the revenge!)
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 Jippii Midlands 85 London Road, Leicester, England, LE2 0PF. tel: 0116 2230662 fax: 0116 2221305
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