I have a table in which one of the columns is primary key and it is of data type citext.
Many threads will update the table, they are properly synchronized. Those threads write the data in files(separate file for each thread) and then dump the data into a temp table which also has same column as primary key and then update the main table using
update tabl1.col1 = temp1.col1, ...
where tabl1.pkcol = temp1.pkcol
and then insert new data using
insert into tabl1 (col1, col2, ...)
select col1, col2, ...
from temp1 where not exists (select * from temp1 where tabl1.col1 = temp1.col1)
Now I have many duplicates in the primary key column.
Environment Details:
OS: Win server 2008 r2 64 bit
Postgres version 9.2.4
Used by a java(java 1.5) application running in the same machine.
I checked for any abnormal stoppages of java/ postgres which had not happened.
After dropping and re creating the table it is working normally under same conditions.
Under what scenario such duplicate entries would be inserted? Why the DB didnt throw any error while duplicate value was being inserted?
Any help or pointers would be appreciated.
REINDEX
should fix it. – M. Mimpen Jan 20 at 10:40citext
's index access methods. Unfortunately, in this case, it sounds like you have destroyed the evidence we would need to help you, so I don't think there's much point pursuing this. (It would've been great if you'd done anALTER TABLE ... RENAME
instead, preserving the old table). – Craig Ringer Jan 20 at 12:53