Forum OpenACS Development: Patched postgres.so shows SQL error messages in TCL error dump

Perhaps you, like me, are tired of seeing:

Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")

and then experiencing the joy of opening another shell, remembering where the log file is for this particular service, opening it, finding it's too big to open, tailing or lessing it, failing to remember the appropriate search command, and then wading through hundreds of lines of irrelevant debris to find a one-line error message. (oh, and the extra hundreds of lines that notifications spews every fifteen seconds! shut up, notifications! I'm coming for your overly verbose logging next!). Anyway, there are several parallel patch sets that fix this problem and return the SQL error through proper channels. The most recent postgres driver, now called nspostgres, is so patched, and is included in Mat Kovach's new distribution, aolserver3.3oacs1.tar.gz. I'm updating the install docs to take advantage of this.

Meanwhile, though, if you are running postgres on linux and want the fix, you can experiment with a patched, compiled binary:

cd /tmp
wget http://aufrecht.org/postgres.so
shut down your web server
cd /usr/local/aolserver
mv postgres.so postgres.so.original
cp /tmp/postgres.so .
start your web server

I'm not responsible for anything that might happen next, but you can reverse it very easily.

Feel free to stomp on notifications ...

It shows you the full SQL error message and complete post-bind query in the TCL error string which gets dumped to the web page. So you don't have to go to the log to find it.

Before

Request Error

Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {

	select first_name || ' ' || last_name as name, email
	from persons, parties
	where person_id = :user_id
	and person_id = ..."
    ("uplevel" body line 1)
    invoked from within

After

Request Error

Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")

ERROR:  Attribute 'first_name' not found

SQL: 

	select first_name || ' ' || last_name as name, email
	from persons, parties
	where person_id = '2604'
	and person_id = party_id

    
    while executing
"ns_pg_bind 0or1row nsdb0 {

	select first_name || ' ' || last_name as name, email
	from persons, parties
	where person_id = :user_id
	and person_id = ..."
    ("uplevel" body line 1)

Also, a mistake in my instructions - postgres.so is in /usr/local/aolserver/bin.

Mat's aolserver3.3oacs1.tar.gz uses current CVS code as of 20030309. It generates nspostgres.so. That file correctly passes SQL error messages to TCL pages in OpenACS. I have updated the documentation to use that distribution.

Since using nspostgres.so requires changes to config files, I also linked to a binary of the earlier driver, postgres.so, containing the patches.

So the nspostgres from Mat's tarball is correct, and he says he got it from aolserver's cvs.