Re: Please Help! Was: Multithread problem: Error in transaction processing

Hi,

I will have to try again. I know that I was probably not specific enough in my first attempt.

The systems on which we have tested and are getting the error:
- PostgreSQL 8.2.4 (also tried 8.2.6 without db upgrade) configured with 'enable_thread_safety'.
- Linux Slackware 10.2.0 both kernel 2.4.31 and kernel 2.6.15, and Linux Slackware 9.1.0 with kernel 2.4.14.

The client (our daemon) has both been compiled based on 8.2.4 and 8.2.6 but seems not to make any difference.

Our daemon creates a couple of threads doing database access on each their (named) connection, where one of these threads will receive UDP packages rather often with data to the database Therefore we open the database at the thread start and keep it open all through the execution time (which can be months). When we get a package it results in a database update (1 row updated for each package), and here we often gets the -401 error. Another thread is a 'timer' thread running every 30 seconds checking the database for certain changes (read only). This also gets the error once in a while. I have not made any statistics about how often, but it's more like in 5% of the attempts than 0.1%.

In which situations is it possible to get this error? I have of course looked in the documentation, but it is not very specific, just that you can get it during Start Transaction (which is where we see it), Rollback, or Commit. What can be the basic cause of getting the error? How do I dig into getting it resolved? Can someone please point me to some documentation/description/examples on how to access the database in multiple threads. (We have of course looked in the PostgreSQL manual and searched the net. That is why we are using one connection per thread.)

As a workaround we close the database and reopen it when we get this error. Is this a normal/bug situation ?

Any suggestions will be most welcome,

Leif

----- Original Message -----
From: "Leif Jensen" <leif(at)leifjensen(dot)com>
To: "Leif Jensen" <leif(at)crysberg(dot)dk>
Sent: Friday, March 14, 2008 9:44:27 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
Subject: [GENERAL] Multithread problem: Error in transaction processing

Hi Guys,

We have a system with a multithreaded daemon running on Linux using
PostgreSQL 8.2.4 Embedded SQL in C. Most of the threads are doing database
access and they have each their own conection which is opened when the
thread is created (as one of the first things within the thread).

We pretty often gets the error:

-401:08007 Error in transaction processing in line 200

which is where there is an EXEC SQL AT :xxx START TRANSACTION ...

I have checked and re-checked the code for non- coomit/rollback and
added lots of test output to verify that every transaction is either
committed or rolled back. I have also made sure that there is no
cross-thread use of connection, especially looking at the test output.

Any ideas ?

Leif