Re: Selecting large objects stored as bytea

Ludger Zachewitz wrote:

> 'ResultSet rs = statement.executeQuery(selectClause);'
>
> After increase of HEAP in java it works, but also the java
> needs much memory, as I don't expect it. I have also
> tried to substitute this command line by prepared-statement
> like 'PreparedStatement ps =
> this.dbConnection.prepareStatement(selectClause);'
>
> Do have anyone a solution for that problem?

You could use the function below that breaks a bytea value into pieces
of 'chunksize' length and returns them as a set of rows.
Syntax of call:
SELECT * FROM chunks((SELECT subquery that returns one bytea column),
1024*1024)

CREATE OR REPLACE FUNCTION chunks (contents bytea,chunksize int)
RETURNS SETOF bytea AS $$
DECLARE
length int;
current int;
chunk bytea;
BEGIN
IF contents IS NULL THEN
RETURN NEXT NULL;
RETURN;
END IF;
SELECT octet_length(contents) INTO length;
current:=1;
LOOP
SELECT substring(contents FROM current FOR chunksize) INTO chunk;
RETURN NEXT chunk;
current:=current+chunksize;
EXIT WHEN current>=length;
END LOOP;
RETURN;
END;
$$ language 'plpgsql';

Another option would be not to use that function, but instead implement
its logic in your client-side code (multiple SELECTs in a loop). I
expect this would lessen the server-side memory consumption.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org