[Psycopg] pg/psycopg: Losing data: END transactions suddenly disappear

[Sebastien Bigaret]

        Hi all,

  I have a *very* annoying problem which drives me crazy and I can't fi=
nd
  any solution.

  I'm using psycopg w/ Zope but I'm not using the zope DA, just plain p=
ython
  adaptor code (this is because the underlying code works also for a
  gtk-app, hence it was designed to be independent from zope).

  This is what happens: the application works fine until some point whe=
re
  the SQL statements it sends are never sent to the application.  More
  precisely:

    - select(nextval) from sequence; continues to work as expected and
      actually increment the sequence by one,

    - but in the same time, UPDATE, INSERT or DELETE statements are NOT
      executed on the postgresql server. There is no exception being ra=
ised
      by psycopg, commit() returns as usual, but the sql statements are
      indeed lost and there's nothing to be done, except restarting the=
 zope
      process.  (END transaction are not produced any more, see below)

  It can happen after a few hours or after a couple of days, and of cou=
rse I
  cannot reproduce the problem by hand. Now every single connection and
  cursor are properly closed and deleted after each transaction, but the
  problem is still there.

  The only thing I noticed in the LOG of postgresql is that the END par=
t for
the transaction:
DEBUG:  StartTransactionCommand
DEBUG:  query: END
DEBUG:  ProcessUtility: END
DEBUG:  CommitTransactionCommand
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes

    is NOT present any more when psycopg commits, until the whole proce=
ss is
    restarted, instead I get the "proc_exit/shmem_exit/exit/reaping dead
    processes" stuff 5 times on different pids.

This is my configuration:

  - psycopg 1.0.15.1 (1.0.12 to 14 had the same problem)
       compiled w/ pg 7.2.3 and egenix-mx-base-2.0.4
  - Zope 2.6.1
  - postgresql 7.2.3
  - linux 2.4.19

  Last: the problem *never* occured with a long-standing single-threaded
  application, but I have no idea whether this can be related to MT. I
  stress-tested the application in single- and multi-threaded situations
  without being able to reproduce the problem.

Does it ring a bell for somebody?

  Any help and hints about a possible way for tracking down the problem=
 would
  be greatly appreciated.

-- S=E9bastien.