pgsql_coonection_pool.h

#ifndef pgsql_connection_pool_H
#define pgsql_connection_pool_H

//this functions gives out the first available connection

shared_ptr<lazyconnection> pgsql_connection_pickup()
{
	//lock access to global variables 
	std::unique_lock<std::mutex> lock_(pgsql_pool_mutex);

	
	//if pool is empty, we have no connection available, so we wait until a connection become available
		while (conList.empty()) {
			pgsql_pool_condition.wait(lock_);//wait for notfiy
		}//while

		//when program reachs this point, we have an available connection in pool

		//get first element (connection) in the queue
		auto conn_ = conList.front();

		//remove first element from queue
		conList.pop();
		return conn_;

}//pgsql_connection_pickup


void hand_back_connection(std::shared_ptr<lazyconnection> conn_)
{
	//lock access to global variables
	std::unique_lock<std::mutex> lock_(pgsql_pool_mutex);

	//hand back connection into pool
	conList.push(conn_);

	// unlock mutex
	lock_.unlock();

	//notify one of thread that is waiting
	pgsql_pool_condition.notify_one();

	return;
}//hand_back_connection



bool setup_pgsql_connection_pool(string database_info)
{
	bool can_connect = false;
	int i = 0;

	//check if wen can connect to database
	try
	{
		connection C(database_info);

		if (C.is_open())
		{
			can_connect = true;

			//close database connection
			C.disconnect();
		}
		else {
			cout << "Can't open database" << endl;
			can_connect = false;
		}//if
	}
	catch (const std::exception& e)
	{
		cout << "ERROR:" << endl;
		cerr << e.what() << std::endl;
		can_connect = false;
	}//catch


	if (can_connect)//we have access to database so we start creating connections for pool
	{
		for (i = 0; i < max_connections; i++)
		{
			
			try
			{
				//we make lazy connection, so it will connect/disconnect when needed
				//when we use lazyconnection we dont have to monitor connections
				//but if you want to use basic connection, you need
				//to monitor them, because it does not connect/disconnect automatically

				conList.emplace(std::make_shared<lazyconnection>(database_info));
			}
			catch (const std::exception& e)
			{
				cerr << e.what() << std::endl;
			}

		}//for

	}//if can_connect

	return can_connect;

}//setup_pgsql_connection_pool





#endif

main.cpp

/*
Postgresql Connection pool for using inside the c++ code
this is a connection pool that makes connections as many as you desired
and give access to threads for making queries

Written By Mehdi.sadighian
mehdi.sadighian@hotmail.com
*/

#include <iostream>
#include <algorithm> 
#include <libpq-fe.h>
#include <string.h>
#include <unistd.h>
#include <stdio.h>
#include <memory>
#include <condition_variable>
#include <queue>
#include <thread>
#include <pqxx/pqxx> 
#include <mutex>



using namespace std;
using namespace pqxx;

//global variables

//how many connections will be in the postgresql connection pool
int max_connections = 8;

//create mutex for concurrent Read/Write access to conList
mutex pgsql_pool_mutex;

//condition variable to notify threads waiting that we have one free connection
condition_variable pgsql_pool_condition;

//queue to store pgsql connections
queue<std::shared_ptr<lazyconnection>> conList;


#include "pgsql_connection_pool.h"


void insert_pgsql(string query) {

	const char* sql;
	bool success = true;

	sql = query.c_str();

	auto conn = pgsql_connection_pickup();

	//if we dont have nullptr in conn
	if (conn)
	{
		try
		{
			// create a transaction from a connection
			work W(reinterpret_cast<lazyconnection&>(*conn.get()));

			try {

				/* Execute SQL query */
				W.exec(sql);

			}//try
			catch (const std::exception& e) {
				cout << "insert ERROR:" << endl;
				cerr << e.what() << std::endl;
				success = false;
			}//catch

			if (success)
			{
				try
				{
					W.commit();
				}
				catch (const std::exception& e) {
					cout << "commit ERROR:" << endl;
					cerr << e.what() << std::endl;
					W.abort();
				}
			}
			else
			{
				cout << "success ERROR:" << endl;
				W.abort();
			}//else

		}//try
		catch (const std::exception& e) {
			cout << "Work Create ERROR:" << endl;
			cerr << e.what() << std::endl;
			success = false;
		}//catch

			//hand back pgsql_connection
			hand_back_connection(conn);

	}//if conn
	else {
		cout << "Can't open database" << endl;
		success = false;
	}

	return;
}//insert_pgsql

int main()
{
	
	std::vector<std::thread> threadList;
	bool database_connected = false;
	string database_info = "dbname = TESTDB user = mehdi password = medhipassword \
        hostaddr = 127.0.0.1 port = 5432 connect_timeout=6";

	while (!database_connected)
	{
		database_connected = setup_pgsql_connection_pool(database_info);
		if (!database_connected)
		{
			sleep(4);//wait and check databse connection again
		}//if
	}//while

	
	//creating insert threads

	string query = "insert into test_table (name, family) VALUES ('mehdi','sadighian')";

	threadList.erase(threadList.begin(), threadList.end());

	for (int j = 0; j < 500; j++)
	{
		threadList.emplace_back(insert_pgsql, query);
	}//for

	std::for_each(threadList.begin(), threadList.end(), std::mem_fn(&std::thread::join));

	return 0;

}//main

compile with:

g++ main.cpp -lpthread -lpq -lpqxx