smsd, postgresql and large numbers of messages - bugs?

[drbob]

Hello,

I'm running gnokki 0.6.26 from the Debian Lenny package. I recently 
noticed that when I add messages to the outbox the new rows are locked and 
not updated until *all* the added messages have been sent, this can be a 
long time if I've added a significant number of messages to the outbox.

Is this intended? I would think a better design would be to update each 
row immediately after the message has been sent, rather then waiting for 
all the messages in that polling cycle to have been sent. 

If this isn't possible, why not limit each polling cycle to say 5 messages 
* the length of the polling cycle as no one phone can send more than 5 
messages a second (can they?) then we'll get a database update at least 
every 5 messages.

Looking at the code for pq.c of 0.6.26 the reason the rows are not updated 
immediately after each message has been sent is that the UPDATE statements 
are queued behind the "SELECT FOR UPDATE" command which retrieve the new 
messages from the database. 

Both the "SELECT FOR UPDATE" and the row UPDATE statements are within a 
BEGIN...COMMIT block which is not committed until all the messages have 
been sent. So whilst the for loop which iterates over each new message 
might appear to update the database after each message has been sent the 
changes are not actually committed until after the last message (as the 
COMMIT command is outside the loop.

Looking at the git master code for pq.c it appears that someone has 
attempted to resolve the above issue by removing the BEGIN and COMMIT 
statements. Therefore postgresql will autocommit each statement. The 
problem with this is that the ROW locks from the SELECT FOR UPDATE are 
lost after the autocommit so removing BEGIN and COMMIT in this way will 
break the ability to run multiple instances of smsd for more than one 
phone - I believe you will end up with messages sent twice as rows already 
being dealt with by one instance of smsd won't be locked.

Perhaps a better solution would be to move the BEGIN..COMMIT and SELECT 
FOR UPDATE into the for loop?

I'd appreciate comments from others before I attempt to tackle these 
issues with at patch - please tell me if you concur with my analysis or if 
I've got something wrong.

Apologies for the wall of text,

kind regards,

drbob