Libpqxx connection pool

Question

I am trying to develop a very simple and straightforward connection pool using the libpqxx library. I am quite new to c++ and still very confused with pointers and referencing. The behaviour of the class is very simple: have a vector with some initialized connection, and pop and push connections onto the vector when they are needed. The code has many errors due to the bad implementation of pointers and referencing. Can you please give me some hints?

EDIT: I managed to fix all compilation errors. It is giving me a segmentation fault when I run the main function.

class DbPool {

public:

pqxx::result runQuery(const string& query) {

    connection *conn = getCon();
    work trans(*conn);
    result res = trans.exec(query);
    trans.commit();
    releaseCon(conn);

    return res;
}

DbPool(uint32_t max_cons) {

    for (uint32_t i = 0; i < max_cons; i++) {

        connection* con = createCon();
        m_freeCons.push_back(shared_ptr < connection > (con));
    }
}

private:

connection * createCon() {

    connection * conn =
            new connection(
                    "user='ak' password='rootpassword' dbname='bips_office' hostaddr='127.0.0.1' port='5432'");
    return conn;
}

void releaseCon(connection *con) {

    m_freeCons.push_back(shared_ptr < connection > (con));
}

connection* getCon() {

    shared_ptr < connection > conn = *(m_freeCons.end() - 1);
    m_freeCons.pop_back();
    return conn.get();
}

vector<shared_ptr<connection> > m_freeCons;

};

int main(int argc, char *argv[]) {
     DbPool *pool = new DbPool(5);
     result res = pool->runQuery("SELECT COUNT (*) from captures");
     return 0;
}

"The code has many error" - do you mean compilation errors, or runtime errors which have some effect on your program run, or what do you mean exactly? Or do you mean design flaws? Please clarify — codeling, Sep 21 '12 at 09:56
yes compilation errors due to the design flaws from my inexperience with pointers — JoaoFLF, Sep 21 '12 at 10:14

score 2 · Answer 1 · edited May 23 '17 at 12:34

If design problems are your worry, here are my five cents:

Does your code actually compile? I think not, because you're having a vector of pointers to connection, but you are pushing back connection objects (m_freeCons.push_back(*con); - the * dereferences the pointer to connection)...
It's usually a bad idea to give out modifiable handles to members (as you are doing in getCon method - at the least, return a connection const * instead if possible
If you have to use a collection of pointers, consider using shared_ptr instead of raw pointers - that way you won't have to worry about freeing memory; or use Boost.PointerContainer; also see here.
Just a matter of strange style: Why do you use return &(*conn)? That dereferences the pointer conn and then takes it's address again. Instead, you can simply write return conn!

In answer to your re-written question with shared_ptrs: You still need to create the connection with new, and wrap a shared_ptr around it; e.g. for createCon:

connection * createCon(){

    connection * conn = new connection("user='ak' password='rootpassword' dbname='bips_office' hostaddr='127.0.0.1' port='5432'");
    return conn;
}

and

    connection* con = createCon();
    m_freeCons.push_back(shared_ptr<connection>(con));

and analogously in the other places.

Do you know of some example that creates a vector of shared_ptr and performs operations such as get the last object, fill the vector and perform push and pop — JoaoFLF, Sep 21 '12 at 10:40
I added some correction for one of the error messages you're seeing, the others should go analogously, but please check a tutorial on shared_ptr instead of sequentially updating your question - this site is for specific questions with one solution, not for fixing changing and not-compiling code. — codeling, Sep 21 '12 at 10:46
I have checked some tutorials and managed to fix all compilation errors. Can you check my edit above? — JoaoFLF, Sep 21 '12 at 11:18
well if it compiles alright, and runs alright, what's there to check? just "looking over it" is something for http://codereview.stackexchange.com/ — codeling, Sep 21 '12 at 11:59

score 1 · Answer 2 · answered Jan 01 '16 at 19:41

Inspired by your post I used this simple connection pool in my own project. I used std::stack. to push and pop. No error checking except for a try-catch when adding the connections. For a postgresql-db.

Database.hpp:

#include <stack>
#include <pqxx/pqxx>

class Database {
private:
    const std::string connectionString = "dbname=db user=usr hostaddr=127.0.0.1 port=5432";
    std::stack<pqxx::connection *> dbpool;

public:
    Database(const unsigned int);

Database.cpp:

#include "Database.hpp"

Database::Database(const unsigned int connections) {
    for (int i = 0; i < connections; ++i) {
        try {
            auto* dbconn = new pqxx::connection(connectionString);
            dbpool.emplace(dbconn);
        } catch (const std::exception& e) {
            std::cerr << e.what() << std::endl;
        }
    }
}

main.cpp:

#include "Database.hpp"

int main(int argc, char* argv[]) {
    Database database {10};

    auto *D = dbpool.top();
    dbpool.pop();

    const std::string query = "select * from mytable";
    pqxx::nontransaction N(*D);
    pqxx::result R(N.exec(query));

    for (pqxx::result::const_iterator c = R.begin(); c != R.end(); ++c) {
        std::cout << c[1].as<std::string>() << std::endl;
    };

    dbpool.push(D);
}

instead of `pqxx::connection` better to use `pqxx::lazyconnection`. and why did you pop in main ? — Krcn U, Apr 20 '16 at 11:46
I used pqxx::connection to connect right away. Then the delay is minimal when acquiring a connection from the stack. top() gives you a reference to the top item in the stack and pop() removes it. if you don't remove it another connection might acquire the same connection. This code is not thread safe though but must use mutexes to achieve that which I have done in later revisions. — kometen, Apr 20 '16 at 12:10

Libpqxx connection pool

2 Answers2