Streaming with listen/notify Fails

253 Views Asked by At

I'm trying to use channels in postgresql to communicate a python script the values that are being inserted. The main goal is to stream data from postgresql to a python script. I have read that NOTIFY and LISTEN are a good way to do this, but I'm open to other solutions that could also do the trick.

I created a trigger that is as follows:

create or replace function notifications() returns trigger as $$

declare ts timestamp without time zone := (select NEW.date);
        b double precision := (select NEW.b);
        a double precision := (select NEW.a);
        msg varchar := null;
begin
    msg := 'timestamp %, b %, a %',ts,b,a;
    select pg_notify('virtual_channel', msg);
    RETURN null;
end;
$$ language plpgsql;

create trigger notifier 
after insert on test_table
for each row execute notifications();

This gives no error when compiled, but it makes the table to stop recieving new entries for new data and also does not send the desired notification. Why this fails? Is better to use the "NOTIFY channel,message" option? I tried to use this first, but I could not use it because of the way of the msg message is created.

0

There are 0 best solutions below