I'm trying to monitor a table in postgres database.
I want to send every operation on this table (INSERT OR UPDATE OR DELETE) to a python tcp server.
So I tried to follow this tutorial, but without any success.
Here's the SQL script I'm using :
CREATE LANGUAGE plpython3u;
CREATE OR REPLACE FUNCTION public.sendsocket(msg character varying, host character varying, port integer)
RETURNS integer AS
$BODY$
import _socket
try:
s = _socket.socket(_socket.AF_INET, _socket.SOCK_STREAM)
s.connect((host, port))
s.sendall(msg)
s.close()
return 1
except:
return 0
$BODY$
LANGUAGE plpython3u VOLATILE
COST 100;
ALTER FUNCTION public.sendsocket(character varying, character varying, integer)
OWNER TO keycloak;
CREATE OR REPLACE FUNCTION public.myTriggerToSendSockets()
RETURNS trigger AS
$BODY$
import json
stmt = plpy.prepare("select public.sendSocket($1, $2, $3)", ["text", "text", "int"])
rv = plpy.execute(stmt, [json.dumps(TD), "myserver", 8881])
$BODY$
LANGUAGE plpython3u VOLATILE
COST 100;
CREATE TRIGGER myTrigger2
AFTER INSERT OR UPDATE OR DELETE
ON event_entity
FOR EACH ROW
EXECUTE PROCEDURE public.myTriggerToSendSockets();
I'm using a simple python TCP server to receive the data and simply print it:
import socket
# Create a socket
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
# Ensure that you can restart your server quickly when it terminates
sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
# Set the client socket's TCP "well-known port" number
well_known_port = 8881
sock.bind(('', well_known_port))
# Set the number of clients waiting for connection that can be queued
sock.listen(5)
# loop waiting for connections (terminate with Ctrl-C)
try:
while 1:
newSocket, address = sock.accept( )
print ("Connected from", address)
# loop serving the new client
while 1:
receivedData = newSocket.recv(1024)
print(receivedData)
if not receivedData: break
newSocket.close( )
finally:
sock.close( )
I'm expecting to get the inserted transaction on this table, but when I execute an operation on event_entity
I'm getting an empty message:
Connected from ('172.29.0.3', 56326)
b''
I believe that the TD object in json.dumps(TD)
is empty.
Can someone help solving this please ?
I had to encode the message in byte before sending it:
s.sendall(msg.encode())