Using postgres notification option to trigger a python script

I’ve a python script in one server, i wanted to trigger the script ,when there is a change in a postgres db table which is on another server.
Note:The postgres db will be updated from a third server.

So far this is what i’ve done,

I have created a trigger function and trigger in postgres db,
then i’ve the function to copy data to db table and a python script to execute through notification from postgres trigger.

In my case ,the trigger part didn’t work, though i was able to copy data to table successfully.

This is my trigger function,

CREATE OR REPLACE FUNCTION notify_changes_my_table()
RETURNS TRIGGER AS
$$
BEGIN
PERFORM pg_notify('my_channel',TG_TABLE_NAME);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This is the trigger,

CREATE TRIGGER trigger_notify_changes_to_others
AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW EXECUTE FUNCTION notify_changes_my_table();

This is how i update my db table,

        with open('my_table.tsv', 'r',) as file:
            cursor.execute("BEGIN;")
            cursor.copy_expert(sql = f"COPY {table_name} FROM STDIN WITH DELIMITER E'\t' CSV HEADER;",file=file)
        cursor.execute("COMMIT;")

This is how i listening to the trigger on other server,

import psycopg2
import subprocess


connection = psycopg2.connect(
host =  "0.0.0.0",
dbname = "db0",
user = "postgres",
password = "password",
port    = "5432"

)


cursor = connection.cursor()
cursor.execute("LISTEN my_channel;")
connection.commit()

try:
    while True:
        if connection.poll():
            print("received notificaation")
            notify = connection.notifies.pop(0)
            subprocess.run(["touch","success.txt"])
finally:
    print("there was some error")
    

Please help me to identify ,why the trigger is not working.Your support is much appreciated.

  • “it didn’t work as expected” – > please clarify

    – 

  • my apologies for inconvenience, i wanted to trigger the python script/the last block after writing the data to db.But the trigger part didn’t work, though i was able to copy data to table successfully.

    – 

  • Don’t use the phrase “didn’t work”, it tells us barely anything, do explain what you expected and what happened instead. edit your question instead of responding in the comments.

    – 

  • The best solution is keep track of the counts, compare the new count with existing count, and if you see any changes, trigger a notification from Python itself instead of relaying on trigger

    – 

  • 2

    In the psycopg2 docs you should read Async notify. You will find that your code differs from what is supposed to be done.

    – 

Leave a Comment