From d76a6d14c7f7473acf20313c73460b4d640fc0db Mon Sep 17 00:00:00 2001 From: Marcus Eggenberger Date: Mon, 31 Jan 2011 00:08:53 +0100 Subject: [PATCH] Reworking handling of Prepared Queries in PostgreSQL - Quasselcore should now survive PG restarts :) - Fixes Bug #1044 --- src/core/postgresqlstorage.cpp | 107 +++++++++++++-------------------- src/core/postgresqlstorage.h | 14 ++--- 2 files changed, 47 insertions(+), 74 deletions(-) diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index ba998ea5..84e6a05f 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -1198,13 +1198,6 @@ bool PostgreSqlStorage::logMessage(Message &msg) { return false; } - if(!prepareQuery("insert_message", queryString("insert_message"), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString("insert_message"); - qWarning() << " Error:" << db.lastError().text(); - db.rollback(); - return false; - } - QVariantList params; params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() @@ -1219,7 +1212,6 @@ bool PostgreSqlStorage::logMessage(Message &msg) { db.rollback(); db.transaction(); - // it's possible that the sender was already added by another thread // since the insert might fail we're setting a savepoint savePoint("sender_sp1", db); @@ -1258,12 +1250,6 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { return false; } - if(!prepareQuery("insert_sender", queryString("insert_sender"), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString("insert_sender"); - qWarning() << " Error:" << db.lastError().text(); - db.rollback(); - return false; - } QSet senders; for(int i = 0; i < msgs.count(); i++) { const QString &sender = msgs.at(i).sender(); @@ -1280,12 +1266,6 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { } // yes we loop twice over the same list. This avoids alternating queries. - if(!prepareQuery("insert_message", queryString("insert_message"), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString("insert_message"); - qWarning() << " Error:" << db.lastError().text(); - db.rollback(); - return false; - } bool error = false; for(int i = 0; i < msgs.count(); i++) { Message &msg = msgs[i]; @@ -1353,13 +1333,6 @@ QList PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, Ms else params << "ALL"; - if(!prepareQuery(queryName, queryString(queryName), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString(queryName); - qWarning() << " Error:" << db.lastError().text(); - db.rollback(); - return messagelist; - } - QSqlQuery query = executePreparedQuery(queryName, params, db); if(!watchQuery(query)) { @@ -1463,30 +1436,49 @@ bool PostgreSqlStorage::beginReadOnlyTransaction(QSqlDatabase &db) { return !query.lastError().isValid(); } -bool PostgreSqlStorage::prepareQuery(const QString &handle, const QString &query, const QSqlDatabase &db) { - if(_preparedQueries.contains(db.connectionName()) && _preparedQueries[db.connectionName()].contains(handle)) - return true; // already prepared +QSqlQuery PostgreSqlStorage::prepareAndExecuteQuery(const QString &queryname, const QString ¶mstring, const QSqlDatabase &db) { + // Query preparing is done lazily. That means that instead of always checking if the query is already prepared + // we just EXECUTE and catch the error + QSqlQuery query; - QMutexLocker locker(&_queryHashMutex); + db.exec("SAVEPOINT quassel_prepare_query"); + if(paramstring.isNull()) { + query = db.exec(QString("EXECUTE quassel_%1").arg(queryname)); + } else { + query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring)); + } - static unsigned int stmtCount = 0; - QString queryId = QLatin1String("quassel_") + QString::number(++stmtCount, 16); - // qDebug() << "prepare:" << QString("PREPARE %1 AS %2").arg(queryId).arg(query); - db.exec(QString("PREPARE %1 AS %2").arg(queryId).arg(query)); if(db.lastError().isValid()) { - return false; + // and once again: Qt leaves us without error codes so we either parse (language dependant(!)) strings + // or we just guess the error. As we're only interested in unprepared queries, this will be our guess. :) + db.exec("ROLLBACK TO SAVEPOINT quassel_prepare_query"); + QSqlQuery checkQuery = db.exec(QString("SELECT count(name) FROM pg_prepared_statements WHERE name = 'quassel_%1' AND from_sql = TRUE").arg(queryname.toLower())); + checkQuery.first(); + if(checkQuery.value(0).toInt() == 0) { + db.exec(QString("PREPARE quassel_%1 AS %2").arg(queryname).arg(queryString(queryname))); + if(db.lastError().isValid()) { + qWarning() << "PostgreSqlStorage::prepareQuery(): unable to prepare query:" << queryname << "AS" << queryString(queryname); + qWarning() << " Error:" << db.lastError().text(); + return QSqlQuery(db); + } + } + // we alwas execute the query again, even if the query was already prepared. + // this ensures, that the error is properly propagated to the calling function + // (otherwise the lasst call would be the test select to pg_prepared_statements + // which always gives a proper result) + if(paramstring.isNull()) { + query = db.exec(QString("EXECUTE quassel_%1").arg(queryname)); + } else { + query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring)); + } } else { - _preparedQueries[db.connectionName()][handle] = queryId; - return true; + // only release the SAVEPOINT + db.exec("RELEASE SAVEPOINT quassel_prepare_query"); } + return query; } -QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariantList ¶ms, const QSqlDatabase &db) { - if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { - qWarning() << "PostgreSqlStorage::executePreparedQuery() no prepared Query with handle" << handle << "on Database" << db.connectionName(); - return QSqlQuery(db); - } - +QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &queryname, const QVariantList ¶ms, const QSqlDatabase &db) { QSqlDriver *driver = db.driver(); QStringList paramStrings; @@ -1502,21 +1494,14 @@ QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const Q paramStrings << driver->formatValue(field); } - const QString &queryId = _preparedQueries[db.connectionName()][handle]; if(params.isEmpty()) { - return db.exec(QString("EXECUTE %1").arg(queryId)); + return prepareAndExecuteQuery(queryname, db); } else { - // qDebug() << "preparedExec:" << QString("EXECUTE %1 (%2)").arg(queryId).arg(paramStrings.join(", ")); - return db.exec(QString("EXECUTE %1 (%2)").arg(queryId).arg(paramStrings.join(", "))); + return prepareAndExecuteQuery(queryname, paramStrings.join(", "), db); } } -QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariant ¶m, const QSqlDatabase &db) { - if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { - qWarning() << "PostgreSqlStorage::executePreparedQuery() no prepared Query with handle" << handle << "on Database" << db.connectionName(); - return QSqlQuery(db); - } - +QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &queryname, const QVariant ¶m, const QSqlDatabase &db) { QSqlField field; field.setType(param.type()); if(param.isNull()) @@ -1524,20 +1509,12 @@ QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const Q else field.setValue(param); - const QString &queryId = _preparedQueries[db.connectionName()][handle]; QString paramString = db.driver()->formatValue(field); - - // qDebug() << "preparedExec:" << QString("EXECUTE %1 (%2)").arg(queryId).arg(paramString); - return db.exec(QString("EXECUTE %1 (%2)").arg(queryId).arg(paramString)); + return prepareAndExecuteQuery(queryname, paramString, db); } -void PostgreSqlStorage::deallocateQuery(const QString &handle, const QSqlDatabase &db) { - if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { - return; - } - QMutexLocker locker(&_queryHashMutex); - QString queryId = _preparedQueries[db.connectionName()].take(handle); - db.exec(QString("DEALLOCATE %1").arg(queryId)); +void PostgreSqlStorage::deallocateQuery(const QString &queryname, const QSqlDatabase &db) { + db.exec(QString("DEALLOCATE quassel_%1").arg(queryname)); } // ======================================== diff --git a/src/core/postgresqlstorage.h b/src/core/postgresqlstorage.h index 5b6a196f..83b24e22 100644 --- a/src/core/postgresqlstorage.h +++ b/src/core/postgresqlstorage.h @@ -117,10 +117,9 @@ protected: bool beginReadOnlyTransaction(QSqlDatabase &db); - bool prepareQuery(const QString &handle, const QString &query, const QSqlDatabase &db); - QSqlQuery executePreparedQuery(const QString &handle, const QVariantList ¶ms, const QSqlDatabase &db); - QSqlQuery executePreparedQuery(const QString &handle, const QVariant ¶m, const QSqlDatabase &db); - void deallocateQuery(const QString &handle, const QSqlDatabase &db); + QSqlQuery executePreparedQuery(const QString &queryname, const QVariantList ¶ms, const QSqlDatabase &db); + QSqlQuery executePreparedQuery(const QString &queryname, const QVariant ¶m, const QSqlDatabase &db); + void deallocateQuery(const QString &queryname, const QSqlDatabase &db); inline void savePoint(const QString &handle, const QSqlDatabase &db) { db.exec(QString("SAVEPOINT %1").arg(handle)); } inline void rollbackSavePoint(const QString &handle, const QSqlDatabase &db) { db.exec(QString("ROLLBACK TO SAVEPOINT %1").arg(handle)); } @@ -129,17 +128,14 @@ protected: private: void bindNetworkInfo(QSqlQuery &query, const NetworkInfo &info); void bindServerInfo(QSqlQuery &query, const Network::Server &server); + QSqlQuery prepareAndExecuteQuery(const QString &queryname, const QString ¶mstring, const QSqlDatabase &db); + inline QSqlQuery prepareAndExecuteQuery(const QString &queryname, const QSqlDatabase &db) { return prepareAndExecuteQuery(queryname, QString(), db); } QString _hostName; int _port; QString _databaseName; QString _userName; QString _password; - - typedef QHash QueryHash; - QHash _preparedQueries; // one query hash per db connection - QMutex _queryHashMutex; - }; inline void PostgreSqlStorage::safeExec(QSqlQuery &query) { query.exec(); } -- 2.20.1