Trying to implement large object support

[NicholasWMRitchie]

This question goes beyond the exported LibPQ functionality.

I'm trying to implement very simple large object support using a couple of the methods exposed in libpq_c.
Neither call to the libpq_c functions works.

function lo_import(conn::LibPQ.Connection, filename::AbstractString)::LibPQ.Oid
    if (res = LibPQ.libpq_c.lo_import(conn.conn, filename)) == LibPQ.Oid(0x0)
        @error LibPQ.error_message(conn)
    end
    res
end

The import function seems to get the correct filename to the library. But if I give it the name
of an existing file it fails with "ERROR: invalid large-object descriptor: 0"

function lo_export(func::Function, conn::LibPQ.Connection, oid::UInt32)
    res = missing
    filename=tempname(;cleanup=false)
    try
        if LibPQ.libpq_c.lo_export(conn.conn, oid, filename) == 1
            try
                res = func(filename)
            catch ex
                @error ex
            end
        else
            @error LibPQ.error_message(conn)
            @error "Unable to export large object ID# $oid to $filename."
        end
    finally
        rm(filename; force=true)
    end
    res
end

Likewise the call to libpq_c.lo_export also fails with the same error message. ("ERROR: invalid large-object descriptor: 0")

Is there any reason you know why these functions will fail? The documentation mentions something about "pipeline mode" introduced in PostgreSQL 14 causing these functions to fail. But this has to be explicitly enabled.

[iamed2]

I'm not sure why this might happen, but I would try to gather more information:

• Perhaps ask for status(conn) before and after calling the function
• Set PQsetErrorVerbosity to PQERRORS_VERBOSE (typically this has little impact on connection errors but maybe it will here)

What client LibPQ.jl version and what server version do you have?

[NicholasWMRitchie]

Thanks! With your suggestions, I was able to answer my own question. The verbose errors pointed to the right line in the libpq C code.
It seems that I must wrap either operation (lo_import / lo_export) in a transaction. (This probably also is true of other lo_XXX operations.)

[NicholasWMRitchie]

For what it is worth, these functions perform basic large object import/export and deletion.

function lo_import(conn::LibPQ.Connection, filename::AbstractString)::LibPQ.Oid
    execute(conn, "BEGIN;");
    res = 0
    try
        if (res = LibPQ.libpq_c.lo_import(conn.conn, filename)) == LibPQ.Oid(0x0)
            @error LibPQ.error_message(conn)
        end
    finally
        execute(conn,"COMMIT;")
    end
    res
end

This export function creates a temporary file, writes the file to the temporary, hands
the filename to func, deletes the temporary and then returns the result of func. On
error the function returns missing.

function lo_export(func::Function, conn::LibPQ.Connection, oid::UInt32)
    res = missing
    filename=tempname(;cleanup=false)
    try
        execute(conn, "BEGIN;");
        try
            if LibPQ.libpq_c.lo_export(conn.conn, oid, filename) == 1
                try
                    res = func(filename)
                catch ex
                    @error ex
                end
            else
                @error LibPQ.error_message(conn)
            end
        finally
            execute(conn, "COMMIT;")
        end
    finally
        rm(filename; force=true)
    end
    res
end

The lo_unlink function doesn't seem to require a transaction.

function lo_unlink(conn::LibPQ.Connection, oid::UInt32)
    res = -1
    if (res=LibPQ.libpq_c.lo_unlink(conn.conn, oid)) != 1
        @error LibPQ.error_message(conn)
    end
    res
end

[iamed2]

I want to keep transactions out of the functions to allow users to decide on their transactions (e.g. maybe they want to do multiple operations within the transactions). But having functions that wrap the libpq_c code would definitely be useful, and this is helpful, thanks.

With your suggestions, I was able to answer my own question. The verbose errors pointed to the right line in the libpq C code.

This is a good indication I should expose the verbosity option at the Connection level.