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