Re: Selecting large objects stored as bytea
От | Daniel Verite |
---|---|
Тема | Re: Selecting large objects stored as bytea |
Дата | |
Msg-id | c2b0bb9d-a24a-4dce-a311-9f7f69beb8e4@mm обсуждение исходный текст |
Ответ на | Selecting large objects stored as bytea ("Ludger Zachewitz" <ludger.zachewitz@gmx.de>) |
Ответы |
Re: Selecting large objects stored as bytea
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: