plpythonpostgresql-12

Sending data from POSTGRESQL trigger to a python TCP server


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 ?


Solution

  • I had to encode the message in byte before sending it:

    s.sendall(msg.encode())