inserting huge file into bytea cause out of memory

From: liuyuanyuan <liuyuanyuan(at)highgo(dot)com(dot)cn>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: inserting huge file into bytea cause out of memory
Date: 2013-08-06 07:12:53
Message-ID: 201308061512534335298@highgo.com.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello everyone!
I got a out of memory problem, when I tried to insert a binary file (256MB) to bytea column;
I want to get a way to insert files (vary from 1byte to 2GB) or byte array or binary stream into
PostgreSQL bytea field, never cause out of memory. Fellowed by the details.
Anybody know about this, please write to me.
Thanks in advance!

Table defination:
create table image_bytea(t_id int, t_image bytea);
Major code:
String sql = "insert into image_bytea(t_id, t_image) values (?, ?)";
ps = conn.prepareStatement(sql);
 ps.setInt(1, 88);
File file = new file("d://1.jpg");
 InputStream in = new BufferedInputStream(new FileInputStream(file));
ps.setBinaryStream(2, in, (int) file.length());
System.out.println("set");
ps.executeUpdate();
Error detail:
org.postgresql.util.PSQLException: Error: out of memory
Details:Failed on request of size 268443660.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
at com.highgo.hgdbadmin.migrationassistant.controller.MigrationController.executeInsert(MigrationController.java:1400)
at com.highgo.hgdbadmin.migrationassistant.controller.MigrationController.insertDate2HG(MigrationController.java:1143)
at com.highgo.hgdbadmin.migrationassistant.controller.MigrationController.migrateTable(MigrationController.java:898)
at com.highgo.hgdbadmin.migrationassistant.controller.MigrationController.migrate(MigrationController.java:301)
at com.highgo.hgdbadmin.migrationassistant.controller.MigrationController.access$000(MigrationController.java:66)
at com.highgo.hgdbadmin.migrationassistant.controller.MigrationController$MigrateRunnable.run(MigrationController.java:241)
at java.lang.Thread.run(Thread.java:662)

Liu Yuanyuan
August 6, 2013

Responses

Browse pgsql-general by date

  From Date Subject
Next Message KONDO Mitsumasa 2013-08-06 10:09:06 Re: [GENERAL] Bottlenecks with large number of relation segment files
Previous Message Pavel Stehule 2013-08-06 05:47:47 Re: Seamless replacement to MySQL's GROUP_CONCAT function...