libpqxx stream_to binarystring tester (normal prepared vs stream_to)

streamtest.cpp

#include <iostream>
#include <string>
#include <ctime>

#include "pqxx/pqxx" //wherever you have the local pqxx superheader - remember to fix your linker as well
 
int lengthByteArray = 10000;
int vectorsize = 10000;  //10K 10K gives a couple of seconds

char* GetRandomByteArrayContent() { //generate the fake data - should include random 0 values (end of char strings)
    char* cp = new char[lengthByteArray];
    for (int j = 0; j < lengthByteArray; j++) {
        char c = (char)rand();
        cp[j] = c;
    }

    return cp;
}

std::vector<std::tuple<std::string, char*>> GenerateRandomPayload() {
    std::vector < std::tuple<std::string, char*>> r;
    for (int j = 0; j < vectorsize; ++j) {
        char* cp = GetRandomByteArrayContent();
        r.push_back(std::make_tuple(std::string("A"), cp));
    }

    return r;
}

std::string m_timingMsg;
time_t m_timingTSBegin;

void BeginTiming(std::string Msg) {
    m_timingMsg = Msg;
    m_timingTSBegin = time(nullptr);
}

void EndTiming() {
    time_t now = time(nullptr);
    auto delta = now - m_timingTSBegin;
    std::cout << m_timingMsg << " took: " << delta << std::endl;
}

int main(int argc, char* argv[])
{
    //determine connstring argument

    std::string connstring = "hostaddr=127.0.0.1 port=5432 dbname=test user=postgres password=postgres";
    //connect
    pqxx::connection pqxxconn(connstring);
    
    {   //quick and dirty check to see if can do a bare minimum
        pqxx::work pqxxtransaction(pqxxconn);
        pqxx::result res = pqxxtransaction.exec("select 1 as one");
        int one = res[0]["one"].as<int>();
        assert(one == 1);
    }

    {//initialize database
        //PrepDatabase(pqxxconn);
        {
            pqxx::work t(pqxxconn);
            t.exec("DROP TABLE IF EXISTS stream");
            t.commit();
        }
        
        {//create table
            pqxx::work t(pqxxconn);
            t.exec("CREATE TABLE stream (pk SERIAL PRIMARY KEY, txt VARCHAR(100), data BYTEA)");
            t.commit();
        }
    }


    /////////////////////////////////////////////////////
    //run streamto tests
    auto data_to_insert = GenerateRandomPayload();

    {
        pqxx::connection c(connstring);
        pqxx::work t(c);
        c.prepare("man_ins", "INSERT INTO stream(txt, data) VALUES ($1,$2);");

        /*
        BeginTiming("simple exec inserts");
        for (auto x : data_to_insert) {
            pqxx::binarystring b(x, lengthByteArray);
            t.exec("INSERT INTO stream(data) VALUES ($1);", b);
        }
        EndTiming();
        */
        {
            pqxx::connection c(connstring);
            pqxx::work t(c);
            c.prepare("man_ins", "INSERT INTO stream(txt, data) VALUES ($1, $2);");

            for (int loop = 0; loop < 5; loop++) {
                std::string label = "manual inserts #" + std::to_string(loop);
                BeginTiming(label);
                for (const auto& [txt, data] : data_to_insert) {
                    pqxx::binarystring b(data, lengthByteArray);
                    t.exec_prepared("man_ins", txt, b);
                }
                t.commit();
                EndTiming();
            }
        }

        //stream_to
        {
            pqxx::connection c(connstring);
            
            std::vector<std::tuple<std::string, pqxx::binarystring>> dataB;
            for (const auto& [txt, data] : data_to_insert) {
                dataB.push_back(std::make_tuple(txt, pqxx::binarystring(data, lengthByteArray)));
            }


            for (int loop = 0; loop < 5; loop++) {
                std::string label = "stream inserts #" + std::to_string(loop);
                BeginTiming(label);
                pqxx::work t(c);
                pqxx::stream_to stream(t, "stream", std::vector<std::string>{"txt", "data"});
                for (const auto& x : dataB) {
                    stream << x;
                   
                }
                stream.complete();
                t.commit();
                EndTiming();
            }
        }
        std::cout << "End" << std::endl;
    }
}