蟒蛇&postgresql:可靠地检查特定表中的更新 [英] python & postgresql: reliably check for updates in a specific table

问题描述

情况:我有一个实时交易脚本，它在我的主 线程 (Python) 中每 x 分钟计算一次各种内容.订单发送是通过这样的thread来执行的.尽管此类订单的接收和执行是另一回事，因为我不能让 x 分钟过去，但我一进来就需要它们.我初始化了另一个 thread 以检查此类数据(执行)它位于数据库表中(POSTGRES SQL).

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).

问题:我不能连续每 xx 毫秒执行一次查询，从数据库中获取数据，比较表长度，然后由于各种原因得到差异(不仅是使用这样的人)DB、性能问题等).所以我查找了一些解决方案并提出了这个线程(https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table) 基本上它的要点是表的最后修改时间没有可靠、权威的记录".

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".

问题:我能做些什么，即:从 postgres sql 表获得近乎瞬时的响应，而无需使用 Python代码>?

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?

推荐答案

您可以在 postgresql 中使用通知:

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();")

这篇关于蟒蛇&postgresql:可靠地检查特定表中的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！