Update SMPP client to send high priority, transactional messages before bulk messages

[tobiasmcnulty]

To do:

• Add a MTMessage.priority_flag integer column with choices based on the priority_flag values in the SMPP spec:

0 = Level 0 (lowest) priority
1 = Level 1 priority
2 = Level 2 priority
3 = Level 3 (highest) priority

• Add -priority_flag to the existing conditional index on the MTMessage model to facilitate fast retrieval of new messages (descending, to return highest priority first) (see: https://docs.djangoproject.com/en/5.1/ref/models/indexes/#expressions, https://docs.djangoproject.com/en/5.1/ref/models/expressions/#using-f-to-sort-null-values)
• Add a PriorityBlockingRouter in the rapidsms-smpp-gateway repo that always sets priority_flag=2 in respond()
• Add --set-priority-flag command line option (BooleanOptionalAction) that includes the priority_flag in the PDU, if enabled. Defaults to False. If a priority_flag is included in the --submit-sm-params on the command line, the priority_flag set on the individual message should take precedence.
• Optionally, create test data locally with (1) 10K 'new', priority_flag=2 messages, (2) 3M 'new', priority_flag=1 messages, (3), 5M 'sent', priority_flag=2, and (4) 5M 'sent' priority_flag=None messages
• EXPLAIN ANALYZE psql should use the index and be fast, like so (might need to VACUUM ANALYZE):

db=> explain analyze SELECT * from smpp_gateway_mtmessage WHERE status='new';
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using mt_message_status_idx on smpp_gateway_mtmessage  (cost=0.38..4.85 rows=1 width=407) (actual time=120.732..120.733 rows=0 loops=1)
 Planning Time: 0.172 ms
 Execution Time: 120.758 ms
(3 rows)

[tobiasmcnulty]

@simonkagwi Would you be up for thinking this through and making a PR?

[simonkagwi]

@tobiasmcnulty Yeah, sure. I'll do that

[simonkagwi]

@tobiasmcnulty Is there a way to filter on the MTMessage model to get transactional messages only, or how would we identify if a message is transactional or not?

[simonkagwi]

@tobiasmcnulty I've set up test data for the MTMessage model and tested the SELECT query that the get_mt_messages_to_send() function does. The query seems fast and uses the index as expected

db=# select status, priority_flag, count(id) as total from smpp_gateway_mtmessage group by 1, 2;
 status | priority_flag |  total  
--------+---------------+---------
 new    |             1 | 2999874
 new    |             2 |    9881
 new    |               |       2
 sent   |             2 | 4994699
 sent   |               | 5005549
(5 rows)

db=# EXPLAIN ANALYZE SELECT "smpp_gateway_mtmessage"."id", "smpp_gateway_mtmessage"."short_message", "smpp_gateway_mtmessage"."params", "smpp_gateway_mtmessage"."priority_flag" FROM "smpp_gateway_mtmessage" WHERE ("smpp_gateway_mtmessage"."backend_id" = 1 AND "smpp_gateway_mtmessage"."status" = 'new') ORDER BY "smpp_gateway_mtmessage"."priority_flag" DESC NULLS LAST LIMIT 100 FOR UPDATE SKIP LOCKED;
                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..42.93 rows=100 width=172) (actual time=0.077..0.206 rows=100 loops=1)
   ->  LockRows  (cost=0.43..1245126.95 rows=2929632 width=172) (actual time=0.076..0.200 rows=100 loops=1)
         ->  Index Scan using mt_message_status_idx on smpp_gateway_mtmessage  (cost=0.43..1215830.63 rows=2929632 width=172) (actual time=0.065..0.144 rows=100 loops=1)
               Index Cond: ((status)::text = 'new'::text)
               Filter: (backend_id = 1)
               Rows Removed by Filter: 2
 Planning Time: 0.097 ms
 Execution Time: 0.245 ms
(8 rows)

[tobiasmcnulty]

@simonkagwi Amazing! Excellent work.