python - losing connection to postgresql in daemon

Question

I am rewriting a python script to store data from an arduino in a postgresql data base, wanting to run it as a deamon using python-daemon. The original script works fine, but in the deamon, I cannot write to the database. The first attempt ends up with:

<class 'psycopg2.DatabaseError'>, DatabaseError('SSL SYSCALL error: EOF detected\n'

and then:

<class 'psycopg2.InterfaceError'>, InterfaceError('cursor already closed',)

In the working script, I do:

connstring="dbname='"+dbdatabase+"' user='"+dbusername+"' host='"+dbhost+"'password='"+dbpassword+"'"
try:
  conn = psycopg2.connect(connstring)
  cur=conn.cursor()
except:
  my_logger.critical(appname+": Unable to connect to the database")
  sys.exit(2)
sql="insert into measure (sensorid,typeid,value) VALUES(%s,%s,%s)"
< more to set up serialport, logging and so on>
while 1:
  < fetch a data set and split it to a list >
  for (i,val) in enumerate measures:
     try:
       cur.execute(sql,(sensors[i],typeid[i],val)) 
       conn.commit()
     except:
       self.logger.error(appname+": error 106 :"+str(sys.exc_info()))

I have a feeling this may be some of the same problem that I initially had with the serial connection, Serial port does not work in rewritten Python code, so I have tried to fiddle with files_preserve, doing:

self.files_preserve=range(daemon.daemon.get_maximum_file_descriptors()+1)

which as far as I can understand should keep open all file handles, but to no avail.

In the daemon, I have tried first to set up the data base connection as attributes in __init__, i. e.:

self.conn = psycopg2.connect(connstring)
self.cur=conn.cursor()

and then do the inserts in the run method. I tried also to create connection at the top of the run method and even setting it up as a global object, but in all cases, something seems to be killing the database connection. Any clues? (or any clues to where to find some documentation (other than the source) for the daemon module?)

Both the daemon and the database are running on debian linux systems with python 2.7and postgresql8.4`.

Answer 1

As far as i can tell from it's source, daemon.runner works by forking and then executing the run method of the daemon app you supplied.

That means that you're creating the database connection in one process, but then try to use it in a forked process, which psycopg2 doesn't like:

libpq connections shouldn’t be used by a forked processes, so [...] make sure to create the connections after the fork.

In this case that means: move your call to psycopg2.connect into the run method.