Re: Please Help! Was: Multithread problem: Error in transaction processing - Mailing list pgsql-general

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
threadswill receive UDP packages rather often with data to the database Therefore we open the database at the thread
startand keep it open all through the execution time (which can be months). When we get a package it results in a
databaseupdate (1 row updated for each package), and here we often gets the -401 error. Another thread is a 'timer'
threadrunning every 30 seconds checking the database for certain changes (read only). This also gets the error once in
awhile. 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
veryspecific, just that you can get it during Start Transaction (which is where we see it), Rollback, or Commit. What
canbe the basic cause of getting the error? How do I dig into getting it resolved? Can someone please point me to some
documentation/description/exampleson how to access the database in multiple threads. (We have of course looked in the
PostgreSQLmanual 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@leifjensen.com>
To: "Leif Jensen" <leif@crysberg.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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general