Re: options for launching sql script asynchronously from web app

We are using it on Debian etch as far as i know.

By default but if you use pgq_ext it guarantees. Just in many cases we can build stuff so thet duplicate events are ignored by procedure that applies them.

That i do not understand fully. Events are grouped in batches as they can be processed more efficiently in batches.

Yes it's polling the queue. Usual delay for event processing with default configuration is around couple of seconds.

Our consumers written in python are usually really small (code fits on screen without scrolling) SkyTools contains several useful consumer so for common solutions wýou might not even need to write anything.
Here is the code for one called simple_serial_consumer that reads url encoded events from queue decodes them and for each event executes function in some database with parameters filled from the event. Also keeps track which events have been processed so it's guaranteed that none will be done twice.

#!/usr/bin/python

import sys, pgq, skytools
skytools.sane_config = 1

class SimpleSerialConsumer(pgq.SerialConsumer):
    def __init__(self, args):
        pgq.SerialConsumer.__init__(self,"simple_serial_consumer","src_db","dst_db", args)
        self.dst_query = self.cf.get("dst_query")
        self.producer = self.cf.get("producer", default="")

    def process_remote_batch(self, batch_id, ev_list, dst_db):
        curs = dst_db.cursor()
        for ev in ev_list:
            if self.producer <> "" and ev.producer <> self.producer:
                ev.tag_done()
                continue
            payload = skytools.db_urldecode(ev.payload)
            self.log.debug(self.dst_query % payload)
            curs.execute(self.dst_query, payload)
            try:
                res = curs.dictfetchone()
                self.log.debug(res)
            except:
                pass
            ev.tag_done()

if __name__ == '__main__':
    script = SimpleSerialConsumer(sys.argv[1:])
    script.start()

SkyTools also contains trigger function logutriga that when attaqched to the table creates url encoded events into the queue thus making inserting events into queue as simple as inserting into table with standard sql.