python & postgresql: reliably check for updates in a specific table

Question

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 reception and execution of such orders though is a different matter as I cannot allow x minutes to pass but I need them as soon as they come in. I initialized another thread to check for such data (execution) which is in a database table (POSTGRES SQL).

Problem(s): I cannot continuosly perform query every xx ms, get data from DB, compare table length, and then get the difference for a variety of reasons (not only guy to use such DB, perforamnce issues, etc). so I looked up some solutions and came up with this thread (https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table) where basically the gist of it was that "There is no reliable, authorative record of the last modified time of a table".

Question: what can I do about it, that is: getting near instantenuous responses from a postgres sql table without overloading the whole thing using Python?

Answer 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:

CREATE OR REPLACE FUNCTION notify_id_trigger() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('new_id', NEW.ID);
  RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER data_modified AFTER insert or update on data_table for each row execute procedure notify_id_trigger();")