[Qt-interest] Postgresql-8.3 blocking with qt 3.3.3 - SOLVED

[John A. Sullivan III]

On Fri, 2008-12-26 at 20:49 -0500, John A. Sullivan III wrote:
> Hello, all.  I'm having a bizarre problem with postgresql-8.3 and qt
> 3.3.3 on Ubuntu 8.0.4 fully patched.  That's my troubleshooting
> environment but we originally saw this problem on RHEL 5.2.  We are
> using the QPSQL7 driver.  The database has a public schema for shared
> production use and each administrator creates a copy of the public
> schema for edits and then merges those edits into production at the end
> of their session.  This is for the open source network security
> management project ISCS (http://iscs.sourceforge.net).
> 
> During the update of the security gateways, we spawn several threads to
> apply the updates.  When the thread terminates, it creates a custom
> event in the main event loop.  The event handler reads the database
> version from the thread and updates the public (production) schema with
> the value.  The update is made using a QSqlQuery object with very simple
> SQL.  The actual query does not seem to matter.  As soon as this query
> executes, the public schema seems to block.  It query returns true, the
> code logic progresses, but the change has not been made (it is a single
> change not bounded by a transaction) and any subsequent action upon the
> public schema fails.  Actions upon the edit schema appear to work fine.
> Previous updates using the same object work perfectly fine.  Only this
> one fails.  The only unique characteristic is it is called within the
> custom event handler upon thread termination. The threads open their own
> QSqlDatabase and QSqlQuery objects.
> 
> I've tracked it down to this specific query by running an independent
> instance of psql from a terminal.  As I step through the code, line by
> line, I begin a transaction, do a simple update to the public schema and
> then rollback the transaction.  It works fine up until I execute the
> QSqlQuery.  The query in the code is very simple:
> 
> update peps set dbversion=6, current=true where pep='WessonSGGW';
> 
> I can run it within a transaction in psql by simply cutting and pasting
> from the debugger.  I even coded an even simpler update in its place:
> 
> "update peps set comment='fudge' where pep='" + PEPDB + "';"
> 
> and it fails just the same.
> 
> If I change the update to a select statement, it does not block.  If I
> leave the test update from psql running (blocked), and then exit the
> ISCS application, the update completes.  This also implies it is not
> just blocking within the application; it is blocking the entire public
> schema from any access, e.g., command line psql.  Even stranger, the
> update blocks itself, i.e., it never completes even though
> QSqlQuery::exec() returns true!
> 
> I have no idea of what to do next.  Here is the pertinent code snippet:
> 
> void SPMMainForm::customEvent( QCustomEvent * ce )
> {
> . . .
> 	if (ce->type() == 10002) { // This is a PEPUpdateThread termination event
> 		QListViewItem * LVI;
> 		int MaxThreads;
> 		bool success, NoErrors, SQTempExists = (so->SQEdit != 0);
> 		QSqlQuery SQTemp( QString::null, SQTempExists ? so->DbEdit : so->DbProd );
> //		SQTemp.setForwardOnly(true); // Awaiting a bug fix for ForwardOnly using MySQL
> 		UpdateThread * puth = (UpdateThread *)ce->data();
> 		puth = static_cast<UpdateThread *>(ce->data());
> 		if ( puth->running()) {
> 			puth->wait();
> 		}
> 		QString PEPDB;
> 		PEPDB = QDeepCopy<QString>(puth->PEP);
> 		PEPDB = PEPDB.replace("'", "''");
> 		NoErrors = (puth->ErrorCode.isEmpty() || (puth->ErrorCode.left(20) == "[PEP Update Warning]"));
> 		if (NoErrors || puth->Retry) { // either there was no error or we errored after a retry
> 			
> //			so->ThreadCounter->lock();
> 			so->OpenUpdateThreads--; // keeping track of open threads so we know when we are done
> //			so->ThreadCounter->unlock();
> 			
> 			// Update the dbVersion in the peps database table
> 			if ( ! so->SQProd->exec("update peps set dbversion=" + QString::number(puth->PEPDbVersion) + ", current=" + (puth->ErrorCode.isEmpty() ? so->True : so->False) + " where pep='" + PEPDB + "';")) {
> 				success = false;
> 
> 
> Can anyone please point me in the right direction? I was delighted to
> port this application from MySQL and really do not want to revert back
> to MySQL only.  Thanks - John
<snip>
Hello, all.  It was a brain cramp on my part (as expected!).  The logic
had a hole where, in an unusual set of circumstances where there were no
database changes to commit, a transaction was opened on the public
schema and not committed or rolled back.  The transaction hanging open
was causing the block.  Once I eliminated the dangling transactions, all
worked.  Sorry for the intrusion - John
-- 
John A. Sullivan III
Open Source Development Corporation
+1 207-985-7880
jsullivan at opensourcedevel.com

http://www.spiritualoutreach.com
Making Christianity intelligible to secular society