Search Postgresql Archives

problem with unique text column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.
--
begin:vcard
fn:Holger Zwingmann
n:Zwingmann;Holger
org:P3 Solutions GmbH
adr:;;Dennewartstrasse 25-27;Aachen;;52068;Germany
email;internet:holger.zwingmann@xxxxxxxxxxxxxxx
tel;work:+49 241 9437 403
tel;fax:+49 241 9437 431
tel;cell:+49 163 7533721
x-mozilla-html:FALSE
url:http://www.p3-solutions.com
version:2.1
end:vcard


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux