0

I have trigger of after update / after insert and have python listener using pg_notify. Trigger:

DECLARE
    channel text :='test_channel';
BEGIN
    RAISE NOTICE 'channel %  % ',id;
    PERFORM pg_notify(channel,json_build_object('id',new.id,'col1',col1,'col2',col2);

    RETURN NEW;
END;

Python code to listen :

while True:
    conn_psycopg.poll()
    while conn_psycopg.notifies:
        notify = conn_psycopg.notifies.pop(0)
        json_payload = json.loads(notify.payload)
        id = json_payload.get('id')
        prepare_payload_and_make_api_call(json_payload, id)

It is working fine with less no of records but there is bulk update (10k) happens taking 1 hour to process all records. Currently if bulk update happens in data receives one by one, so one by one processing is taking time.

{id:1, name: abc} {id:2, name: xyz} {id:3, name: pqr}

Because of this behavior we are also processing these records one by one, but we receive bulk records in one go like this:

[{id:1, name: abc}, {id:2, name: xyz}, {id:3, name: pqr}]

then processing will improve.

Please suggest if there are other approaches also which will help to improve speed.

jayashri sathe
  • 75
  • 1
  • 7
  • You have a problem with your code, but you haven't shown us any of it. Receiving 10,000 notices takes a small fraction of a second. If you want to aggregate them into a large list before processing them, just do that. – jjanes Aug 24 '23 at 18:12
  • Added python code and trigger – jayashri sathe Aug 28 '23 at 06:20
  • You could try reading a slice of `notifies`, then deleting the slice, something like `num_notifications = len(notify = conn_psycopg.notifies; notify = conn_psycopg.notifies[:num_notifications]; del conn_psycopg.notifies[:num_notifications]`. – snakecharmerb Aug 28 '23 at 07:22
  • Thing is `conn_psycopg.notifies` gives single record at a time like this, this doesn't give all records in a single list `num_notifications====== [Notify(564580, 'test_channel', '{"id" : 1, "name" : "abc"})] num_notifications====== [Notify(564580, 'test_channel', '{"id" : 2, "name" : "pqr"})] num_notifications====== [Notify(564580, 'test_channel', '{"id" : 3, "name" : "abc"})]` – jayashri sathe Aug 28 '23 at 12:27
  • @jayashrisathe By using pop(0) in a loop, you are *asking* for a single record at a time. – jjanes Aug 30 '23 at 02:31

1 Answers1

0

You could use a statement trigger rather than row trigger, and then use the 'transition table' feature to compose a larger payload. But pg_notify has a rather small limit on the size of a payload, so there is no way you can condense 10,000 updated rows into a single pg_notify call.

Better to accumulate the messages on the python side, and then dispatch them to the api call once enough have accumulated (or enough time has elapsed that you don't want to wait longer for more). Or maybe just fix the api call so it isn't so inefficient to do one at a time.

jjanes
  • 37,812
  • 5
  • 27
  • 34