On Nov 17, 2003, at 7:20 AM, Gary Murphy wrote:
Ilja:
To truly benefit from transaction control you really need to
advantage, not
just by grouping DML statements, but also by use of the "rollback"
statement
in the case of an individual statement's failure.
Correct, although you don't actually need to rollback a transaction
postgres if there's a error reported from the database, as it puts the
entire transaction into an abort state. You can (effectively) either
commit or rollback to clear that transaction and start another one. Of
course, you really want to be trapping for that sort of error so that
you can report back to the outer levels.
Eric's first try is a quick and dirty method which manages to bundle
together the three insert statements which are used for inserting data
into
the database, which will definitely help performance and is definitely
an
improvement.
That was quick and dirty, and (iirc) it's not exactly right. It
certainly doesn't deal with all of the possible quota issues. I was
trying to get something that was both simple enough to be easily coded
and explained and enough of a performance win that it's clearly a
direction to go.
I've also been thinking about the quotas a bit, especially not worrying
about them if there's no quota on the account. But for those cases
where there is one, It would be _really_ nice to be able to figure out
message size and quota applicability before a potentially large message
was added to the database, at least from a code path point of view. It
would make the transactioning much more of a catch for database
problems than the rollback bits that are required for a quota miss now.
There are really a few possibilities: If the message size is small,
then it isn't that bad to read it into memory. Either it's going to
work, or you're already over quota. For large messages, it's more
dangerous to read into memory, especially depending on the largest
message that is supported through the mta. Here if maxMessage <
freeQuota, then quota is not an issue. It's only where
maxMessage>freeQuota and maxMessage is too much ram to burn that
there's a problem. I don't know of many systems where the max mail
message size is over 10 megs.
The other possibilty is that if you look at is as softQuota =
enteredQuota and hardQuota = enteredQuota+maxMessage, then you could
just look to see if they're already over quota before the insert, just
preventing any further messages past the one that went over. If that's
coupled with reading in the entire message to get the size, then you
already have all of the information for the uniqueid hash and the
message size, so it's no longer an insert + update on the message
table.
Best use concepts, however, dictate that transaction control
really belongs to the database level, not the application level. His
is,
however, the "easiest" solution given the current code layout.
Indication of begin/end transactions need to be at the code level where
you know that you're dealing with a set of changes that need to
atomically happen. That could be calling a stored procedure so that one
statement is issued with autocommit transactions, or it could be at the
level that we have here. I will say that we shouldn't be issuing sql
for begin/commit at that level.
I believe the correct solution, if it doesn't break one of the IMAP or
POP3
RFC's is to call only one database function to completely manage a
message
insert when one is desired rather than depending upon an assorted
number of
these. Then transaction enable the function to respond correctly upon
inserts (batch them together) and or failures (rollback everything) if
and
when they happen.
It depends on how much error reporting logic we want. It it's just
pass/fail, then a transaction failing is fine. But with more fine
grained deliveries, with multiple recipients on different quotas, it's
possible for some to fail and some to succeed. Which is hard to do from
within the database event horizon. (at least, on postgres. It's
impossible on mysql). Pop should be nearly trivial to do with single
statement update/deletes, there's some trickyness to imap that might
prevent some of that.
Sorry if this is a bit of a brain dump, but I've had these things
rattling around in my head for a while. Expect another one on the
db_getmailbox function soon, since that's one place where my
performance is hurting right now. ;>
eric
