Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
    
When searching the Interwebs, I found this post. At first glance, they talk about a corrupt index in which case REINDEX should fix it. –  M. Mimpen Jan 20 at 10:40
    
Thanks M.Mimpen , the thread pointed says that there was a system crash that corrupted the index. In my case there was no such event. So if it was really a corrupted index(Which I doubt) what could be the possible causes. and since I have dropped the table and recreated it, cant try REINDEX. Any way thanks for the pointer. –  Kam Jan 20 at 11:03
    
Abnormal stops should not cause such issues anyway. I won't be too shocked if you've found a bug lurking somewhere, possibly in citext'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 an ALTER TABLE ... RENAME instead, preserving the old table). –  Craig Ringer Jan 20 at 12:53
    
Thanks Craig. in future fortunately or unfortunately if I come across the same problem what are the possible things I should check/do (other than renaming the table). I dont know about the internals of pgsql indexing. Any pointers? –  Kam Jan 20 at 13:12
    
There is a gotcha: "The schema containing the citext operators must be in the current search_path (typically public); if it is not, the normal case-sensitive text operators will be invoked instead." - maybe you recreated the table in different schema. –  hegemon Apr 25 at 22:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.