CODEWITHSUNDEEP
postgresql
Xin
Xin

Reputation: 807

How come PostgreSQL 9.2.1 can store Large Object bigger than 2GB?

I am totally new to PostgreSQL and database, and trying to do some tests about the large object.

I just found that 8GB file could be saved to Postgres.

However the document says the max for large object (pg_largeobject storage) is 2GB.

http://www.postgresql.org/docs/9.2/static/lo-intro.html

Am I missing something here?

Select version() shows:

PostgreSQL 9.2.1 on x86_64-unknow-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

here is my code in case you are interested:

private long insertLargeObject(UsSqlSession session, FileEntity fileEntity) throws SQLException, FileNotFoundException, IOException{
    LargeObjectManager lobj = getLargeObjectAPI(session);

    long oid = lobj.createLO();
    LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

    try(FileInputStream fis = new FileInputStream(fileEntity.getFile())){
        int bufSize = 0x8FFFFFF;
        byte buf[] = new byte[bufSize];
        int s = 0;
        int tl = 0;
        while( (s = fis.read(buf, 0, bufSize)) > 0 ) {
            obj.write(buf, 0, s);
            tl += s;
        }
    }

    obj.close();
    return oid;
}

UPDATE:

The size of the pg_largeobject is 11GB, and pg_largeobject_metadata says there is one line which means only one large object exists. 

select sum(length(lo.data)) 
from pg_largeobject lo 
where lo.loid=1497980;

returns 4378853347.

UPDATE:

public File findLargeObject(UsSqlSession session, long oid) throws SQLException, FileNotFoundException, IOException{
    LargeObjectManager lobj = getLargeObjectAPI(session);
    LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

    int bufSize = 0x8FFFFFF;
    byte buf[] = new byte[bufSize];
    int s = 0;
    int tl=0;

    File file = new File("e:/target-file");
    try(FileOutputStream output = new FileOutputStream(file)){

        while( (s = obj.read(buf, 0, bufSize)) > 0 ){
            output.write(buf, 0, s);
            tl += s;
        }
        output.flush();
    }

    obj.close();
    return file;
}

Upvotes: 3

Views: 1333

Answers (1)

kworr
kworr

Reputation: 3674

I think the correct answer would be: "Your PostgreSQL is built with int64 support thus enabling you to write more then 2GB in one LO. You can have problems reading it though."

Try reading reply from Tom Lane there: http://postgresql.1045698.n5.nabble.com/Large-objects-td2852592.html pay attention to random rant about "lo_seek64" and "lo_tell64" functions.

Upvotes: 2

Related Questions