I have a Log table, append only, with some data that I wish to stream back to clients.
Exist a way to stream data from it using only PG without polling?
So I can get:
1. Inv.Add 1, T=10
.. some seconds after, the server push?
2. Inv.Add 2, T=15
Add a comment
|
2 Answers
Add a trigger which calls NOTIFY and then have your client listen to that channel.
There are some huge caveats here with Listen and Notify but they are there to do exactly what you want to do. For example, there are no security permissions checks on who can send notifications so don't rely on the payload for anything.
My approach would be:
- LISTEN (first to prevent race conditions)
- Read the table. Store the latest timestamp, serial id, or other incrementing value
- When you get a notification, query the table for all later rows. Repeat forever.
Again you can send the row data via notify, but then anyone can send your app row data if they can connect to the db and that strikes me as a bit dangerous.
Comments
PostgreSQL doesn't have any way to initiate any operation. All activity must be triggered by external events.
You could add a plperlu trigger on the table, to format the output from values of the row inserted, and append them to a given log file.
CREATE OR REPLACE FUNCTION tf_log_to_file() RETURNS trigger AS
$BODY$
use strict;
use Time::HiRes qw(time);
use POSIX qw(strftime);
my $t = time;
my $date = strftime "%Y%m%d %H:%M:%S", localtime $t;
$date .= sprintf ".%03d", ($t-int($t))*1000;
my $contents = $date . ' Inv.Add ' . $_TD->{new}{yourcolname};
my $file = "/var/log/mylog.log";
open(my $out, '>>', $file) or die "Unable to open or create file $file: $!";
print $out decode_bytea($contents);
close($out);
$BODY$ LANGUAGE plperlu VOLATILE SECURITY DEFINER;
CREATE TRIGGER t_log_to_file AFTER INSERT ON yourtable
FOR EACH ROW EXECUTE PROCEDURE tf_log_to_file();
