Re: BYTEA - Mailing list pgsql-sql

From Eugene Yin
Subject Re: BYTEA
Date
Msg-id 1592189786.7265044.1453131627096.JavaMail.yahoo@mail.yahoo.com
Whole thread Raw
In response to Re: BYTEA  (Andreas Joseph Krogh <andreas@visena.com>)
Responses Re: BYTEA
List pgsql-sql
"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, then 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 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)?  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:


På søndag 17. januar 2016 kl. 23:13:09, skrev Thomas Kellerer <spam_eater@gmx.net>:
Andreas Joseph Krogh schrieb am 17.01.2016 um 23:09:
>      > Do I  really *Need to escape/encode binary data before sending to DB
>      > then do the reverse after retrieving the data?*
>      > *
>      > *
>      > If so, what (*Java*) codes should I use to achieve 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.
 
Depends on what "works" is.
Using BLOBs (that is SQL-BLOB, not *ps.setBinaryStream etc.) with ps.setBlob/rs.getBlob and Connection.createBlob certainly doesn't work using the official driver.
 
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java#L1284-L1287
 
  public Blob createBlob() throws 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 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 using 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.
 
But, of course, if you only work with small-ish binary data, yes - BYTEA does the job.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 


Attachment

pgsql-sql by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Re: BLOBs
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: BYTEA