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
In the
psycopg2
docs you should read Async notify. You will find that your code differs from what is supposed to be done.Show 1 more comment