Skip site navigation (1) Skip section navigation (2)

Re: bytea size limit?

From: Michael Privat <michael(at)ceci(dot)mit(dot)edu>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: pg(at)fastcrypt(dot)com,"pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: bytea size limit?
Date: 2004-04-12 02:53:37
Message-ID: 1133534581.20040411225337@ceci.mit.edu (view raw or flat)
Thread:
Lists: pgsql-jdbc
Mmmh, well thanks guys. Only problem with changing to LOs is that I
already have data in production so changing the DB schema will be a
little complicated. I guess I could also contribute to the driver to
provide a streaming implementation. Do you know where that char[] is
in the code?

Sunday, April 11, 2004, 10:44:47 PM, you wrote:

OJ> Dave Cramer wrote:
>> Michael,
>> 
>> that doesn't surprise me, as the postgresql driver currently buffers
>> that internally, so you end up with two buffers of 1400000 bytes, have a
>> look through the archives for out of memory errors.

OJ> It's worse that that, Jim..

OJ>  From memory, we actually end up with a char[] of size 3*array length on
OJ> average (assuming 50% of the data needs escaping to a '\\nnn' form),
OJ> i.e. we need about 6 times the array's size in temporary storage (8.4mb
OJ> in this case).

>>>                        byte[] data = new byte[size];
>>>
>>>                        int id = Math.abs(new Random().nextInt());
>>>
>>>                        PreparedStatement stmt = c.prepareStatement(sql);
>>>                        stmt.setInt(1, id);
>>>                        stmt.setBinaryStream(2, new
>>> ByteArrayInputStream(data), data.length);

OJ> setBinaryStream ends up allocating a new byte array and reading into it,
OJ> then passing the result to setBytes. So you need a total of almost 10mb
OJ> of temporary storage to insert a 1.4mb bytearray. Yes, this sucks.

OJ> You'd be better off in this case (where the data is already in a byte
OJ> array) to call setBytes() directly. Once we have a streaming 
OJ> implementation, though, calling setBinaryStream() will be better, as
OJ> setBytes() will have to take an immediate copy of the array to avoid
OJ> seeing later changes before the statement is executed and the data streamed.

OJ> If you can't throw memory at the problem, using LOs as Dave suggested
OJ> earlier is probably a better idea. The LO manager uses 4k blocks (IIRC)
OJ> when moving data to/from the backend so the memory overhead is much lower.

OJ> -O


In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2004-04-12 03:14:01
Subject: Re: bytea size limit?
Previous:From: Oliver JowettDate: 2004-04-12 02:44:47
Subject: Re: bytea size limit?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group