Python & Postgresql: Reliably Check For Updates In A Specific Table

Situation: I have a live trading script which computes all sorts of stuff every x minutes in my main thread (Python). the order sending is performed through such thread. the recept

Solution 1:

You can use notifications in postgresql:

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import select


def dblisten(dsn):
    connection = psycopg2.connect(dsn)
    connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    cur = connection.cursor()
    cur.execute("LISTEN new_id;")
    while True:
        select.select([connection],[],[])
        connection.poll()
        events = []
        while connection.notifies:
            notify = connection.notifies.pop().payload
            do_something(notify)

and install a trigger for each update:

CREATEOR REPLACE FUNCTION notify_id_trigger() RETURNStriggerAS $$
BEGIN
  PERFORM pg_notify('new_id', NEW.ID);
  RETURNnew;
END;
$$ LANGUAGE plpgsql;

CREATETRIGGER data_modified AFTER insertorupdateon data_table foreachrowexecuteprocedure notify_id_trigger();")