problem with unique text column

From: Holger Zwingmann <holger(dot)zwingmann(at)p3-solutions(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: problem with unique text column
Date: 2006-04-26 22:12:39
Message-ID: 444FF057.8040201@p3-solutions.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Attachment Content-Type Size
holger.zwingmann.vcf text/x-vcard 324 bytes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Talbot-Wilson 2006-04-26 22:42:12 Re: "save history" problem
Previous Message alexandre - aldeia digital 2006-04-26 22:02:58 Re: pg_dump -t <> pg_restore -t