Aug 262006
 

Today I started playing with Python again.  My goal is to create a listening “daemon” to process PostgreSQL events.  I wrote about my initial proof of concept at the start of the month. Today, it’s time to look at using this for real.

I had to first install databases/py-psycopg on my development machine. My previous post was based on work I did on my laptop. To recap, the sample source code was:

DSN = 'dbname=test user=dan'

import sys, psycopg, select

print "Opening connection using dns:", DSN
conn = psycopg.connect(DSN)
conn.autocommit(1)

curs = conn.cursor()

curs.execute("listen test")

print "Waiting for 'NOTIFY test'"
while 1:
  select.select([curs],[],[])==([],[],[])
  curs.execute("SELECT 1")
  notifies = curs.notifies()
  for n in notifies:
    # in real life, do something with each...
    print n[0]

This is good for proving you can listen for an event. But you must then act upon the event. Remember that if a given event is raised more than once during a commit, listeners will received only one notification. Mulitple signals (NOTIFY) are reduced to a single event (LISTEN). Thus, there must be some way for the LISTENer to know what work must be done. For FreshPorts, I have identified these events that need to be processed. For the most part, these events involve updates to the following items:

  1. a port – remove the cache entry
  2. ports/MOVED – parse it and record changes into the database
  3. ports/UPDATING – – parse it and record changes into the database
  4. ports/security/vuxml/vuln.xml – parse it and mark any affected ports as vulnerable

As a LISTENer, you don’t get any data. All you know is that at least one event has occurred, and you need to act. For MOVED, UPDATING, and vuln.xml, it doens’t matter if multiple NOTIFY signals have been condensed into one. There is only one action to take: process the newly committed file.

For port updates however, the LISTENer will need to take multiple actions. The signal contains no information as to what ports have been updated. The simple solution is to keep a list of modified ports in another table.

The solution must also maintain state. If for some reason, the Python LISTENer is not running, all events that occur must be recorded. When the LISTENer returns, it will catch up. An event table makes sense here.

I’ve run out of time for this issue today…. We’re heading off to the dinosaur park near Morrisburg.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive