Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aLBv1-0006Ui-Lg for pgsql-sql@arkaria.postgresql.org; Mon, 18 Jan 2016 15:40:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aLBv0-0003E1-VQ for pgsql-sql@arkaria.postgresql.org; Mon, 18 Jan 2016 15:40:35 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aLBuz-0003Dn-Po for pgsql-sql@postgresql.org; Mon, 18 Jan 2016 15:40:34 +0000 Received: from nm12-vm0.bullet.mail.bf1.yahoo.com ([98.139.213.140]) by makus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aLBuv-0005JK-DR for pgsql-sql@postgresql.org; Mon, 18 Jan 2016 15:40:32 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s2048; t=1453131628; bh=NEVZhqgJSxRrgcdG4tFIMS0p4VnMHkNCfGhKXmvQpfo=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=Oe214/Epg86mAT4RD01ctsKkbET4P8uCJYzqjkg6r1NJn1z94Y72WKTdljewX/EXYYRxBG/ktGx0rFFW/w5UoCrH5F4xiTzASvBlBBUgFs8wkorQkgKPuZtmd42e6nC3XhG2gY7ahj2y3R4sLZpYJAnwrxkegPBT6N9ucpj3uxMXeafa0Vz26iK4kejnfSITE9au5zbrAmJPJ2VNlVpWGWxuvivi8TgB5lKwAHNFlO0bPSapfrIOeFLHv+eKLe7p4VkLEnpZ05xqo/UGEIv47pmVx467z5cQSQ8fqrsi2/AM+dDUCiDCNbNs+nHplahPAYF+HGeZzIr/q+WC8o4Ofg== Received: from [98.139.214.32] by nm12.bullet.mail.bf1.yahoo.com with NNFMP; 18 Jan 2016 15:40:28 -0000 Received: from [98.139.212.215] by tm15.bullet.mail.bf1.yahoo.com with NNFMP; 18 Jan 2016 15:40:28 -0000 Received: from [127.0.0.1] by omp1024.mail.bf1.yahoo.com with NNFMP; 18 Jan 2016 15:40:27 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 998268.12117.bm@omp1024.mail.bf1.yahoo.com X-YMail-OSG: JRDCMigVM1kM1AiMJkAIpwOkTztfQyUc5w.UkZfRlazcGm_0WX6dLqKUUFFIO.H y_cwyXz50.oz1Sfvp9J9uNWkrkmtyXz5bn0b37u7LRJmnyvHvA6wDWqsfyBV.QNB.ieWgNz6JsOW ZZj7wiOoaAbKyUM3zEhhaCF_upQpL952m6waXYpiXy6nE._DNw6MW2J1E25WtjfmBxb74heGofyH 0fqnWoDPaHUH6Co29fcfpAixsR_z3UMO40ah2TJwtS.txTvlvD0mx36Fcp8zZtp7bNSglNX2c57j FQV2iqzAC2Wx1OlqxemQnsiKAsbQ8vrizhjnEBhhFQp5OlmJylBFlEUfwMGTDp6LG2ySsPark26d y4rIzF6XxcNhtpkqCP2IVkwwIT847LEifuSUV39.oGpYw0rVJ4epl.FeIyMS.5sCAYqdGCHAaPMr 29FNHa34.24dT5jW8lNGOU9GnUsTUsb5HaGWklPFt4XS95AEroZxs3c5xa6ZrgggxQLZlfKl6gbW PdrteyQKRdP6I2GG9TxvgpQlsFlwuG955Sw-- Received: by 66.196.81.117; Mon, 18 Jan 2016 15:40:27 +0000 Date: Mon, 18 Jan 2016 15:40:26 +0000 (UTC) From: Eugene Yin Reply-To: Eugene Yin To: Andreas Joseph Krogh Cc: Postgres List Message-ID: <1592189786.7265044.1453131627096.JavaMail.yahoo@mail.yahoo.com> In-Reply-To: References: Subject: Re: BYTEA MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_7265043_148557982.1453131627096" Content-Length: 14197 X-Pg-Spam-Score: -1.2 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_7265043_148557982.1453131627096 Content-Type: multipart/alternative; boundary="----=_Part_7265042_1162797814.1453131627085" ------=_Part_7265042_1162797814.1453131627085 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable "if you only work with small-ish binary data, yes - BYTEA does the job." --The goal is to save/retrieve the user's photos (JPEG, TIFF, GIF) and PDF = files.=C2=A0 The size of each file is less than=20 5 MB.=C2=A0 For such purpose, is BYTEA ok?=C2=A0 If not, then how about 1 M= B each? "whole byte-array is kept in memory, both in the JAVA-app and in PG" --I believe in Java the GarbageCollector will clean it up (?).=C2=A0 "Who" = will then clean up the Pg side? "whole byte-array is kept in memory, both in the JAVA-app and in PG" --Does that mean for the OID, byte-array is NOT kept in memory (of JAVA-app= or PG)?=C2=A0 If so, where is it kept? And how they are got cleaned up? Thanks Eugene =20 =20 On Monday, January 18, 2016 5:07 AM, Andreas Joseph Krogh wrote: =20 P=C3=A5 s=C3=B8ndag 17. januar 2016 kl. 23:13:09, skrev Thomas Kellerer : Andreas Joseph Krogh schrieb am 17.01.2016 um 23:09: >=C2=A0 =C2=A0 =C2=A0 > Do I=C2=A0 really *Need to escape/encode binary dat= a before sending to DB >=C2=A0 =C2=A0 =C2=A0 > then do the reverse after retrieving the data?* >=C2=A0 =C2=A0 =C2=A0 > * >=C2=A0 =C2=A0 =C2=A0 > * >=C2=A0 =C2=A0 =C2=A0 > If so, what (*Java*) codes should I use to achieve = this goal (I am using >=C2=A0 =C2=A0 =C2=A0 > the Java to interface with the DB)? > >=C2=A0 =C2=A0 =C2=A0https://jdbc.postgresql.org/documentation/94/binary-da= ta.html > > Save yourself the trouble and don't go this route. Use https://github.com= /impossibl/pgjdbc-ng instead. Can you elaborate? Using the "official" JDBC driver with bytea column works just fine for me. =C2=A0Depends on what "works" is.Using BLOBs (that is SQL-BLOB, not *ps.set= BinaryStream etc.) with ps.setBlob/rs.getBlob and Connection.createBlob cer= tainly doesn't work using the official driver.=C2=A0https://github.com/pgjd= bc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection= .java#L1284-L1287=C2=A0 public Blob createBlob() throws SQLException { checkClosed(); throw org.postgresql.Driver.notImplemented(this.getClass(), "createBlob= ()"); }=C2=A0AFAIU this thread is about working with LARGE OBJECTS, not only bi= nary data.Also, using BYTEA with LARGE objects (not just binary data) quick= ly leads to OutOfMemoryError. Which is why I recommend using pgjdbc-ng and = real BLOBs (using OID) instead. It is true that get/setBinaryStream "works"= , in essence that it appears to do the jobb. The problem is that despite us= ing get/setBinaryStream with BYTEA appears to use streams, it doesn't, and = the whole byte-array is kept in memory, both in the JAVA-app and in PG. The= only way to work with real streams all the way is using OID, not BYTEA.=C2= =A0But, of course, if you only work with small-ish binary data, yes - BYTEA= does the job.=C2=A0--Andreas Joseph KroghCTO / Partner - Visena ASMobile: = +47 909 56 963andreas@visena.comwww.visena.com=C2=A0 =20=20= ------=_Part_7265042_1162797814.1453131627085 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
"= if you only work with small-ish binary data, yes - BYTEA does the job."=

--The goal is to save/= retrieve the user's photos (JPEG, TIFF, GIF) and PDF files.  The size = of each file is less than
5 MB.  For such purpose, is BYTEA ok?  If not, t= hen how about 1 MB each?


= "whole byte-array is kept in memory, both in the JAVA-app and in PG"

--I believe in Java the GarbageCollector = will clean it up (?).  "Who" will then clean up the Pg side?


"whole byte-array is kept i= n memory, both in the JAVA-app and in PG"

--Does that mean for the OID, byte-arra= y is NOT kept in memory (of JAVA-app or PG)?  If so, where is it kept?= And how they are got cleaned up?



Thanks

Eugene








On Monday, January 18, 2016 = 5:07 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
<= /div>

P=C3=A5 s=C3=B8ndag 17. januar 2016 kl. 23:13:09, skrev Thomas Kell= erer <spam_eater@gmx.= net>:
Andreas= Joseph Krogh schrieb am 17.01.2016 um 23:09:
>      > Do I  really *Need to escape/encode bina= ry data before sending to DB
>      > then do the reverse after retrieving the data= ?*
>      > *
>      > *
>      > If so, what (*Java*) codes should I use to ac= hieve this goal (I am using
>      > the Java to interface with the DB)?
>
>     https://jdbc.postgresql.org/documentation/94/binary= -data.html
>
> Save yourself the trouble and don't go this route. Use https://github.= com/impossibl/pgjdbc-ng instead.

Can you elaborate?

Using the "official" JDBC driver with bytea column works just fine for me.<= /div>
 
Depends on what "works" is.
Using BLOBs (that is SQL-BLOB, not *ps.setBinaryStream etc.) with ps.s= etBlob/rs.getBlob and Connection.createBlob certainly doesn't work using th= e official driver.
 
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/= postgresql/jdbc/PgConnection.java#L1284-L1287
 
  public Blob createBlob() thr=
ows SQLException {
    checkClosed();
    throw org.postgresql.Driver.notImplemented(this.getClass(), "createBlob=
()");
  }
 
AFAIU this thread is about working with LARGE OBJECTS, not only binary= data.
Also, using BYTEA with LARGE objects (not just binary data) quickly leads t= o OutOfMemoryError. Which is why I recommend using pgjdbc-ng and real BLOBs= (using OID) instead. It is true that get/setBinaryStream "works", in essen= ce that it appears to do the jobb. The problem is that despite using get/se= tBinaryStream with BYTEA appears to use streams, it doesn't, and t= he whole byte-array is kept in memory, both in the JAVA-app and in= PG. The only way to work with real streams all the way is using OID, not B= YTEA.
 
But, of course, if you only work with small-ish binary data, yes - BYT= EA does the job.
 
--
Andr= eas Joseph Krogh
CTO / Partne= r - Visena AS
Mobile: +47 = 909 56 963
3D""
 


<= /html>= ------=_Part_7265042_1162797814.1453131627085-- ------=_Part_7265043_148557982.1453131627096 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgI fAhkiAAABzBJREFUaEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrA UgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/HADs43q6kROeJNbOYgQACIAgCWJKn g4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfdt5mBx1NhqSEo 8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X9 49OnaS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8Wf Ci80Xgf9vVlrhg9yRONe/f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl 94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd8M58lxPCvvD6aihfvexbEQoP YO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe+JXkyGgW INq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZ lN16xygIgl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1v CvvqKXIW8mZYW+F3Tg4r8HvZkQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6 beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMHQPwReg2wW8umEe/+L8T/ LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aYhdiC nRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZ TDUEj8u6IVhuEhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQa v4vrmp7cpMp2qbHnBxWJbisbho2QXI6C1sIHDUFhH4Hij4UbIev66eANeiwb kA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8UBIYhBd6oSUgYMijO kecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdS PAvxHYin6YmN9YBXQmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvyko BEc9CoCGWJisOLOGoCORs1FvIMbYA8z3kwM59oemy6LlWrLxFLmWgiQAfEGd 8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNjv/gANOdQxPfc iP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcP c4rVNjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5 pqCuK01k40kSNtnKiKIlUfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/b db9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gNJ/aYDhk+hhjExwaPcz5PObA5 Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82Nr4LeS2j 1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3 ZEJXi/GRa6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqp HLu9jBrhUuhNwVGbpyZrzrl0HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1 t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ppTsuRCbvUD9fwquksG5 GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQGoX0 ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9k TXoluzeS0fB99CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOY Nt9pwxSZtYDsX/GE3xTkrXgwAsXmfud08FiTeC+m2/o7ppo+PTS/NBK5ARpD G5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofssxBA1gj5NXGvfJu/ Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsY KyZxvxZSZBAgjhIzYDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+ IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuuhuC4pCGGx+YD2fcc5tfIAA0h/Et4 /jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3CHU2mTMwZ+B/ y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_7265043_148557982.1453131627096 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ------=_Part_7265043_148557982.1453131627096--