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.
--