Skip to content

Commit 5eaa4a8

Browse files
author
jef
committedMar 25, 2008
reintroduce connection pooling, but use cursors independantly of transactions
by declaring them "WITH HOLD" and "CLOSE" them when done. git-svn-id: http://svn.osgeo.org/qgis/trunk@8273 c8812cc2-4d05-0410-92ff-de0c093fc19c
·
ltr-3_40Before-merge-new_vector_api
1 parent be1a955 commit 5eaa4a8

File tree

2 files changed

+101
-69
lines changed

2 files changed

+101
-69
lines changed
 

‎src/providers/postgres/qgspostgresprovider.cpp

Lines changed: 87 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,9 @@
5858
const QString POSTGRES_KEY = "postgres";
5959
const QString POSTGRES_DESCRIPTION = "PostgreSQL/PostGIS data provider";
6060

61+
QMap<QString, QgsPostgresProvider::Conn *> QgsPostgresProvider::connections;
62+
int QgsPostgresProvider::providerIds=0;
63+
6164
QgsPostgresProvider::QgsPostgresProvider(QString const & uri)
6265
: QgsVectorDataProvider(uri),
6366
geomType(QGis::WKBUnknown),
@@ -67,10 +70,7 @@ QgsPostgresProvider::QgsPostgresProvider(QString const & uri)
6770
// assume this is a valid layer until we determine otherwise
6871
valid = true;
6972

70-
// Make connection to the data source
71-
// For postgres, the connection information is passed as a space delimited
72-
// string:
73-
// host=192.168.1.5 dbname=test port=5342 user=gsherman password=xxx table=tablename
73+
providerId=providerIds++;
7474

7575
QgsDebugMsg("Postgresql Layer Creation");
7676
QgsDebugMsg("URI: " + uri);
@@ -234,8 +234,6 @@ QgsPostgresProvider::QgsPostgresProvider(QString const & uri)
234234
QgsDebugMsg("Main thread just dispatched mCountThread");
235235
#endif
236236

237-
ready = false; // not ready to read yet cuz the cursor hasn't been created
238-
239237
//fill type names into sets
240238
mSupportedNativeTypes.insert("double precision");
241239
mSupportedNativeTypes.insert("int4");
@@ -283,6 +281,13 @@ QgsPostgresProvider::~QgsPostgresProvider()
283281

284282
PGconn *QgsPostgresProvider::connectDb(const QString & conninfo)
285283
{
284+
if( connections.contains(conninfo) )
285+
{
286+
QgsDebugMsg( QString("Using cached connection for %1").arg(conninfo) );
287+
connections[conninfo]->ref++;
288+
return connections[conninfo]->conn;
289+
}
290+
286291
QgsDebugMsg(QString("New postgres connection for ") + conninfo);
287292

288293
PGconn *pd = PQconnectdb(conninfo.toLocal8Bit()); // use what is set based on locale; after connecting, use Utf8
@@ -323,14 +328,36 @@ PGconn *QgsPostgresProvider::connectDb(const QString & conninfo)
323328
"work properly.\nPlease install PostGIS with "
324329
"GEOS support (http://geos.refractions.net)"));
325330
}
326-
//--std::cout << "Connection to the database was successful\n";
331+
332+
QgsDebugMsg("Connection to the database was successful");
333+
334+
Conn *conn = new Conn(pd);
335+
connections.insert( conninfo, conn );
327336

328337
return pd;
329338
}
330339

331340
void QgsPostgresProvider::disconnectDb()
332341
{
333-
PQfinish( connection );
342+
if(mFetching)
343+
{
344+
PQexecNR(connection, QString("CLOSE qgisf%1").arg(providerId).toUtf8() );
345+
mFetching=false;
346+
}
347+
348+
QMap<QString, Conn *>::iterator i;
349+
for(i=connections.begin(); i!=connections.end() && i.value()->conn!=connection; i++)
350+
;
351+
352+
assert( i.value()->conn==connection );
353+
assert( i.value()->ref>0 );
354+
355+
if( --i.value()->ref==0 ) {
356+
PQfinish( connection );
357+
delete i.value();
358+
connections.remove( i.key() );
359+
}
360+
334361
connection = 0;
335362
}
336363

@@ -341,14 +368,17 @@ QString QgsPostgresProvider::storageType() const
341368

342369
bool QgsPostgresProvider::getNextFeature(QgsFeature& feature)
343370
{
371+
assert(mFetching);
372+
344373
if (valid)
345374
{
346375

347376
// Top up our queue if it is empty
348377
if (mFeatureQueue.empty())
349378
{
350-
QString fetch = QString("fetch forward %1 from qgisf")
351-
.arg(mFeatureQueueSize);
379+
QString fetch = QString("fetch forward %1 from qgisf%2")
380+
.arg(mFeatureQueueSize)
381+
.arg(providerId);
352382

353383
if(mFirstFetch)
354384
{
@@ -368,9 +398,7 @@ bool QgsPostgresProvider::getNextFeature(QgsFeature& feature)
368398
QgsDebugMsg("End of features");
369399

370400
PQclear(queryResult);
371-
if (ready)
372-
PQexecNR(connection, QString("end work").toUtf8());
373-
ready = false;
401+
374402
return false;
375403
}
376404

@@ -499,7 +527,7 @@ void QgsPostgresProvider::select(QgsAttributeList fetchAttributes,
499527
QgsFieldMap attributeMap = fields();
500528
QgsFieldMap::const_iterator fieldIt;
501529
for(QgsAttributeList::const_iterator it = mAttributesToFetch.constBegin();
502-
it != mAttributesToFetch.constEnd(); ++it)
530+
it != mAttributesToFetch.constEnd(); ++it)
503531
{
504532
fieldIt = attributeMap.find(*it);
505533
if(fieldIt != attributeMap.end())
@@ -508,14 +536,22 @@ void QgsPostgresProvider::select(QgsAttributeList fetchAttributes,
508536
}
509537
}
510538

511-
QString declare = "declare qgisf binary cursor for select " + quotedIdentifier(primaryKey);
539+
if(mFetching)
540+
{
541+
PQexecNR(connection, QString("CLOSE qgisf%1").arg(providerId).toUtf8() );
542+
mFetching=false;
543+
}
544+
545+
QString declare = QString("declare qgisf%1 binary cursor with hold for select %2")
546+
.arg(providerId).arg(quotedIdentifier(primaryKey));
512547

513548
if(fetchGeometry)
514549
{
515550
declare += QString(",asbinary(%1,'%2') as qgs_feature_geometry")
516-
.arg( quotedIdentifier(geometryColumn) )
517-
.arg( endianString() );
551+
.arg( quotedIdentifier(geometryColumn) )
552+
.arg( endianString() );
518553
}
554+
519555
for(std::list<QString>::const_iterator it = mFetchAttributeNames.begin(); it != mFetchAttributeNames.end(); ++it)
520556
{
521557
if( (*it) != primaryKey) //no need to fetch primary key again
@@ -570,18 +606,14 @@ void QgsPostgresProvider::select(QgsAttributeList fetchAttributes,
570606

571607
QgsDebugMsg("Selecting features using: " + declare);
572608

573-
// set up the cursor
574-
if(ready){
575-
PQexecNR(connection, QString("end work").toUtf8());
576-
}
577-
PQexecNR(connection,QString("begin work").toUtf8());
578-
ready = true;
579609
PQexecNR(connection, declare.toUtf8());
580-
610+
581611
while(!mFeatureQueue.empty())
582-
{
583-
mFeatureQueue.pop();
584-
}
612+
{
613+
mFeatureQueue.pop();
614+
}
615+
616+
mFetching = true;
585617
mFirstFetch = true;
586618
}
587619

@@ -605,40 +637,41 @@ bool QgsPostgresProvider::getFeatureAtId(int featureId,
605637
}
606638
}
607639

608-
QString sql = "declare qgisfid binary cursor for select " + quotedIdentifier(primaryKey);
640+
QString declare = QString("declare qgisfid%1 binary cursor with hold for select %2")
641+
.arg(providerId).arg(quotedIdentifier(primaryKey));
609642

610643
if(fetchGeometry)
611644
{
612-
sql += QString(",asbinary(%1,'%2') as qgs_feature_geometry")
613-
.arg( quotedIdentifier(geometryColumn) )
614-
.arg( endianString() );
645+
declare += QString(",asbinary(%1,'%2') as qgs_feature_geometry")
646+
.arg( quotedIdentifier(geometryColumn) )
647+
.arg( endianString() );
615648
}
649+
616650
for(namesIt = attributeNames.begin(); namesIt != attributeNames.end(); ++namesIt)
617651
{
618652
if( (*namesIt) != primaryKey) //no need to fetch primary key again
619653
{
620-
sql += "," + quotedIdentifier(*namesIt) + "::text";
654+
declare += "," + quotedIdentifier(*namesIt) + "::text";
621655
}
622656
}
623657

624-
sql += " " + QString("from %1").arg(mSchemaTableName);
625-
626-
sql += " where " + quotedIdentifier(primaryKey) + "=" + QString::number(featureId);
658+
declare += QString(" from %1 where %2=%3")
659+
.arg(mSchemaTableName)
660+
.arg(quotedIdentifier(primaryKey))
661+
.arg(featureId);
627662

628-
QgsDebugMsg("Selecting feature using: " + sql);
629-
630-
PQexecNR(connection,QString("begin work").toUtf8());
663+
QgsDebugMsg("Selecting feature using: " + declare);
631664

632665
// execute query
633-
PQexecNR(connection, sql.toUtf8());
666+
PQexecNR(connection, declare.toUtf8());
634667

635-
PGresult *res = PQexec(connection, QString("fetch forward 1 from qgisfid").toUtf8());
668+
PGresult *res = PQexec(connection, QString("fetch forward 1 from qgisfid%1").arg(providerId).toUtf8());
636669

637670
int rows = PQntuples(res);
638671
if (rows == 0)
639672
{
640673
PQclear(res);
641-
PQexecNR(connection, QString("end work").toUtf8());
674+
PQexecNR(connection, QString("CLOSE qgisfid%1").arg(providerId).toUtf8());
642675
QgsDebugMsg("feature " + QString::number(featureId) + " not found");
643676
return FALSE;
644677
}
@@ -710,7 +743,7 @@ bool QgsPostgresProvider::getFeatureAtId(int featureId,
710743
}
711744

712745
PQclear(res);
713-
PQexecNR(connection, QString("end work").toUtf8());
746+
PQexecNR(connection, QString("CLOSE qgisfid%1").arg(providerId).toUtf8());
714747

715748
return TRUE;
716749
}
@@ -770,8 +803,11 @@ QString QgsPostgresProvider::dataComment() const
770803

771804
void QgsPostgresProvider::reset()
772805
{
773-
QString move = "move 0 in qgisf"; //move cursor to first record
774-
PQexecNR(connection, move.toUtf8());
806+
if(mFetching)
807+
{
808+
//move cursor to first record
809+
PQexecNR(connection, QString("move 0 in qgisf%1").arg(providerId).toUtf8());
810+
}
775811
mFeatureQueue.empty();
776812
loadFields();
777813
}
@@ -852,9 +888,6 @@ void QgsPostgresProvider::loadFields()
852888
fieldComment = QString::fromUtf8(PQgetvalue(tresult, 0, 0));
853889
PQclear(tresult);
854890

855-
QgsDebugMsg("Field: " + attnum + " maps to " + QString::number(i) + " " + fieldName + ", "
856-
+ fieldTypeName + " (" + QString::number(fldtyp) + "), " + fieldSize + ", " + QString::number(fieldModifier));
857-
858891
if(fieldName!=geometryColumn)
859892
{
860893
QVariant::Type fieldType;
@@ -1906,10 +1939,8 @@ bool QgsPostgresProvider::addFeatures(QgsFeatureList & flist)
19061939
appendGeomString( features->geometry(), geomParam);
19071940

19081941
QList<QByteArray> qparam;
1909-
19101942
qparam.append( geomParam.toUtf8() );
19111943
qparam.append( QString("%1").arg( ++primaryKeyHighWater ).toUtf8() );
1912-
19131944
param[0] = qparam[0];
19141945
param[1] = qparam[1];
19151946

@@ -2446,16 +2477,14 @@ bool QgsPostgresProvider::deduceEndian()
24462477

24472478
// get the same value using a binary cursor
24482479

2449-
PQexecNR(connection,QString("begin work").toUtf8());
2450-
QString oidDeclare = "declare oidcursor binary cursor for select regclass('" + mSchemaTableName + "')::oid";
2480+
QString oidDeclare = "declare oidcursor binary cursor with hold for select regclass('" + mSchemaTableName + "')::oid";
24512481
// set up the cursor
24522482
PQexecNR(connection, oidDeclare.toUtf8());
24532483
QString fetch = "fetch forward 1 from oidcursor";
24542484

24552485
QgsDebugMsg("Fetching a record and attempting to get check endian-ness");
24562486

24572487
PGresult *fResult = PQexec(connection, fetch.toUtf8());
2458-
PQexecNR(connection, QString("end work").toUtf8());
24592488
swapEndian = true;
24602489
if(PQntuples(fResult) > 0){
24612490
// get the oid value from the binary cursor
@@ -2469,6 +2498,7 @@ bool QgsPostgresProvider::deduceEndian()
24692498

24702499
PQclear(fResult);
24712500
}
2501+
PQexecNR(connection, QString("close oidcursor").toUtf8());
24722502
return swapEndian;
24732503
}
24742504

@@ -2643,16 +2673,15 @@ void QgsPostgresProvider::PQexecNR(PGconn *conn, const char *query)
26432673
PGresult *res = PQexec(conn, query);
26442674
if(res)
26452675
{
2646-
QgsDebugMsg( QString("Query: %1 returned %2 [%3]")
2647-
.arg(query)
2648-
.arg(PQresStatus(PQresultStatus(res)))
2649-
.arg(PQresultErrorMessage(res))
2650-
);
2676+
QgsDebugMsgLevel( QString("Query: %1 returned %2 [%3]")
2677+
.arg(query)
2678+
.arg(PQresStatus(PQresultStatus(res)))
2679+
.arg(PQresultErrorMessage(res)), 3 );
26512680
PQclear(res);
26522681
}
26532682
else
26542683
{
2655-
QgsDebugMsg( QString("Query: %1 returned no result buffer").arg(query) );
2684+
QgsDebugMsgLevel( QString("Query: %1 returned no result buffer").arg(query), 3 );
26562685
}
26572686
}
26582687

‎src/providers/postgres/qgspostgresprovider.h

Lines changed: 14 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -290,7 +290,6 @@ class QgsPostgresProvider:public QgsVectorDataProvider
290290
*/
291291
QString name() const;
292292

293-
294293
/** return description
295294
296295
Return a terse string describing what the provider is.
@@ -304,12 +303,7 @@ class QgsPostgresProvider:public QgsVectorDataProvider
304303
*/
305304
QString description() const;
306305

307-
308-
309-
310-
311-
312-
signals:
306+
signals:
313307
/**
314308
* This is emitted whenever the worker thread has fully calculated the
315309
* PostGIS extents for this layer, and its event has been received by this
@@ -330,6 +324,7 @@ class QgsPostgresProvider:public QgsVectorDataProvider
330324
void repaintRequested();
331325

332326
private:
327+
int providerId; // id to append to provider specific identified (like cursors)
333328

334329
/** Double quote a PostgreSQL identifier for placement in a SQL string.
335330
*/
@@ -343,7 +338,8 @@ class QgsPostgresProvider:public QgsVectorDataProvider
343338
*/
344339
void loadFields();
345340

346-
bool mFirstFetch; //true if fetch forward is called the first time after select
341+
bool mFetching; // true if a cursor was declared
342+
bool mFirstFetch; // true if fetch forward is called the first time after select
347343
std::vector < QgsFeature > features;
348344
QgsFieldMap attributeFields;
349345
QString mDataComment;
@@ -547,9 +543,6 @@ class QgsPostgresProvider:public QgsVectorDataProvider
547543
int SRCFromViewColumn(const QString& ns, const QString& relname, const QString& attname_table,
548544
const QString& attname_view, const QString& viewDefinition, SRC& result) const;
549545

550-
bool ready;
551-
std::ofstream pLog;
552-
553546
//! PostGIS version string
554547
QString postgisVersionInfo;
555548

@@ -601,6 +594,16 @@ class QgsPostgresProvider:public QgsVectorDataProvider
601594

602595
// run a query and free result buffer
603596
static void PQexecNR(PGconn *conn, const char *query);
597+
598+
struct Conn
599+
{
600+
Conn(PGconn *connection) : ref(1), conn(connection) {}
601+
602+
int ref;
603+
PGconn *conn;
604+
};
605+
static QMap<QString, Conn *> connections;
606+
static int providerIds;
604607
};
605608

606609
#endif

0 commit comments

Comments
 (0)
Please sign in to comment.