Thread: Cannot Retrieve Binary Data

Cannot Retrieve Binary Data

From
patrick
Date:
Hi all ,
i'm following the example at
http://www.postgresql.org/docs/7.4/interactive/jdbc-binary-data.html
trying to retrieve previous stored  file (more than 600MB) using the
following code:

            Jdbc3ConnectionPool source = new Jdbc3ConnectionPool();
            conn =
DriverManager.getConnection("jdbc:postgresql://localhost/backup",
"test", "test");
            conn.setAutoCommit(false);
                   PreparedStatement ps = conn.prepareStatement("SELECT img
FROM images WHERE imgname = ?");
            ps.setString(1, "civil.dmg");
            ResultSet rs = ps.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    byte[] imgBytes = rs.getBytes(1);
                    // use the data in some way here
                }
                rs.close();
            }
            ps.close();
            conn.commit();

during the executeQuery() i got a the following exception

java.sql.SQLException: ERROR: invalid memory alloc request size
2017394403

The Postgres version is 8.0.1 (compiled from source with default
settings ) running on Darwin Kernel Version 7.8.0

Thanks in advance for any help

-patrick


Re: Cannot Retrieve Binary Data

From
patrick
Date:
Exactly the same error:

ERROR:  XX000: invalid memory alloc request size 2017394403
LOCATION:  MemoryContextAlloc, mcxt.c:502

the table is declared as:
backup=# \d images;
     Table "public.images"
  Column  | Type  | Modifiers
---------+-------+-----------
  imgname | text  |
  img     | bytea |

and the query is (there's only one record)

backup=# SELECT img from images ;
ERROR:  invalid memory alloc request size 2017394403


There's no way to stream a bytea ? how postgres support bytea with 1G
if is not possible to insert and select ?
FYI:
i tried the same Test with postgres 7.4.7 and earlier and is even worst
  i was not able to insert.
The  JDBC Driver  Return Out_Of_Memory during the query execution




On 11 Mar 2005, at 22:05, Oliver Jowett wrote:

> patrick wrote:
>
>> i'm following the example at
>> http://www.postgresql.org/docs/7.4/interactive/jdbc-binary-data.html
>> trying to retrieve previous stored  file (more than 600MB)
>
>> during the executeQuery() i got a the following exception
>> java.sql.SQLException: ERROR: invalid memory alloc request size
>> 2017394403
>
> What happens if you run an equivalent query via psql?
>
> I suspect what is happening is that you inserted the data earlier fine
> as JDBC sends it directly in binary form; however, when retrieving,
> the data asks for the text form, which expands the data by up to a
> factor of 5 (to >3GB for your file). The backend doesn't stream this
> data AFAIK, so it has to allocate space for the entire text
> representation. Then you hit a memory allocation sanity check in the
> backend, resulting in the error you see.
>
> -O
>


Re: Cannot Retrieve Binary Data

From
Oliver Jowett
Date:
patrick wrote:

> There's no way to stream a bytea ? how postgres support bytea with 1G if
> is not possible to insert and select ?

You need to use the binary parameter/result format (at the protocol
level). The current JDBC driver uses the binary format for bytea
parameters, but the text format for resultsets. psql uses the text
format for both, I believe.

> FYI:
> i tried the same Test with postgres 7.4.7 and earlier and is even worst
>  i was not able to insert.
> The  JDBC Driver  Return Out_Of_Memory during the query execution

I assume you mean with an earlier version of the driver? Earlier driver
versions used the text format for both parameters and results, and also
used a large amount of temporary heap space for large bytea parameters.

You may want to look at using the large-object interface if you are
dealing with extremely large data; it allows random read/write access to
the data without transferring it all across the wire in one go.

-O

Re: Cannot Retrieve Binary Data

From
patrick
Date:
On 13 Mar 2005, at 13:36, Oliver Jowett wrote:
>
> You need to use the binary parameter/result format (at the protocol
> level). The current JDBC driver uses the binary format for bytea
> parameters, but the text format for resultsets. psql uses the text
> format for both, I believe.
Thanks for the suggestion i will try again


>> FYI:
>> i tried the same Test with postgres 7.4.7 and earlier and is even
>> worst  i was not able to insert.
>> The  JDBC Driver  Return Out_Of_Memory during the query execution
>
> I assume you mean with an earlier version of the driver? Earlier
> driver versions used the text format for both parameters and results,
> and also used a large amount of temporary heap space for large bytea
> parameters.
both earlier driver and earlier version of postgresql  now i undestand
why my tests were failing.

> You may want to look at using the large-object interface if you are
> dealing with extremely large data; it allows random read/write access
> to the data without transferring it all across the wire in one go.

Yes it appear the be the best solution.. thanks for you help

-patrick


Re: Cannot Retrieve Binary Data

From
jonathan.lister@vaisala.com
Date:

I have also noticed that jdbc driver seems to be able to store larger binary objects than it can retrieve when using a bytea column.

Is there any guidance on what the recommended maximum size would be for a bytea column before moving to an OID column .. or is this dependant on tuning database server parameters?

-----Original Message-----
From: patrick [mailto:pch@freeshell.org]
Sent: 14 March 2005 07:34
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Cannot Retrieve Binary Data

On 13 Mar 2005, at 13:36, Oliver Jowett wrote:
>
> You need to use the binary parameter/result format (at the protocol
> level). The current JDBC driver uses the binary format for bytea
> parameters, but the text format for resultsets. psql uses the text
> format for both, I believe.
Thanks for the suggestion i will try again

>> FYI:
>> i tried the same Test with postgres 7.4.7 and earlier and is even
>> worst  i was not able to insert.
>> The  JDBC Driver  Return Out_Of_Memory during the query execution
>
> I assume you mean with an earlier version of the driver? Earlier
> driver versions used the text format for both parameters and results,
> and also used a large amount of temporary heap space for large bytea
> parameters.
both earlier driver and earlier version of postgresql  now i undestand
why my tests were failing.

> You may want to look at using the large-object interface if you are
> dealing with extremely large data; it allows random read/write access
> to the data without transferring it all across the wire in one go.

Yes it appear the be the best solution.. thanks for you help

-patrick

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org