How to fix 0F000: locator_exception in PostgreSQL

-- WRONG - This will fail
SELECT lo_create(0);

-- CORRECT - Wrap in transaction
BEGIN;
  SELECT lo_create(0);  -- Returns OID
COMMIT;

BEGIN;
  SELECT lo_get(16389);  -- Read large object by OID
COMMIT;

// JDBC Example
Connection conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);  // CRITICAL for LOBs

try {
    LargeObjectManager lobj = ((PGConnection)conn).getLargeObjectAPI();
    long oid = lobj.createLO();  // Create in transaction

    // Perform LOB operations
    LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
    obj.write(data);
    obj.close();

    conn.commit();  // Explicit commit
} catch (Exception e) {
    conn.rollback();
    throw e;
}

# Python psycopg2 Example
import psycopg2

conn = psycopg2.connect(database="mydb")
conn.autocommit = False  # Disable autocommit

try:
    cursor = conn.cursor()
    cursor.execute("BEGIN")

    # Create and write large object
    lo = conn.lobject(0, "w")
    lo.write(b"data")
    lo.close()

    conn.commit()
except Exception as e:
    conn.rollback()
    raise

-- Check if specific large object exists
SELECT EXISTS(
    SELECT 1 FROM pg_largeobject_metadata
    WHERE oid = 16389
);

-- List all large objects with metadata
SELECT
    oid,
    lomowner,
    lomacl,
    pg_size_pretty(pg_total_relation_size(
        pg_largeobject_metadata.oid::regclass
    )) as size
FROM pg_largeobject_metadata
ORDER BY oid;

-- Find the OID of recently created large objects
SELECT oid FROM pg_largeobject_metadata
ORDER BY oid DESC
LIMIT 10;

BEGIN;
  DO $$
    DECLARE
        v_oid OID := 16389;
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM pg_largeobject_metadata WHERE oid = v_oid) THEN
            RAISE EXCEPTION 'Large object % does not exist', v_oid;
        END IF;
        -- Safe to access now
        PERFORM lo_get(v_oid);
    END;
  $$ LANGUAGE plpgsql;
COMMIT;

// JDBC - Validate connection before LOB operations
Connection conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);

try {
    // Validate connection is still alive
    if (conn.isClosed() || !conn.isValid(5)) {
        System.out.println("Connection invalid, reconnecting...");
        conn = DriverManager.getConnection(url, user, password);
        conn.setAutoCommit(false);
    }

    // Now safe to proceed with LOB operations
    LargeObjectManager lobj = ((PGConnection)conn).getLargeObjectAPI();
    // ... rest of LOB operations
} finally {
    if (!conn.isClosed()) {
        conn.close();
    }
}

# Python - Test connection before LOB operations
import psycopg2
from psycopg2 import OperationalError

def get_valid_connection(conn):
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT 1")
        cursor.close()
        return conn
    except OperationalError:
        return psycopg2.connect(database="mydb")

conn = psycopg2.connect(database="mydb")
conn.autocommit = False
conn = get_valid_connection(conn)

-- Create table with BYTEA (recommended)
CREATE TABLE files (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    content BYTEA NOT NULL,
    size INT NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

-- Insert data (no transaction required for simple inserts)
INSERT INTO files (name, content, size)
VALUES ('document.pdf', '\x255044462d...'::bytea, 1024);

-- Read data
SELECT content FROM files WHERE id = 1;

-- Update data
UPDATE files SET content = '\x...'::bytea WHERE id = 1;