Re: problem with unique text column

Holger Zwingmann wrote:
> If I check, after the reconnect, for some given 'text key' my SELECT
> won't find it and my procedure will thus insert a new (key,value) pair.
> When I disconnect and reconnect sometimes later again, I will suddenly
> find both of the keys with the next query and will thus raise a unique
> violation from within my store procedure.
>
> During my investigations, I also realized the following:
>
> When I dump the 'key/value' table using pgdump into a file containing
> INSERTS and then execute the file into a (empty) DB, which I have
> created initially via a file copy of my DB folder (DB was down, of
> course), I am able to insert a already existing 'text_key' again. I am
> only able find the 'text_key' if I do a string compare using LIKE, a
> simple key_value='value' query does not work. I thought it might be a
> encoding problem, but this also happens if I set the encoding option of
> pgdump to generate the dump file in utf-8, which is my DB setting,
> explicitly.

OK, if you are certain that the text values are the same...

It could be that the index is disagreeing with the data in the tables.
You can test this by issuing "SET enable_indexscan=false" before running
your two queries. If they both return the same answer then the index is
at fault. Try a REINDEX and see if the problem goes away.

If this is happening only when you stop the DB, copy the files and
restart it then that would suggest to me one of:
1. You have fsync turned off, so data is not being written to disk
2. You aren't copying the files fully
3. There may be errors in the logs

--
Richard Huxton
Archonet Ltd