Forum OpenACS Development: Problem with db_transaction?

One of our servers (AOL 3.3+ad13, OpenACS 5.0.2 (approximately), postgres 7.4.2 ) occasionally hangs, not accepting new connections (and not running scheduled procs).

Restarting the server gets things back to normal.

At least two out of the last four times this happened, the last code being executed has been:

ns_log notice "mailing lists: try to record bounced mail"
catch {
if {[regexp {X-Field: ([0-9]*)-([0-9]*)} $body match user_id job_id]} {
db_transaction {
ns_log notice "mailing lists: bounce recorded for job $job_id and user $user_id"
db_dml record_bounce_in_log {}
if {[db_resultrows]} {
db_dml increment_bounce_count {}
}
}
}
}

The first ns_log statement is executed, the second one (inside the db_transaction) isn't. The regexp matches.

Can anyone think of any reason db_transaction should fail to initialize? Unable to allocate a db_handle? Or something else related to postgresql and transactions?

/Nis

Ahh, exhaustion of available db handles was going to be my guess ... cool.

Stopping AOLserver doesn't send a SIGTERM signal to the PG backend (or Oracle, for that matter, if you're using that RDMS). Should it? I don't think the answer is obviously "yes", just because you stop AOLserver doesn't mean that you don't want to record (say) the finalization of a credit card transaction that just cleared before you decided to restart AOLserver ... normally I think you'd want transactions that have started to run to completion.

As far as runaway queries on a handle, then ... if you have minimal stats enabled in PG you can query and find out exactly what query has run away with your backend and AFTER having checked it out manually, kill -TERM your backend or let it run to completion, whichever you prefer.

I think manual administration of the DB in this instance is better than having the AOLserver database driver "guess" for you, no?

No, they aren't in monitoring, it would be nice if someone added pages to do this.

Ahem.

"I certainly wouldn't be sending any (OS) signals to the db server when shutting down AOLServer."

OK

"However I would expect the database driver to terminate it's connections to the dbms, which I would in turn expect to cause the running queries to terminate (like pressing Ctrl-C in a psql terminal)."

Sending an OS signal (SIGTERM) to the backend process is HOW psql terminates a query when you press Ctrl-C ...

And you're misreading the doc you cite, the backend isn't actively checking for a termination message, a new backend process is sent a termination message and it in turn bops the query-running process with a SIGTERM signal. Signals are CAUGHT, not POLLED ... all the doc sez is that the backend, while running a query, doesn't POLL to see if a terminate message has been sent but rather responds to a signal (which takes no overhead since the OS takes care of calling the catching function directly when it processes the signal).

Sending an OS signal (SIGTERM) to the backend process is HOW psql terminates a query when you press Ctrl-C ...

Doesn't look like it to me. As far as I can read from the source, psql sends a cancellation message to the server in another connection - which then probably sends a SIGTERM to the process.

And you're misreading the doc you cite, the backend isn't actively checking for a termination message, a new backend process is sent a termination message and it in turn bops the query-running process with a SIGTERM signal. Signals are CAUGHT, not POLLED ... all the doc sez is that the backend, while running a query, doesn't POLL to see if a terminate message has been sent but rather responds to a signal (which takes no overhead since the OS takes care of calling the catching function directly when it processes the signal).

I believe you misunderstood my use of the word "termination". I used it in the context of the postgresql protocol document, not OS signals.

As I read the doc:

Cancellation of a query happens by the client sending a message on another connection, as described above.

Termination of a connection happens either because of an explicit message sent from the client on the connection, OR when the backend discovers that the connection has been terminated by the client. I don't know how the last part happens, but apparently it is NOT happening during query execution, or at least it didn't for a couple of days on my production server.

/Nis

PS: Can we please have the option back to reply to a reply?

Don, yes it does make perfect sense to reply to a specific post in the openacs.org Forums. The Forums package keeps internal track of the tree reply structure of posts. And when the Forums package displays one single individual post, it also displays all children replies below it, tree-wise. Examples: one, two.

Of course, Nis, as this post demonstrates, if you display a post singly the "Post a Reply" then replies to that specific post rather than the first post in the thread, maintaining the threaded/tree structure. This seems like a reasonable compromise between specificity for power users and UI simplicity for newbies.