Overview

You are interested in the procedure to check how many messages are in the queue/buffer and the procedure to delete these messages

Solution

Checking the number of messages in the queue

1. Connect as mercury
   

   ssh newnet@<servername>
   sudo su
   su - mercury

2. Run psql utility
   

   psql mdg

3. Execute the following query:
   

   select q.queue_id, q.entity_id, count('x') cnt from ddgtr d inner join queue q on q.queue_id = d.queue_id where d.tr_flags != 1024 group by q.queue_id, q.entity_id;

   
   If you need to check buffer/queue on the basis of MSISDN please run the following query:
   

   select  q.queue_id, q.entity_id, count('x') cnt from  ddgtr d inner join queue q on q.queue_id = d.queue_id where d.tr_flags != 1024 and d.recipient_address='{A or B party MSISDN}' group by q.queue_id, q.entity_id;
   

   Example output:
   mdg=# select
   mdg-# q.queue_id,
   mdg-# q.entity_id,
   mdg-# count('x') cnt
   mdg-# from
   mdg-# ddgtr d inner join
   mdg-# queue q on q.queue_id = d.queue_id
   mdg-# where
   mdg-# d.tr_flags != 1024
   mdg-# group by
   mdg-# q.queue_id,
   mdg-# q.entity_id;
   queue_id | entity_id | cnt
   ----------------------------------------------------------
   6003 | <entityname> | 6
   6005 | <entityname> | 1

   In this example queue 6003 contains 6 unexpired messages.
   
4. To delete the messages from the queue, the messages need to be set as expired. Please execute this SQL query:
   

   update ddgtr set tr_flags = 1024 where queue_id = ... and tr_flags != 1024;

5. The messages will be marked as expired and the cleanup script (scheduled to execute in crontab) will properly clean them up from disk and database.