PostgreSQL Save and Pick files as large objects with C libpq

Question

I am working in this issue since 2 weeks without any result. Do someone know how to manage with lipq with blobs or bytea without losing format and any data? The exported file size is 0B, I can not understand the steps I must follow to upload a file to a postgreSQL database from C and pick it again with the correct format and features. Any help will be great. I tryed near every example and theory on the net, even PG documents and manuals, no way. I am close to quit programing and go farmer (not jocking xD). Thank you in advance.

<CODE EDITED mantaining transaction open 24-5-21>

After code modifications, I pick a file 59bytes higher than the file uploaded as large object. Feeling I am closer but changing my mind about using Large Objects.

#include "libpq/libpq-fs.h"
#include "libpq-fe.h"

int main(int argc, char* argv[])
{
    //*************************** IMPORT TEST **********
    manager.conn = manager.ConnectDB();  // my manager, working fine    

    Oid blob;
    char* picName = new char[]{ "powerup.png" };
    PGresult* res;

    ress = PQexec(manager.conn, "begin");
    PQclear(res);

    blob = lo_import(manager.conn, "powerup.png");
    cout << endl << "import returned oid " << blob;

    //res = PQexec(manager.conn, "end");
    //PQclear(res);

    string sentenceB = "INSERT INTO testblob(filename, fileoid) VALUES('powerup.png', '" + std::to_string(blob) + "')";

    manager.GenericQuery(manager.conn, sentenceB); //same as PQexec + result evaluation, works ok
    PQclear(res);

    //*************************** EXPORT TEST **********

    OidManager oidm;

    oidm.exportFile(manager.conn, blob, picName);  // Ill show the function content at the end

    res = PQexec(manager.conn, "end"); //SAME TRANSACTION TO AVOID LOSING THE OID, CHANGES AFTER TRANSACTION...
    PQclear(res);

    manager.CloseConn(manager.conn);   // my manager, works fine

    return true;
}

    // oidm.exportFile() FUNCTION DETAIL
    // code from: 35.5 Example Program Chapter 34 Large Objects
    // https://www.postgresql.org/docs/10/lo-examplesect.html

void OidManager::exportFile(PGconn* conn, Oid lobjId, char* filename)
{
    int         lobj_fd;
    char        buf[BUFSIZE];
    int         nbytes,
        tmp;
    int         fd;

    /*
     * open the large object
     */
    lobj_fd = lo_open(conn, lobjId, INV_READ);
    if (lobj_fd < 0)
        fprintf(stderr, "cannot open large object %u", lobjId);

    /*
     * open the file to be written to
     */
    fd = _open(filename, O_CREAT | O_WRONLY | O_TRUNC, 0666);
    if (fd < 0)
    {                           /* error */
        fprintf(stderr, "cannot open unix file\"%s\"",
            filename);
    }

    /*
     * read in from the inversion file and write to the Unix file
     */
    while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
    {
        tmp = _write(fd, buf, nbytes);
        if (tmp < nbytes)
        {
            fprintf(stderr, "error while writing \"%s\"",
                filename);
        }
    }

    lo_close(conn, lobj_fd);
    _close(fd);

    return;
}

Answer 1

Like the documentation says:

The descriptor is only valid for the duration of the current transaction.

So you must call lo_open and lo_read in the same transaction.

Do not use large objects. They are slow, complicated to use and give you all kinds of serious trouble (for example, if you have many of them). Use bytea, then your code will become much simpler.