Обсуждение: problem with unique text column

Hi,

I have a problem with table which I use to store some variables. The
table holds a primary key, a text column (unique) named 'text_key' and a
text colum named 'text_value'.

I query and update and insert into the table via appropriate store
procedures, which usually work fine. This looks then e.g. like

SELECT * FROM AddUpdateKeyValue(key,value) INTO p_error

Here, my store procedure checks if the text 'key' exists and will update
the row or create a new entry in order to store the (key,value) pair, if
needed.

I read out the variables via e.g.

SELECT * FROM GetKeyValue(key) INTO value, p_error

I use these store procedures within a C++ project, encapsulating each
access to the DB with a transaction and roll back if anything goes
wrong. Access to the DB is running within a separate thread utilizing a
thread save FIFO queue to interact with the DB.

Everything works fine unless I disconnect from the DB, shut it down,
create a (file) copy of it, start it up and reconnect again within my
thread. While I am disconnected form the DB, my code will simply queue
up the DB requests until I have reconnected again.

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.


I am using vers. 8.1 utilizing UTF 8 encoding on a Linux OS.


Any Ideas?


Regards,
Holger.
--

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