Read/write large object from postgres using pqxx

Question

The main pqxx API works with columns as text. So how to access binary data from large objects (LOB) using the pqxx library?

Answer 1

There are a couple of ways. The first way, is to translate the data to/from bytea, and work through the common pqxx api. If you know how to work with bytea, probably this is your way. Here is example how to insert a string as lob, plain sql, no c++ code:

select lo_from_bytea(0, 'this is a test'::bytea);
...
select encode(lo_get(190850), 'escape'); -- here 190850 is the oid for lob created by the first line.

The other option is to use iostream API provided by pqxx library. There is no much examples of how to use it, so here we go:

// write lob
auto conn = std::make_shared<pqxx::connection>(url);
auto tran = std::make_shared<pqxx::work>(*conn);
auto stream = std::make_shared<pqxx::olostream>(*tran, oid);
stream->write(data, size);
stream->flush();
stream.reset();
tran->commit();

// read lob
stream = std::make_shared<pqxx::ilostream>(*tran, oid);
...
sszie_t get_chunk(shard_ptr<> stream, char *buf, size_t max_len)
{
    while (!stream->eof() && len < max_len && stream->get(buf[len])) {
        len++;
    }

    return (len > 0 || !stream->eof()) ? len : -1;
}

Note: there is a bug in pqxx::ilostream, you can get truncated data if 0xff byte in the data will hit the inner buffer boundary, it will mistakenly considered as EOF character. The bug was fixed in the february 2020, and for now this fix didn't get to all distributions.