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

Question

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;
}

I'm curious about this myself. Could you try something from [this answer](http://stackoverflow.com/questions/10169309/get-size-of-large-object-in-postgresql-query) to check if it actually is 8 GB on the server side, just to be sure? — jpmc26, Jun 11 '13 at 08:33
Yeah... if you're on 9.2 I'm not convinced the object is really the size you think, since all sizes are 32-bit signed integers. A 64-bit integer API for large objects is coming; I think it's in 9.3 but would need to double-check. **+1 thanks for including a proper version and the code in question**. — Craig Ringer, Jun 11 '13 at 08:37
@jpmc26 The size of the pg_large_object is 11GB, and pg_largeobject_metadata says there is one line. — Xin, Jun 11 '13 at 08:43
@jpmc26 `select sum(length(lo.data)) from pg_largeobject lo where lo.loid=1497980;` returns `4378853347`. I will update the question. — Xin, Jun 11 '13 at 08:52
Assuming that's bytes, that's roughly 4 GB. Sounds like something fishy is going on all around. (Notifying @CraigRinger because I am clearly out of my league. =) ) — jpmc26, Jun 11 '13 at 08:58

score 2 · Accepted Answer · answered Jun 11 '13 at 08:58

2

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.

answered Jun 11 '13 at 08:58

kworr

3,579
1
21
33

Good find... that's a reasonable explanation. – Craig Ringer Jun 11 '13 at 09:05
"Ouch. Letting people write data to where they can't get it back from seems double-plus ungood. " by Robert Haas – Xin Jun 11 '13 at 09:48
@CraigRinger I just ran anothe test by writing and reading a 3GB file. Postgres jsut works fine. `select sum(length(lo.data)) from pg_largeobject lo where lo.loid=1497987;` returns `3350906766`. – Xin Jun 12 '13 at 00:57
Yes, but can you *actually get the data* with `loread`, `lo_lseek`, etc? There are no `bigint` versions of those. – Craig Ringer Jun 12 '13 at 01:12
@CraigRinger I can read it from Postgres. See my updated code for the reading. – Xin Jun 12 '13 at 01:45
@jpmc26 I see. So Pg's internally using the 64-bit API, you just can't read more than 2GB chunks or seek more than 2GB at a time because the SQL interface is limited to ints. Presumably `lo_tell` will give weird results or an integer overflow error too. Ugly, but workable. Nice work on the question and follow-up, wish I could upvote you more; ditto kworr for finding the appropriate reference. I'll see if I can get this fixed in PostgreSQL 9.4, it's ugly. – Craig Ringer Jun 12 '13 at 02:48

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

1 Answers1