agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedbytea
17+ messages / 10 participants
[nested] [flat]
* bytea
@ 1999-08-24 05:34 Stanley Morgan <stan@cyberfort.com>
0 siblings, 0 replies; 17+ messages in thread
From: Stanley Morgan @ 1999-08-24 05:34 UTC (permalink / raw)
To: pgsql-sql
hi all
can smebody give me any examples about bytea's select, insert and
update?
what is diferrence between 'text' and 'bytea' types?
Thanks!
Attachments:
[text/x-vcard] stan.vcf (314B, ../../37C22EF7.B011642@cyberfort.com/2-stan.vcf)
download | inline:
begin:vcard
n:Morgan;Stanley
tel;pager:ICQ 13840917
tel;fax:USA 281-282-9904
tel;home:USA 409-766-7888
tel;work:USA 281-282-9902
x-mozilla-html:TRUE
org:CyberFort, Inc.
adr:;;;;;;
version:2.1
email;internet:stan@cyberfort.com
title:Director of Product Development
x-mozilla-cpt:;-28256
fn:Stanley Morgan
end:vcard
^ permalink raw reply [nested|flat] 17+ messages in thread
* bytea
@ 2003-02-05 09:55 Adrian Chong <achong@cecid.hku.hk>
0 siblings, 0 replies; 17+ messages in thread
From: Adrian Chong @ 2003-02-05 09:55 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a table containing a field of type bytea:
CREATE TABLE a_table (
a_field bytea
);
How can I import a file in a SQL script? What function I can use?
Thank you very much.
Adrian
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: bytea
@ 2003-02-05 10:15 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 17+ messages in thread
From: Christoph Haller @ 2003-02-05 10:15 UTC (permalink / raw)
To: pgsql-sql; +Cc: achong@cecid.hku.hk
>
> I have a table containing a field of type bytea:
>
> CREATE TABLE a_table (
> a_field bytea
> );
>
> How can I import a file in a SQL script? What function I can use?
>
The documentation says as in PostgreSQL 7.2.1 (I doubt this changed
significantly since)
Octets of certain values must be escaped (but all octet values may be
escaped) when used as part of a string literal in an SQL
statement. In general, to escape an octet, it is converted into the
three-digit octal number equivalent of its decimal octet value, and
preceded by two backslashes.
In general it goes like this
INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ;
to load the first four ASCII characters.
You did not mention how your file looks like.
There is also a C function available called PQescapeBytea
which does all the required escaping to store memory areas in bytea
columns.
Refer to Command Execution Functions within libpq - C Library for
details.
Regards, Christoph
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: bytea
@ 2003-02-05 10:25 Adrian Chong <achong@cecid.hku.hk>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 17+ messages in thread
From: Adrian Chong @ 2003-02-05 10:25 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
Hi Christoph,
Thanks for your reply. But what I want to do is loading a file of a particular path with a sql
statement in psql. Why I need to care about how the file looks like? Thanks.
Adrian
----- Original Message -----
From: "Christoph Haller" <ch@rodos.fzk.de>
To: <pgsql-sql@postgresql.org>
Cc: <achong@cecid.hku.hk>
Sent: Wednesday, February 05, 2003 6:15 PM
Subject: Re: [SQL] bytea
> >
> > I have a table containing a field of type bytea:
> >
> > CREATE TABLE a_table (
> > a_field bytea
> > );
> >
> > How can I import a file in a SQL script? What function I can use?
> >
> The documentation says as in PostgreSQL 7.2.1 (I doubt this changed
> significantly since)
>
> Octets of certain values must be escaped (but all octet values may be
> escaped) when used as part of a string literal in an SQL
> statement. In general, to escape an octet, it is converted into the
> three-digit octal number equivalent of its decimal octet value, and
> preceded by two backslashes.
>
> In general it goes like this
> INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ;
> to load the first four ASCII characters.
> You did not mention how your file looks like.
> There is also a C function available called PQescapeBytea
> which does all the required escaping to store memory areas in bytea
> columns.
> Refer to Command Execution Functions within libpq - C Library for
> details.
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: bytea
@ 2003-02-05 12:04 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 17+ messages in thread
From: Christoph Haller @ 2003-02-05 12:04 UTC (permalink / raw)
To: pgsql-sql; +Cc: achong@cecid.hku.hk
>
> Thanks for your reply. But what I want to do is loading a file of a
particular path with a sql
> statement in psql. Why I need to care about how the file looks like?
Thanks.
>
Because "non-printables" might not be properly escaped.
If they are, just use the SQL COPY command as described in the doc.
Regards, Christoph
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: bytea
@ 2003-02-07 18:54 Steve Crawford <scrawford@pinpointresearch.com>
parent: Adrian Chong <achong@cecid.hku.hk>
0 siblings, 0 replies; 17+ messages in thread
From: Steve Crawford @ 2003-02-07 18:54 UTC (permalink / raw)
To: Adrian Chong <achong@cecid.hku.hk>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
That would be convenient but there are some difficulties. Say you have a
function that worked something like:
insert.....values (...., filetobytea("/home/me/myfile"),...);
It would be a nice ability to have but the server may not be on the same
machine (or even the same type/os/filesystem) as the client so sending a
pathname to the server is of little use. Such an ability must be handled by
the client which can read/translate the local file into the appropriate byte
sequence to send to the server.
I'm toying with a client program in C program that would include the ability
to take a file on the client machine and store it in a bytea field but
haven't come up with a good way to handle the necessary buffer space (ie. I
don't see any way to "stream" data to the server - it appears I need to
allocate sufficient buffer space for the entire file plus the entire escaped
version of the file before I can generate the query which causes me some
other problems.) Any suggestions would be appreciated.
Cheers,
Steve
On Wednesday 05 February 2003 2:25 am, Adrian Chong wrote:
> Hi Christoph,
>
> Thanks for your reply. But what I want to do is loading a file of a
> particular path with a sql statement in psql. Why I need to care about how
> the file looks like? Thanks.
>
> Adrian
>
> ----- Original Message -----
> From: "Christoph Haller" <ch@rodos.fzk.de>
> To: <pgsql-sql@postgresql.org>
> Cc: <achong@cecid.hku.hk>
> Sent: Wednesday, February 05, 2003 6:15 PM
> Subject: Re: [SQL] bytea
>
> > > I have a table containing a field of type bytea:
> > >
> > > CREATE TABLE a_table (
> > > a_field bytea
> > > );
> > >
> > > How can I import a file in a SQL script? What function I can use?
> >
> > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed
> > significantly since)
> >
> > Octets of certain values must be escaped (but all octet values may be
> > escaped) when used as part of a string literal in an SQL
> > statement. In general, to escape an octet, it is converted into the
> > three-digit octal number equivalent of its decimal octet value, and
> > preceded by two backslashes.
> >
> > In general it goes like this
> > INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ;
> > to load the first four ASCII characters.
> > You did not mention how your file looks like.
> > There is also a C function available called PQescapeBytea
> > which does all the required escaping to store memory areas in bytea
> > columns.
> > Refer to Command Execution Functions within libpq - C Library for
> > details.
> >
> > Regards, Christoph
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 17+ messages in thread
* BYTEA
@ 2016-01-17 19:33 Eugene Yin <eugeneymail@ymail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Eugene Yin @ 2016-01-17 19:33 UTC (permalink / raw)
To: pgsql-sql
Pg 9.4+
Storing binary data using bytea or text data types
- Pluses
- Storing and Accessing entry utilizes the same interface when accessing any other data type or record.
- No need to track OID of a "large object" you create
- Minus
- bytea and text data type both use TOAST (details here)
- limited to 1G per entry
- 4 Billion (> 2KB) entries per table max.
- Need to escape/encode binary data before sending to DB then do the reverse after retrieving the data
- Memory requirements on the server can be steep even on a small record set.
https://wiki.postgresql.org/wiki/BinaryFilesInDB
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)?
Thanks
Eugene
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-17 20:29 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Eugene Yin <eugeneymail@ymail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2016-01-17 20:29 UTC (permalink / raw)
To: Eugene Yin <eugeneymail@ymail.com>; pgsql-sql
On 01/17/2016 11:33 AM, Eugene Yin wrote:
> Pg 9.4+
>
> Storing binary data using bytea
> <http://www.postgresql.org/docs/8.4/static/datatype-binary.html; or text
> <http://www.postgresql.org/docs/8.4/static/datatype-character.html; data
> types
>
> * Pluses
> o Storing and Accessing entry utilizes the same interface when
> accessing any other data type or record.
> o No need to track OID of a "large object" you create
> * Minus
> o bytea and text data type both use TOAST
> <http://www.postgresql.org/docs/8.4/static/storage-toast.html;
> (details here <https://wiki.postgresql.org/wiki/TOAST;)
> + limited to 1G per entry
> + 4 Billion (> 2KB) entries per table max
> <https://wiki.postgresql.org/wiki/TOAST;.
> o *Need to escape/encode binary data before sending to DB then do
> the reverse after retrieving the data *
> o Memory requirements on the server can be steep even on a small
> record set.
>
> https://wiki.postgresql.org/wiki/BinaryFilesInDB
>
>
>
> 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
>
>
>
> Thanks
>
> Eugene
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-17 22:09 Andreas Joseph Krogh <andreas@visena.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 17+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-17 22:09 UTC (permalink / raw)
To: pgsql-sql
På søndag 17. januar 2016 kl. 21:29:08, skrev Adrian Klaver <
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:
On 01/17/2016 11:33 AM, Eugene Yin wrote:
> Pg 9.4+
>
> Storing binary data using bytea
> <http://www.postgresql.org/docs/8.4/static/datatype-binary.html; or text
> <http://www.postgresql.org/docs/8.4/static/datatype-character.html; data
> types
>
> * Pluses
> o Storing and Accessing entry utilizes the same interface when
> accessing any other data type or record.
> o No need to track OID of a "large object" you create
> * Minus
> o bytea and text data type both use TOAST
> <http://www.postgresql.org/docs/8.4/static/storage-toast.html;
> (details here <https://wiki.postgresql.org/wiki/TOAST;)
> + limited to 1G per entry
> + 4 Billion (> 2KB) entries per table max
> <https://wiki.postgresql.org/wiki/TOAST;.
> o *Need to escape/encode binary data before sending to DB then do
> the reverse after retrieving the data *
> o Memory requirements on the server can be steep even on a small
> record set.
>
> https://wiki.postgresql.org/wiki/BinaryFilesInDB
>
>
>
> 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.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-17 22:13 Thomas Kellerer <spam_eater@gmx.net>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 17+ messages in thread
From: Thomas Kellerer @ 2016-01-17 22:13 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-18 00:25 Cat <cat@zip.com.au>
parent: Eugene Yin <eugeneymail@ymail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Cat @ 2016-01-18 00:25 UTC (permalink / raw)
To: Eugene Yin <eugeneymail@ymail.com>; +Cc: pgsql-sql
On Sun, Jan 17, 2016 at 07:33:38PM +0000, Eugene Yin wrote:
> Pg 9.4+
> Storing binary data using bytea or text data types
> - Pluses
> - Storing and Accessing entry utilizes the same interface when accessing any other data type or record.
> - No need to track OID of a "large object" you create
>
> - Minus
>
> - bytea and text data type both use TOAST (details here)
> - limited to 1G per entry
> - 4 Billion (> 2KB) entries per table max.
>
> - Need to escape/encode binary data before sending to DB then do the reverse after retrieving the data
> - Memory requirements on the server can be steep even on a small record set.
> https://wiki.postgresql.org/wiki/BinaryFilesInDB
Would http://multicorn.org/foreign-data-wrappers/#idfilesystem-foreign-data-wrapper be of greater
use? Not sure if it's r/w or r/o.
--
"A search of his car uncovered pornography, a homemade sex aid, women's
stockings and a Jack Russell terrier."
- http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1111118083480
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-18 10:05 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Cat <cat@zip.com.au>
0 siblings, 0 replies; 17+ messages in thread
From: Karsten Hilbert @ 2016-01-18 10:05 UTC (permalink / raw)
To: pgsql-sql
On Mon, Jan 18, 2016 at 11:25:43AM +1100, Cat wrote:
> On Sun, Jan 17, 2016 at 07:33:38PM +0000, Eugene Yin wrote:
> > Pg 9.4+
> > Storing binary data using bytea or text data types
> > - Pluses
> > - Storing and Accessing entry utilizes the same interface when accessing any other data type or record.
> > - No need to track OID of a "large object" you create
> >
> > - Minus
> >
> > - bytea and text data type both use TOAST (details here)
> > - limited to 1G per entry
> > - 4 Billion (> 2KB) entries per table max.
> >
> > - Need to escape/encode binary data before sending to DB then do the reverse after retrieving the data
> > - Memory requirements on the server can be steep even on a small record set.
> > https://wiki.postgresql.org/wiki/BinaryFilesInDB
>
> Would http://multicorn.org/foreign-data-wrappers/#idfilesystem-foreign-data-wrapper be of greater use?
It would certainly be of use to add that option to the aforementioned wiki page.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-18 13:06 Andreas Joseph Krogh <andreas@visena.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 17+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-18 13:06 UTC (permalink / raw)
To: pgsql-sql
På søndag 17. januar 2016 kl. 23:13:09, skrev Thomas Kellerer <
spam_eater@gmx.net <mailto: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.j...
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 BYTEAappears to use streams, it doesn't, and the whole
byte-array is kept in memory, both in the JAVA-appand 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-18 15:40 Eugene Yin <eugeneymail@ymail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 17+ messages in thread
From: Eugene Yin @ 2016-01-18 15:40 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: 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.j... 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 KroghCTO / Partner - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com
=
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-18 15:50 Andreas Joseph Krogh <andreas@visena.com>
parent: Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-18 15:50 UTC (permalink / raw)
To: pgsql-sql
På mandag 18. januar 2016 kl. 16:40:26, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
"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?
Again, that depends. If you plan on having 1000 simultaneous users then having
everything in memory might not be a good idea. Remember the memory consumed by
the JVM is quite a lot more the the raw byte-size of each image/blob.
"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?
It will clean it up, if it's not referenced anymore. PG will clean up its side
at the end of the transaction (at least).
"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?
As reading BLOBs (using the OID-type) really means your processing a stream of
bytes it's really up to you to decide the size of the byte-buffer you want to
use. Only this byte-buffer is kept in memory (and is freed by the GC when not
referenced anymore), which often is much less than the whole BLOB.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-18 16:30 Eugene Yin <eugeneymail@ymail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 17+ messages in thread
From: Eugene Yin @ 2016-01-18 16:30 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
To understand it further, let me give an example that I have a group of pictures (assume they a set of house pictures, such as living room, bedroom, kitchen, patio, yard, etc). I first save them (larger size) to the DB. Then when the user retrieve them, the user first see a line-up of small pics, then they pick and click one, the selected one will popup a larger picture.
I have two ways to do this:
1) BYTEA
2) OID
For BYTEA, the whole set of pictures (larger size) are retrieved (dumped) into the memories on both the Java and Pg sides, occupying the same size as the original picture is. Hence taking up larger memories.
For OID, it takes smaller memories on both Java and Pg sides (store the byte array in a buffer?). Whenever the user clicks the smaller pic, it then get the byte array from the buffer and display the larger (original) sized one, hence taking up less memories (avoiding the memory leaking)?
Thanks
Eugene
On Monday, January 18, 2016 7:51 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På mandag 18. januar 2016 kl. 16:40:26, skrev Eugene Yin <eugeneymail@ymail.com>:
"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 than5 MB. For such purpose, is BYTEA ok? If not, then how about 1 MB each?
Again, that depends. If you plan on having 1000 simultaneous users then having everything in memory might not be a good idea. Remember the memory consumed by the JVM is quite a lot more the the raw byte-size of each image/blob.
"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?
It will clean it up, if it's not referenced anymore. PG will clean up its side at the end of the transaction (at least).
"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?
As reading BLOBs (using the OID-type) really means your processing a stream of bytes it's really up to you to decide the size of the byte-buffer you want to use. Only this byte-buffer is kept in memory (and is freed by the GC when not referenced anymore), which often is much less than the whole BLOB. --Andreas Joseph KroghCTO / Partner - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com
=
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: BYTEA
@ 2016-01-18 17:26 Andreas Joseph Krogh <andreas@visena.com>
parent: Eugene Yin <eugeneymail@ymail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-18 17:26 UTC (permalink / raw)
To: pgsql-sql
På mandag 18. januar 2016 kl. 17:30:08, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
To understand it further, let me give an example that I have a group of
pictures (assume they a set of house pictures, such as living room, bedroom,
kitchen,patio, yard, etc). I first save them (larger size) to the DB. Then
when the user retrieve them, the user first see a line-up ofsmall pics, then
they pick and click one, the selected one will popup a larger picture.
I have two ways to do this:
1) BYTEA
2) OID
For BYTEA, the whole set of pictures (larger size) are retrieved (dumped) into
the memories on both the Java and Pg sides, occupying the same size as the
original picture is. Hence taking up larger memories.
For OID, it takes smaller memories on both Java and Pg sides (store the byte
array in a buffer?). Whenever the user clicks the smaller pic, it then get the
byte array from the buffer and display the larger (original) sized one, hence
taking up less memories (avoiding the memory leaking)?
One is always free to design an app in such a way that no matter how you store
the images it may still use all available memory. My point is that using BLOB
with OID (together with the pgjdbc-ng driver) lets you get away with using as
little memory as you choose. It's the same as working with a large file using
FileInputStream. Consuming such streams is done by reading chunks of it into a
pre-allocated byte-array, which is the common way in most
programming-languages. This byte-array is the only extra penalty in you app.
How you feed the contents of the stream back to the browser is up to you, and
there are lots of ways to do that inefficiently, even if the underlying
producer of data is a stream of bytes. If you are in a JEE servlet-environment
then writing to ServletOutputStream while reading form the BLOB's inputstream
is the most memory-efficient way to stream data from PG to the browser.
Note that reading BLOBs in PG requires a transaction.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2016-01-18 17:26 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-24 05:34 bytea Stanley Morgan <stan@cyberfort.com>
2003-02-05 09:55 bytea Adrian Chong <achong@cecid.hku.hk>
2003-02-05 10:15 Re: bytea Christoph Haller <ch@rodos.fzk.de>
2003-02-05 10:25 ` Adrian Chong <achong@cecid.hku.hk>
2003-02-07 18:54 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-02-05 12:04 Re: bytea Christoph Haller <ch@rodos.fzk.de>
2016-01-17 19:33 BYTEA Eugene Yin <eugeneymail@ymail.com>
2016-01-17 20:29 ` Re: BYTEA Adrian Klaver <adrian.klaver@aklaver.com>
2016-01-17 22:09 ` Re: BYTEA Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 22:13 ` Re: BYTEA Thomas Kellerer <spam_eater@gmx.net>
2016-01-18 13:06 ` Re: BYTEA Andreas Joseph Krogh <andreas@visena.com>
2016-01-18 15:40 ` Re: BYTEA Eugene Yin <eugeneymail@ymail.com>
2016-01-18 15:50 ` Re: BYTEA Andreas Joseph Krogh <andreas@visena.com>
2016-01-18 16:30 ` Re: BYTEA Eugene Yin <eugeneymail@ymail.com>
2016-01-18 17:26 ` Re: BYTEA Andreas Joseph Krogh <andreas@visena.com>
2016-01-18 00:25 ` Re: BYTEA Cat <cat@zip.com.au>
2016-01-18 10:05 ` Re: BYTEA Karsten Hilbert <Karsten.Hilbert@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox