Обсуждение: Picture with Postgres and Delphi
Hi !! Everybody I am developing app using Delphi and I have a question: I have to save pictures into my database. Each picture has 20 o 30k aprox. What is the way more optimus? That 's table will have 500000 records around. Somebody said the best way to do that was encoder the picture to field bytea but I dont know about this. Another way is save the path to the picture file but I dont like so much because I need to write to disk by OS and have permission to write a directory to OS. What do u think?? Edwin Quijada
Hi, Edwin Quijada wrote: > Hi !! Everybody > I am developing app using Delphi and I have a question: > I have to save pictures into my database. Each picture has 20 o 30k aprox. > What is the way more optimus? > That 's table will have 500000 records around. Somebody said the best way to > do that was encoder the picture to field bytea but I dont know about this. > Another way is save the path to the picture file but I dont like so much > because I need to write to disk by OS and have permission to write a > directory to OS. > What do u think?? > Yes, generally you have two choices, but you can as well store data in files and get it by stored procedures... Any way I did it with FTP (Indy component). It allowed me to cache files locally. -- Regards, Michał Zaborowski (TeXXaS)
Hi Edwin,
it depends on the components u use in Delphi. Normally Pictures and
ohter binary data is stored in the FieldType oid. I noticed that bytea
isn't mapped as TBlobField in Delphi in most components. I also don't
know if you are able to use the PG-Functions lo_import(), lo_export()
and so on with bytea. I use oid with Delphi and that works fine for me.
In Delphi you can use Procedures like TBlobField(MyField).LoadFromFile.
Post a Picture to the Server will look like this :
DataSet.Insert;
DataSet.BlobField.LoadFromFile('MyPictureFile');
DataSet.Post;
Now your pitcure is on the Server. You are also able to use standard
DBPicture-components...
Daniel
Am Di, 2003-09-09 um 18.54 schrieb Edwin Quijada:
> Hi !! Everybody
> I am developing app using Delphi and I have a question:
> I have to save pictures into my database. Each picture has 20 o 30k aprox.
> What is the way more optimus?
> That 's table will have 500000 records around. Somebody said the best way to
> do that was encoder the picture to field bytea but I dont know about this.
> Another way is save the path to the picture file but I dont like so much
> because I need to write to disk by OS and have permission to write a
> directory to OS.
> What do u think??
> Edwin Quijada
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
----- Original Message ----- From: "listy.mailowe" <listy.mailowe@wp.pl> To: <pgsql-general@postgresql.org> Sent: Tuesday, September 09, 2003 8:10 PM Subject: Re: [GENERAL] Picture with Postgres and Delphi > Hi, > > Edwin Quijada wrote: > > > Hi !! Everybody > > I am developing app using Delphi and I have a question: > > I have to save pictures into my database. Each picture has 20 o 30k aprox. > > What is the way more optimus? > > That 's table will have 500000 records around. Somebody said the best way to > > do that was encoder the picture to field bytea but I dont know about this. > > Another way is save the path to the picture file but I dont like so much > > because I need to write to disk by OS and have permission to write a > > directory to OS. > > What do u think?? > > > Yes, generally you have two choices, but you can as well store data in > files and get it by stored procedures... Any way I did it with FTP (Indy > component). It allowed me to cache files locally. > There is no need for manually storing files on filesystem, because large objects are doing that for You. I am storing whole binary files in blobs(synonym for large objects from some other platforms), and I do not remember that I had a single problem with that. Do not forget that libpq has great support for large objects, and you can store large object without actually storing them on server filesystem, so You do not need any file permissions on "upload directory" or something like that. If You are using Delphi, there is great project called "Zeos objects", and if I remember correctly it has support for large objects. Regards !
----- Original Message ----- From: "Edwin Quijada" <listas_quijada@hotmail.com> To: <pgsql-general@postgresql.org> Sent: Tuesday, September 09, 2003 6:54 PM Subject: [GENERAL] Picture with Postgres and Delphi > Hi !! Everybody > I am developing app using Delphi and I have a question: > I have to save pictures into my database. Each picture has 20 o 30k aprox. > What is the way more optimus? > That 's table will have 500000 records around. Somebody said the best way to > do that was encoder the picture to field bytea but I dont know about this. > Another way is save the path to the picture file but I dont like so much > because I need to write to disk by OS and have permission to write a > directory to OS. > What do u think?? You may choose between Bytea or large objects. I had some problems with bytea on earlier version of pg (7.2 I think), but last time I checked (7.3), all worked fine. However, I use large objects. The only disadvantage of large objects I see is that You can't dump them into textual dump. You must use binary dumps. Regards !
On Tue, 9 Sep 2003, Darko Prenosil wrote: > > ----- Original Message ----- > From: "Edwin Quijada" <listas_quijada@hotmail.com> > To: <pgsql-general@postgresql.org> > Sent: Tuesday, September 09, 2003 6:54 PM > Subject: [GENERAL] Picture with Postgres and Delphi > > > > Hi !! Everybody > > I am developing app using Delphi and I have a question: > > I have to save pictures into my database. Each picture has 20 o 30k aprox. > > What is the way more optimus? > > That 's table will have 500000 records around. Somebody said the best way > to > > do that was encoder the picture to field bytea but I dont know about this. > > Another way is save the path to the picture file but I dont like so much > > because I need to write to disk by OS and have permission to write a > > directory to OS. > > What do u think?? > > You may choose between Bytea or large objects. > I had some problems with bytea on earlier version of pg (7.2 I think), but > last time I checked (7.3), all worked fine. > However, I use large objects. > The only disadvantage of large objects I see is that You can't dump them > into textual dump. You must use binary dumps. for portability, I've always base64 encoded and stored them as straight text. That works well too.
I'm a big fan of bytea. In every case where I've done the filesystem method I wished I hadn't. Jon On Tue, 9 Sep 2003, Edwin Quijada wrote: > Hi !! Everybody > I am developing app using Delphi and I have a question: > I have to save pictures into my database. Each picture has 20 o 30k aprox. > What is the way more optimus? > That 's table will have 500000 records around. Somebody said the best way to > do that was encoder the picture to field bytea but I dont know about this. > Another way is save the path to the picture file but I dont like so much > because I need to write to disk by OS and have permission to write a > directory to OS. > What do u think?? > Edwin Quijada > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
--- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > I'm a big fan of bytea. In every case where I've > done the filesystem > method I wished I hadn't. For the education of me and maybe others too, why was that? i.e. what problems did you run into, that bytea avoids? __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
> For the education of me and maybe others too, why was > that? i.e. what problems did you run into, that bytea avoids? > Compared to the filesystem, bytea provides data integrity. Bytea gives you remote access, which you can cache if needed. Bytea gives you the same permissions as anything else in Postgres, so you don't have to worry about that separately. Compared to BLOBs, bytea's are just simpler. You can select them with a single statement, you don't have to worry about leaving unreferenced BLOBs, and, after 4 billion inserts, byteas are still meaningful while BLOBs might not be. (due to OID problems). Jon > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com >
What is the size limit of bytea, I thought it was 8K? How do you dump your database when you have bytea, do you need to do a binary dump? What are you using to insert the binary data? Thank you in advance. Guy Jonathan Bartlett wrote: >>For the education of me and maybe others too, why was >>that? i.e. what problems did you run into, that bytea avoids? >> >> >> > >Compared to the filesystem, bytea provides data integrity. > >Bytea gives you remote access, which you can cache if needed. > >Bytea gives you the same permissions as anything else in Postgres, so you >don't have to worry about that separately. > >Compared to BLOBs, bytea's are just simpler. You can select them with a >single statement, you don't have to worry about leaving unreferenced >BLOBs, and, after 4 billion inserts, byteas are still meaningful while >BLOBs might not be. (due to OID problems). > >Jon > > > > >>__________________________________ >>Do you Yahoo!? >>Yahoo! SiteBuilder - Free, easy-to-use web site design software >>http://sitebuilder.yahoo.com >> >> >> > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on.
I'd actually like to get some comments on this too because for compatibility and throughput issues, I would think that storing the file path in the database instead of the actually file would be "better". I've done one application like this in the past that very worked well. I'm getting ready to do the final e-commerce integration on a new site and if there is an advantage to storing the files in the database (in this case about 300 jpeg images for a t-shirt site) I'll try that out. I'll have to research that base64 encoding part because I'll only every do text dumps. Keith- Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > I'm a big fan of bytea. In every case where I've > > done the filesystem > > method I wished I hadn't. > > For the education of me and maybe others too, why was > that? i.e. what problems did you run into, that bytea avoids? > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Is the size limit 8K for 'text' field types as well? ----- Original Message ----- From: "Guy Fraser" <guy@incentre.net> To: <pgsql-general@postgresql.org> Sent: Wednesday, September 10, 2003 9:40 AM Subject: Re: [GENERAL] Picture with Postgres and Delphi > What is the size limit of bytea, I thought it was 8K? > > How do you dump your database when you have bytea, do you need to do a > binary dump? > > What are you using to insert the binary data? > > Thank you in advance. > > Guy > > Jonathan Bartlett wrote: > > >>For the education of me and maybe others too, why was > >>that? i.e. what problems did you run into, that bytea avoids? > >> > >> > >> > > > >Compared to the filesystem, bytea provides data integrity. > > > >Bytea gives you remote access, which you can cache if needed. > > > >Bytea gives you the same permissions as anything else in Postgres, so you > >don't have to worry about that separately. > > > >Compared to BLOBs, bytea's are just simpler. You can select them with a > >single statement, you don't have to worry about leaving unreferenced > >BLOBs, and, after 4 billion inserts, byteas are still meaningful while > >BLOBs might not be. (due to OID problems). > > > >Jon > > > > > > > > > >>__________________________________ > >>Do you Yahoo!? > >>Yahoo! SiteBuilder - Free, easy-to-use web site design software > >>http://sitebuilder.yahoo.com > >> > >> > >> > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > -- > Guy Fraser > Network Administrator > The Internet Centre > 780-450-6787 , 1-888-450-6787 > > There is a fine line between genius and lunacy, fear not, walk the > line with pride. Not all things will end up as you wanted, but you > will certainly discover things the meek and timid will miss out on. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Network Administrator wrote: >I'll have to research that base64 encoding part because I'll >only every do text dumps. > Then the base64 storage in the database is perfect.
> I'll try that out. I'll have to research that base64 encoding part because I'll > only every do text dumps. Oh yea, I forgot, bytea columns come out just fine in text dumps (BLOBs don't, however). Jon > > Keith- > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > I'm a big fan of bytea. In every case where I've > > > done the filesystem > > > method I wished I hadn't. > > > > For the education of me and maybe others too, why was > > that? i.e. what problems did you run into, that bytea avoids? > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > http://sitebuilder.yahoo.com > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > > -- > Keith C. Perry > Director of Networks & Applications > VCSN, Inc. > http://vcsn.com > > ____________________________________ > This email account is being host by: > VCSN, Inc : http://vcsn.com > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
> > What is the size limit of bytea, I thought it was 8K?
No limit that I've found. Some are several meg.
> > How do you dump your database when you have bytea, do you need to do a
> > binary dump?
Nope. pg_dump automagically escapes everything.
> > What are you using to insert the binary data?
Perl example:
my $COMPLETED_TEMPLATE_VARS_INSERT = <<EOF; insert into
completed_template_vars (completed_template, name, value, binvalue) VALUES
(?, ?, ?, ?)
EOF
$sth = $dbh->prepare($COMPLETED_TEMPLATE_VARS_INSERT);
$value = undef;
$binvalue = $field->{BINANS};
$value = $field->{ANS} unless $binvalue;
$sth->bind_param(1, $self->getOID);
$sth->bind_param(2, $name);
$sth->bind_param(3, $value);
$sth->bind_param(4, $binvalue, DBI::SQL_BINARY);
$sth->execute || die("DBERROR:${DBI::errstr}:");
Note that I explicityl set DBI::SQL_BINARY.
Now, for php, you do the following:
$logodata = pg_escape_bytea($tmpdata);
$tmpsql = "update advertisements set $column_name = '$logodata'::bytea where object_id = $advertisement_oid";
$tmp = $db->query($tmpsql);
I never got it to work with parameterized queries, but this works fine for
me. To select it back out, you need to do:
$q = $db->query("select teaser_logo_gif_image from advertisements where
object_id = ?::int8", array($_GET['advertisement']));
$row = $q->fetchrow();
$data = pg_unescape_bytea($row[0]);
NOTE that many versions of PHP include pg_escape_bytea but NOT
pg_unescape_bytea. Look in the docs to see which function appeared in
which version.
Jon
> >
> > Jonathan Bartlett wrote:
> >
> > >>For the education of me and maybe others too, why was
> > >>that? i.e. what problems did you run into, that bytea avoids?
> > >>
> > >>
> > >>
> > >
> > >Compared to the filesystem, bytea provides data integrity.
> > >
> > >Bytea gives you remote access, which you can cache if needed.
> > >
> > >Bytea gives you the same permissions as anything else in Postgres, so you
> > >don't have to worry about that separately.
> > >
> > >Compared to BLOBs, bytea's are just simpler. You can select them with a
> > >single statement, you don't have to worry about leaving unreferenced
> > >BLOBs, and, after 4 billion inserts, byteas are still meaningful while
> > >BLOBs might not be. (due to OID problems).
> > >
> > >Jon
> > >
> > >
> > >
> > >
> > >>__________________________________
> > >>Do you Yahoo!?
> > >>Yahoo! SiteBuilder - Free, easy-to-use web site design software
> > >>http://sitebuilder.yahoo.com
> > >>
> > >>
> > >>
> > >
> > >
> > >---------------------------(end of broadcast)---------------------------
> > >TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> > >
> > >
> > >
> > >
> >
> > --
> > Guy Fraser
> > Network Administrator
> > The Internet Centre
> > 780-450-6787 , 1-888-450-6787
> >
> > There is a fine line between genius and lunacy, fear not, walk the
> > line with pride. Not all things will end up as you wanted, but you
> > will certainly discover things the meek and timid will miss out on.
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
text types are limited to ~1 Gig depending on things like encoding and what not, you might get as much as 2 gig per record. On Wed, 10 Sep 2003, Derrick Betts wrote: > Is the size limit 8K for 'text' field types as well? > > ----- Original Message ----- > From: "Guy Fraser" <guy@incentre.net> > To: <pgsql-general@postgresql.org> > Sent: Wednesday, September 10, 2003 9:40 AM > Subject: Re: [GENERAL] Picture with Postgres and Delphi > > > > What is the size limit of bytea, I thought it was 8K? > > > > How do you dump your database when you have bytea, do you need to do a > > binary dump? > > > > What are you using to insert the binary data? > > > > Thank you in advance. > > > > Guy > > > > Jonathan Bartlett wrote: > > > > >>For the education of me and maybe others too, why was > > >>that? i.e. what problems did you run into, that bytea avoids? > > >> > > >> > > >> > > > > > >Compared to the filesystem, bytea provides data integrity. > > > > > >Bytea gives you remote access, which you can cache if needed. > > > > > >Bytea gives you the same permissions as anything else in Postgres, so you > > >don't have to worry about that separately. > > > > > >Compared to BLOBs, bytea's are just simpler. You can select them with a > > >single statement, you don't have to worry about leaving unreferenced > > >BLOBs, and, after 4 billion inserts, byteas are still meaningful while > > >BLOBs might not be. (due to OID problems). > > > > > >Jon > > > > > > > > > > > > > > >>__________________________________ > > >>Do you Yahoo!? > > >>Yahoo! SiteBuilder - Free, easy-to-use web site design software > > >>http://sitebuilder.yahoo.com > > >> > > >> > > >> > > > > > > > > >---------------------------(end of broadcast)--------------------------- > > >TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > -- > > Guy Fraser > > Network Administrator > > The Internet Centre > > 780-450-6787 , 1-888-450-6787 > > > > There is a fine line between genius and lunacy, fear not, walk the > > line with pride. Not all things will end up as you wanted, but you > > will certainly discover things the meek and timid will miss out on. > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Well, assuming you can store a reasonable large text file in any database, you can also look at uuencoding / base-64 encoding as a way of storing things in the database. It's more easily ported than either bytea or large objects. On Wed, 10 Sep 2003, Network Administrator wrote: > I'd actually like to get some comments on this too because for compatibility and > throughput issues, I would think that storing the file path in the database > instead of the actually file would be "better". I've done one application like > this in the past that very worked well. I'm getting ready to do the final > e-commerce integration on a new site and if there is an advantage to storing the > files in the database (in this case about 300 jpeg images for a t-shirt site) > I'll try that out. I'll have to research that base64 encoding part because I'll > only every do text dumps. > > Keith- > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > I'm a big fan of bytea. In every case where I've > > > done the filesystem > > > method I wished I hadn't. > > > > For the education of me and maybe others too, why was > > that? i.e. what problems did you run into, that bytea avoids? > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > http://sitebuilder.yahoo.com > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > >
Network Administrator wrote: > I'd actually like to get some comments on this too because for compatibility and > throughput issues, I would think that storing the file path in the database > instead of the actually file would be "better". I've done one application like > this in the past that very worked well. I'm getting ready to do the final > e-commerce integration on a new site and if there is an advantage to storing the > files in the database (in this case about 300 jpeg images for a t-shirt site) > I'll try that out. I'll have to research that base64 encoding part because I'll > only every do text dumps. Storing the file path on the database can work well, but with modern databases, there shouldn't be any issue with performance with storing BLOB data. If you find there is an issue, then you should just set up a table holding this information along with a key field. This key would be a foreign key on your table holding the other information (in the case of the t-shirt site, perhaps the other table holds information relating to the style, size, etc). In effect, what you are doing by storing the path is the path is your "foreign key", to the filesystem "database". That is, you are using the filesystem as a database. One of the issues with storing the path on the database, instead of storing the BLOB, is that the data on the filesystem can change or be moved, without the database knowing about it. You then have "broken keys", keys that refer to data that is no longer the same as it was when the data was inserted into the database (the data is different, or it doesn't exist, or it is corrupted in some manner). There is also the issue of backups and restoring the state of the database. If you take a snapshot of the database at any point in time for a backup, you need to do the same with the pictures as well, and when you restore, remember to restore both. If you kept the data in the database, you just need to restore it alone. I know there are other reasons as well - hopefully others on here will point them out, as my memory is a bit fuzzy right now... Andrew Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
"Derrick Betts" <Derrick@grifflink.com> writes: > Is the size limit 8K for 'text' field types as well? There is no size limit (OK, a very very large one) on either "text" or "bytea" in modern versions of Postgres. -Doug
How do you deal with backing up the images? Right now i can remote backup my filesystem using rsync to an offsite location many times a day, only taking a very small amount of I/O, bandwidth and time. Dealing with the backup scared me away from using postgres in the first place. The idea of doing a 200gb dump multiple times a day on an image database scares me. So does doing a vacuum on it. The I/O, time and bandwidth required to do this is daunting. Are there any suggestions on how to do incremental backups of the images and any other suggestions on performance? In the future I'd like to move some filesystem images to postgres to have a centralized storage. It would make some things easier, but i'm not sure it's worth the additional problems. Hopefully i'm imagining the problems. --brian On Tuesday, September 9, 2003, at 08:56 PM, Jonathan Bartlett wrote: >> For the education of me and maybe others too, why was >> that? i.e. what problems did you run into, that bytea avoids? >> > > Compared to the filesystem, bytea provides data integrity. > > Bytea gives you remote access, which you can cache if needed. > > Bytea gives you the same permissions as anything else in Postgres, so > you > don't have to worry about that separately. > > Compared to BLOBs, bytea's are just simpler. You can select them with > a > single statement, you don't have to worry about leaving unreferenced > BLOBs, and, after 4 billion inserts, byteas are still meaningful while > BLOBs might not be. (due to OID problems). > > Jon > > >> __________________________________ >> Do you Yahoo!? >> Yahoo! SiteBuilder - Free, easy-to-use web site design software >> http://sitebuilder.yahoo.com >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
On Wednesday 10 September 2003 17:17, Derrick Betts wrote: > Is the size limit 8K for 'text' field types as well? > > > What is the size limit of bytea, I thought it was 8K? Not for some time now - the TOAST system (Tom Lane's work IIRC) means you can store large text fields in a table. By large I mean MB large, not 64k or something. I think there's a section on TOAST in the manuals. -- Richard Huxton Archonet Ltd
Quoting Andrew Ayers <aayers@eldocomp.com>: > Network Administrator wrote: > > I'd actually like to get some comments on this too because for > compatibility and > > throughput issues, I would think that storing the file path in the > database > > instead of the actually file would be "better". I've done one application > like > > this in the past that very worked well. I'm getting ready to do the final > > e-commerce integration on a new site and if there is an advantage to > storing the > > files in the database (in this case about 300 jpeg images for a t-shirt > site) > > I'll try that out. I'll have to research that base64 encoding part because > I'll > > only every do text dumps. > > Storing the file path on the database can work well, but with modern > databases, there shouldn't be any issue with performance with storing > BLOB data. If you find there is an issue, then you should just set up a > table holding this information along with a key field. This key would be > a foreign key on your table holding the other information (in the case > of the t-shirt site, perhaps the other table holds information relating > to the style, size, etc). > > In effect, what you are doing by storing the path is the path is your > "foreign key", to the filesystem "database". That is, you are using the > filesystem as a database. The issue I would be referring to might be a very narrow on in scope as a I think about this. In order to do this in the most portable way, I would need to encode the images so that I can maintain my currrent procedures for database disaster recovery, maintanance, etc. In the previous site I did, the html pages were all dynamically generally by mpl's (mod_perl scripts). In the case of that site, the image's size could be controlled by the users. So if you wanted to scale and image to 800x600 or 320x240, the script simple read in the file (pointed to by the database) and then scaled then and wrote they to the screen. If the case of storing the images as BLOBS what I am worried about generally speaking is the conversation coding time "penalty" associated with each image that would be displayed. This really has nothing to do with the database since the obvious things to do is to NOT store the data in an encoded form. The penalty now is in the arena of our company procedures. Of course the other piece of this you nail on the head. Related information for the shirts, (size, color, price) are part of another table. Looking at the docs there is also a question of escaping certain characters. Now I'm wondering just how exactly do I get the images into the database? I would have thought I could just copy them from STDIN or something along those lines (I haven't even considered this until now). In seems like managementwise this might be more difficult for two reasons. One, just how to I get 250 images or ANY arbitrary number of images into the database? I'm guessing I could script it but then two, what about the escaping issues? With the storing the file path the only thing I had to do is run a "recatalog" script we wrote which would write to the database all the file paths. That made it real easy for the admins do the FTP or HTTP upload and then run that script from the admin page we built them. The application code, included the ability to detect these updates and to recognize when a file was actually not of the system. > One of the issues with storing the path on the database, instead of > storing the BLOB, is that the data on the filesystem can change or be > moved, without the database knowing about it. You then have "broken > keys", keys that refer to data that is no longer the same as it was when > the data was inserted into the database (the data is different, or it > doesn't exist, or it is corrupted in some manner). There is also the > issue of backups and restoring the state of the database. If you take a > snapshot of the database at any point in time for a backup, you need to > do the same with the pictures as well, and when you restore, remember to > restore both. If you kept the data in the database, you just need to > restore it alone. The issue of backup/restore as you stated above is more procedural than technical (simply make sure you backup client databases with their website data) for exactly the scenario you gave. You could still have human errors (i.e. upload file, don't recatalog, crash, recover, I get a call- "where are my files?") but that is acceptable compared having to dump in a non-portable, non-editable (binary?) format. > I know there are other reasons as well - hopefully others on here will > point them out, as my memory is a bit fuzzy right now... > > Andrew Ayers > Phoenix, Arizona You're fuzzy- I'm perplexed- a fork just appeared in my road! If anyone has strategies for loading large amount of files into the database, I'd love to heard them. I'm going to have to play around with this. I just don't think I have the time to do it before this project is due. I *love* when that happens... *snicker* :) > -- CONFIDENTIALITY NOTICE -- > > This message is intended for the sole use of the individual and entity to > whom it is addressed, and may contain information that is privileged, > confidential and exempt from disclosure under applicable law. If you are not > the intended addressee, nor authorized to receive for the intended addressee, > you are hereby notified that you may not use, copy, disclose or distribute to > anyone the message or any information contained in the message. If you have > received this message in error, please immediately advise the sender by reply > email, and delete the message. Thank you. > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
I think that is what someone mentioned earlier in this thread. I guess you can do that with a "text" type which according to the docs doesn't have an upper limit (although there is that 1Gb limit for a single character string. The way that entire paragraph reads would have me to believe that it means you and do 1Gb of characters including the EOL but then there is no restriction on the number of lines themselves) Quoting "scott.marlowe" <scott.marlowe@ihs.com>: > Well, assuming you can store a reasonable large text file in any database, > you can also look at uuencoding / base-64 encoding as a way of storing > things in the database. > > It's more easily ported than either bytea or large objects. > > On Wed, 10 Sep 2003, Network Administrator wrote: > > > I'd actually like to get some comments on this too because for > compatibility and > > throughput issues, I would think that storing the file path in the > database > > instead of the actually file would be "better". I've done one application > like > > this in the past that very worked well. I'm getting ready to do the final > > e-commerce integration on a new site and if there is an advantage to > storing the > > files in the database (in this case about 300 jpeg images for a t-shirt > site) > > I'll try that out. I'll have to research that base64 encoding part because > I'll > > only every do text dumps. > > > > Keith- > > > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > > I'm a big fan of bytea. In every case where I've > > > > done the filesystem > > > > method I wished I hadn't. > > > > > > For the education of me and maybe others too, why was > > > that? i.e. what problems did you run into, that bytea avoids? > > > > > > __________________________________ > > > Do you Yahoo!? > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > http://sitebuilder.yahoo.com > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > > > > > > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
This discussion provides an opportunity to capture the essentials of how to store and retrieve images using PostgreSQL, and the issues (performance, administration etc.) associated with each possible method. A discussion article on Techdocs (or even General Bits?) would be a fine thing. My observation is that questions on this topic are coming up on the lists more and more frequently. I'm not volunteering, not having the competence to write on the subject. But maybe someone else will be moved to attempt it. __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
Darko Prenosil wrote: > > There is no need for manually storing files on filesystem, because large > objects are doing that for You. I am storing whole binary files in > blobs(synonym for large objects from some other platforms), and I do not > remember that I had a single problem with that. Do not forget that libpq has > great support for large objects, and you can store large object without > actually storing them on server filesystem, so You do not need any file > permissions on "upload directory" or something like that. > The pictures are prepared for web. Storing in files is faster from that side. That system is calling db every 30 mins... > If You are using Delphi, there is great project called "Zeos objects", and > if I remember correctly it has support for large objects. > Zeos are useing a lot of memory... Regards, Michał Zaborowski (TeXXaS)
Hi, I think both ways can be used. For editing/working pictures can be stored in db - easy interface and others... For speed of reading files should be stored outside db. Regards, Michał Zaborowski (TeXXaS)
Thanks that is extremely helpfull.
Guy
Jonathan Bartlett wrote:
>>>What is the size limit of bytea, I thought it was 8K?
>>>
>>>
>
>No limit that I've found. Some are several meg.
>
>
>
>>>How do you dump your database when you have bytea, do you need to do a
>>>binary dump?
>>>
>>>
>
>Nope. pg_dump automagically escapes everything.
>
>
>
>>>What are you using to insert the binary data?
>>>
>>>
>
>Perl example:
>
>my $COMPLETED_TEMPLATE_VARS_INSERT = <<EOF; insert into
>completed_template_vars (completed_template, name, value, binvalue) VALUES
>(?, ?, ?, ?)
>EOF
>
> $sth = $dbh->prepare($COMPLETED_TEMPLATE_VARS_INSERT);
> $value = undef;
> $binvalue = $field->{BINANS};
> $value = $field->{ANS} unless $binvalue;
> $sth->bind_param(1, $self->getOID);
> $sth->bind_param(2, $name);
> $sth->bind_param(3, $value);
> $sth->bind_param(4, $binvalue, DBI::SQL_BINARY);
> $sth->execute || die("DBERROR:${DBI::errstr}:");
>
>Note that I explicityl set DBI::SQL_BINARY.
>
>Now, for php, you do the following:
>
>$logodata = pg_escape_bytea($tmpdata);
>$tmpsql = "update advertisements set $column_name = '$logodata'::bytea where object_id = $advertisement_oid";
>$tmp = $db->query($tmpsql);
>
>I never got it to work with parameterized queries, but this works fine for
>me. To select it back out, you need to do:
>
>$q = $db->query("select teaser_logo_gif_image from advertisements where
>object_id = ?::int8", array($_GET['advertisement']));
>$row = $q->fetchrow();
>$data = pg_unescape_bytea($row[0]);
>
>NOTE that many versions of PHP include pg_escape_bytea but NOT
>pg_unescape_bytea. Look in the docs to see which function appeared in
>which version.
>
>Jon
>
>
Richard Huxton <dev@archonet.com> writes:
> On Wednesday 10 September 2003 17:17, Derrick Betts wrote:
>>> What is the size limit of bytea, I thought it was 8K?
> Not for some time now - the TOAST system (Tom Lane's work IIRC)
Not my work, Jan Wieck's. But yes, the 8K limit is ancient history.
regards, tom lane
> What is the size limit of bytea, I thought it was 8K? No limit > How do you dump your database when you have bytea, do you need to do a > binary dump? Just dump it normally. > What are you using to insert the binary data? Depends. For php, I use pg_escape_bytea, and just use it as an in-query string value. For perl, I use bind_param with SQL_BINARY. Jon > > Thank you in advance. > > Guy > > Jonathan Bartlett wrote: > > >>For the education of me and maybe others too, why was > >>that? i.e. what problems did you run into, that bytea avoids? > >> > >> > >> > > > >Compared to the filesystem, bytea provides data integrity. > > > >Bytea gives you remote access, which you can cache if needed. > > > >Bytea gives you the same permissions as anything else in Postgres, so you > >don't have to worry about that separately. > > > >Compared to BLOBs, bytea's are just simpler. You can select them with a > >single statement, you don't have to worry about leaving unreferenced > >BLOBs, and, after 4 billion inserts, byteas are still meaningful while > >BLOBs might not be. (due to OID problems). > > > >Jon > > > > > > > > > >>__________________________________ > >>Do you Yahoo!? > >>Yahoo! SiteBuilder - Free, easy-to-use web site design software > >>http://sitebuilder.yahoo.com > >> > >> > >> > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > -- > Guy Fraser > Network Administrator > The Internet Centre > 780-450-6787 , 1-888-450-6787 > > There is a fine line between genius and lunacy, fear not, walk the > line with pride. Not all things will end up as you wanted, but you > will certainly discover things the meek and timid will miss out on. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Wednesday 10 September 2003 21:36, Michał Zaborowski wrote: > Darko Prenosil wrote: > > There is no need for manually storing files on filesystem, because large > > objects are doing that for You. I am storing whole binary files in > > blobs(synonym for large objects from some other platforms), and I do not > > remember that I had a single problem with that. Do not forget that libpq > > has great support for large objects, and you can store large object > > without actually storing them on server filesystem, so You do not need > > any file permissions on "upload directory" or something like that. > > The pictures are prepared for web. Storing in files is faster from > that side. That system is calling db every 30 mins... > > > If You are using Delphi, there is great project called "Zeos objects", > > and if I remember correctly it has support for large objects. > > Zeos are useing a lot of memory... > > Regards, > Michał Zaborowski (TeXXaS) > I do not remember that this was the problem whan I used it. I was also diging through that code, because I was writing something similar to dataset for QT. What actually "Zeos are useing a lot of memory..." means ? A lot of alocated space for result ? Regards !
>> If You are using Delphi, there is great project called "Zeos >> objects", and if I remember correctly it has support for large >> objects. >> > Zeos are useing a lot of memory... Another issue I had with Zeos was that when I looked into possibly using those components (this was probably over a year ago now), and tried connecting to a database which is configured for md5 password encryption, I got some kind of error message about that (md5 password authentication) not being supported. Perhaps it works differently now, but be forewarned to look at that if you need that type of authentication for your database. ~Berend Tober
I thought "bytea" was PG's version of BLOBs. I don't see a "blob" type in the current docs. Also, I saw your example code as well. I use Perl as well but I use the native Pg.pm module. There doesn't seem to be a way to switch the input to binary data but there is support for what is called "large object" which is I think what I might want. If you or anyone else out there has worked with bytea's with module, I appreciate some direction and any other wisdom (e.g. pros/cons, catch22's, etc). Thanks! Learning everyday... Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > I'll try that out. I'll have to research that base64 encoding part because > I'll > > only every do text dumps. > > Oh yea, I forgot, bytea columns come out just fine in text dumps (BLOBs > don't, however). > > Jon > > > > Keith- > > > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > > I'm a big fan of bytea. In every case where I've > > > > done the filesystem > > > > method I wished I hadn't. > > > > > > For the education of me and maybe others too, why was > > > that? i.e. what problems did you run into, that bytea avoids? > > > > > > __________________________________ > > > Do you Yahoo!? > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > http://sitebuilder.yahoo.com > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > > > > > -- > > Keith C. Perry > > Director of Networks & Applications > > VCSN, Inc. > > http://vcsn.com > > > > ____________________________________ > > This email account is being host by: > > VCSN, Inc : http://vcsn.com > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
BLOBs are PostgreSQL wierdness from before PostgreSQL had TOAST (which removed the 8kb row limit). See http://www.postgresql.org/docs/7.3/static/largeobjects.html For info on the old BLOB interface. Jon On Thu, 11 Sep 2003, Network Administrator wrote: > I thought "bytea" was PG's version of BLOBs. I don't see a "blob" type in the > current docs. > > Also, I saw your example code as well. I use Perl as well but I use the native > Pg.pm module. There doesn't seem to be a way to switch the input to binary data > but there is support for what is called "large object" which is I think what I > might want. If you or anyone else out there has worked with bytea's with > module, I appreciate some direction and any other wisdom (e.g. pros/cons, > catch22's, etc). Thanks! > > Learning everyday... > > > > Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > > > I'll try that out. I'll have to research that base64 encoding part because > > I'll > > > only every do text dumps. > > > > Oh yea, I forgot, bytea columns come out just fine in text dumps (BLOBs > > don't, however). > > > > Jon > > > > > > Keith- > > > > > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > > > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > > > I'm a big fan of bytea. In every case where I've > > > > > done the filesystem > > > > > method I wished I hadn't. > > > > > > > > For the education of me and maybe others too, why was > > > > that? i.e. what problems did you run into, that bytea avoids? > > > > > > > > __________________________________ > > > > Do you Yahoo!? > > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > > http://sitebuilder.yahoo.com > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 8: explain analyze is your friend > > > > > > > > > > -- > > > Keith C. Perry > > > Director of Networks & Applications > > > VCSN, Inc. > > > http://vcsn.com > > > > > > ____________________________________ > > > This email account is being host by: > > > VCSN, Inc : http://vcsn.com > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > -- > Keith C. Perry > Director of Networks & Applications > VCSN, Inc. > http://vcsn.com > > ____________________________________ > This email account is being host by: > VCSN, Inc : http://vcsn.com >
Ahh ok, thats in the programmers docs. Ok, so I'm guessing that really large object support (BLOBs) are depreciated in favor of using the "bytea" or "text" types, yes? I would this this to be true since your "object" is contain within a single record and non spanned because of row limit (i.e. before TOAST). So then the "large object" function in the Pg.pm are not what I'm looking for. I'm back to square one- How does one actually insert a file into a table using Pg.pm or more importantly psql? I'm more concerned about doing this on the system first since that is where I would be scripting the initial load of all the images- something like that should not be done via a web client. Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > BLOBs are PostgreSQL wierdness from before PostgreSQL had TOAST (which > removed the 8kb row limit). See > > http://www.postgresql.org/docs/7.3/static/largeobjects.html > > For info on the old BLOB interface. > > Jon > > On Thu, 11 Sep 2003, Network Administrator wrote: > > > I thought "bytea" was PG's version of BLOBs. I don't see a "blob" type in > the > > current docs. > > > > Also, I saw your example code as well. I use Perl as well but I use the > native > > Pg.pm module. There doesn't seem to be a way to switch the input to binary > data > > but there is support for what is called "large object" which is I think > what I > > might want. If you or anyone else out there has worked with bytea's with > > module, I appreciate some direction and any other wisdom (e.g. pros/cons, > > catch22's, etc). Thanks! > > > > Learning everyday... > > > > > > > > Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > > > > > I'll try that out. I'll have to research that base64 encoding part > because > > > I'll > > > > only every do text dumps. > > > > > > Oh yea, I forgot, bytea columns come out just fine in text dumps (BLOBs > > > don't, however). > > > > > > Jon > > > > > > > > Keith- > > > > > > > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > > > > > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > > > > I'm a big fan of bytea. In every case where I've > > > > > > done the filesystem > > > > > > method I wished I hadn't. > > > > > > > > > > For the education of me and maybe others too, why was > > > > > that? i.e. what problems did you run into, that bytea avoids? > > > > > > > > > > __________________________________ > > > > > Do you Yahoo!? > > > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > > > http://sitebuilder.yahoo.com > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > > > TIP 8: explain analyze is your friend > > > > > > > > > > > > > -- > > > > Keith C. Perry > > > > Director of Networks & Applications > > > > VCSN, Inc. > > > > http://vcsn.com > > > > > > > > ____________________________________ > > > > This email account is being host by: > > > > VCSN, Inc : http://vcsn.com > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > -- > > Keith C. Perry > > Director of Networks & Applications > > VCSN, Inc. > > http://vcsn.com > > > > ____________________________________ > > This email account is being host by: > > VCSN, Inc : http://vcsn.com > > > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
> So then the "large object" function in the Pg.pm are not what I'm looking for. > I'm back to square one- How does one actually insert a file into a table using > Pg.pm or more importantly psql? I'm more concerned about doing this on the > system first since that is where I would be scripting the initial load of all > the images- something like that should not be done via a web client. See if you have a function like pg_escape_bytea and pg_unescape_bytea. If you do, you can escape the data, and then just stick it in quotes in your query. As far as selecting the data, I don't think Perl needs to unescape it, but I could be wrong. Jon > > Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > > BLOBs are PostgreSQL wierdness from before PostgreSQL had TOAST (which > > removed the 8kb row limit). See > > > > http://www.postgresql.org/docs/7.3/static/largeobjects.html > > > > For info on the old BLOB interface. > > > > Jon > > > > On Thu, 11 Sep 2003, Network Administrator wrote: > > > > > I thought "bytea" was PG's version of BLOBs. I don't see a "blob" type in > > the > > > current docs. > > > > > > Also, I saw your example code as well. I use Perl as well but I use the > > native > > > Pg.pm module. There doesn't seem to be a way to switch the input to binary > > data > > > but there is support for what is called "large object" which is I think > > what I > > > might want. If you or anyone else out there has worked with bytea's with > > > module, I appreciate some direction and any other wisdom (e.g. pros/cons, > > > catch22's, etc). Thanks! > > > > > > Learning everyday... > > > > > > > > > > > > Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > > > > > > > I'll try that out. I'll have to research that base64 encoding part > > because > > > > I'll > > > > > only every do text dumps. > > > > > > > > Oh yea, I forgot, bytea columns come out just fine in text dumps (BLOBs > > > > don't, however). > > > > > > > > Jon > > > > > > > > > > Keith- > > > > > > > > > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > > > > > > > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > > > > > I'm a big fan of bytea. In every case where I've > > > > > > > done the filesystem > > > > > > > method I wished I hadn't. > > > > > > > > > > > > For the education of me and maybe others too, why was > > > > > > that? i.e. what problems did you run into, that bytea avoids? > > > > > > > > > > > > __________________________________ > > > > > > Do you Yahoo!? > > > > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > > > > http://sitebuilder.yahoo.com > > > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > > > > TIP 8: explain analyze is your friend > > > > > > > > > > > > > > > > -- > > > > > Keith C. Perry > > > > > Director of Networks & Applications > > > > > VCSN, Inc. > > > > > http://vcsn.com > > > > > > > > > > ____________________________________ > > > > > This email account is being host by: > > > > > VCSN, Inc : http://vcsn.com > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > -- > > > Keith C. Perry > > > Director of Networks & Applications > > > VCSN, Inc. > > > http://vcsn.com > > > > > > ____________________________________ > > > This email account is being host by: > > > VCSN, Inc : http://vcsn.com > > > > > > > > -- > Keith C. Perry > Director of Networks & Applications > VCSN, Inc. > http://vcsn.com > > ____________________________________ > This email account is being host by: > VCSN, Inc : http://vcsn.com >
On Thu, Sep 11, 2003 at 10:08:48AM -0700, Jonathan Bartlett wrote: > BLOBs are PostgreSQL wierdness from before PostgreSQL had TOAST (which > removed the 8kb row limit). See Actually, blobs were invented by the guys at Interbase many years ago. There's a nice history on them on the ibphoenix site: http://www.ibphoenix.com/a4355.htm The whole TOAST concept uses a very similar mechanism (using a pointer to an object that's somewhere else), but for toastable fields the handling is transparent to the user. That is, the user just gets or sets the field in the same way he would with any standard datatype. With blobs, you had to use special syntax. That's what I know, at least. I'm sure somebody will correct me if there's anything wrong. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Pensar que el espectro que vemos es ilusorio no lo despoja de espanto, sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)
Ok, I went through my 7.3.3. and 7.3.4 tarball directories and I forget Pg.pm is separate now (http://gborg.postgresql.org/project/pgperl/projdisplay.php). In any event, I have the current version which is 2.0.2 (12/17/2002) and I don't see any references to bytea in the documentation (the Pg.3 file). There is a method to "put" an arbitary number of bytes to the back end but I don't that is it. Perhaps I should fill out a feature request or maybe one of the developers (is anyone on this list?) could tell me how to handle bytea types properly (i.e. address the escaping concerns) in Pg.pm. Currently for the character types, I do the parsing myself to make sure data is clean going to the database but it would **really nice** if I could take data directly from its web input and submit it (well, not directly but NOT have to escape "bad" characters like apostrophes). There might be a way of doing this in the Pg.pm but it hasn't revealed itself to me yet :) Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > So then the "large object" function in the Pg.pm are not what I'm looking > for. > > I'm back to square one- How does one actually insert a file into a table > using > > Pg.pm or more importantly psql? I'm more concerned about doing this on > the > > system first since that is where I would be scripting the initial load of > all > > the images- something like that should not be done via a web client. > > See if you have a function like pg_escape_bytea and pg_unescape_bytea. > If you do, you can escape the data, and then just stick it in quotes in > your query. > > As far as selecting the data, I don't think Perl needs to unescape it, but > I could be wrong. > > Jon > > > > > > Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > > > > BLOBs are PostgreSQL wierdness from before PostgreSQL had TOAST (which > > > removed the 8kb row limit). See > > > > > > http://www.postgresql.org/docs/7.3/static/largeobjects.html > > > > > > For info on the old BLOB interface. > > > > > > Jon > > > > > > On Thu, 11 Sep 2003, Network Administrator wrote: > > > > > > > I thought "bytea" was PG's version of BLOBs. I don't see a "blob" type > in > > > the > > > > current docs. > > > > > > > > Also, I saw your example code as well. I use Perl as well but I use > the > > > native > > > > Pg.pm module. There doesn't seem to be a way to switch the input to > binary > > > data > > > > but there is support for what is called "large object" which is I > think > > > what I > > > > might want. If you or anyone else out there has worked with bytea's > with > > > > module, I appreciate some direction and any other wisdom (e.g. > pros/cons, > > > > catch22's, etc). Thanks! > > > > > > > > Learning everyday... > > > > > > > > > > > > > > > > Quoting Jonathan Bartlett <johnnyb@eskimo.com>: > > > > > > > > > > I'll try that out. I'll have to research that base64 encoding > part > > > because > > > > > I'll > > > > > > only every do text dumps. > > > > > > > > > > Oh yea, I forgot, bytea columns come out just fine in text dumps > (BLOBs > > > > > don't, however). > > > > > > > > > > Jon > > > > > > > > > > > > Keith- > > > > > > > > > > > > Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>: > > > > > > > > > > > > > --- Jonathan Bartlett <johnnyb@eskimo.com> wrote: > > > > > > > > I'm a big fan of bytea. In every case where I've > > > > > > > > done the filesystem > > > > > > > > method I wished I hadn't. > > > > > > > > > > > > > > For the education of me and maybe others too, why was > > > > > > > that? i.e. what problems did you run into, that bytea avoids? > > > > > > > > > > > > > > __________________________________ > > > > > > > Do you Yahoo!? > > > > > > > Yahoo! SiteBuilder - Free, easy-to-use web site design software > > > > > > > http://sitebuilder.yahoo.com > > > > > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > > > > > TIP 8: explain analyze is your friend > > > > > > > > > > > > > > > > > > > -- > > > > > > Keith C. Perry > > > > > > Director of Networks & Applications > > > > > > VCSN, Inc. > > > > > > http://vcsn.com > > > > > > > > > > > > ____________________________________ > > > > > > This email account is being host by: > > > > > > VCSN, Inc : http://vcsn.com > > > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > > -- > > > > Keith C. Perry > > > > Director of Networks & Applications > > > > VCSN, Inc. > > > > http://vcsn.com > > > > > > > > ____________________________________ > > > > This email account is being host by: > > > > VCSN, Inc : http://vcsn.com > > > > > > > > > > > > > -- > > Keith C. Perry > > Director of Networks & Applications > > VCSN, Inc. > > http://vcsn.com > > > > ____________________________________ > > This email account is being host by: > > VCSN, Inc : http://vcsn.com > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Network Administrator <netadmin@vcsn.com> writes:
> I thought "bytea" was PG's version of BLOBs.
Not really (or not yet). Usually when people talk about blobs, they are
thinking of a feature that lets them store a large amount of data in one
table entry, and that provides the ability to read and write that data
sequentially (ie, not all at once, but in conveniently-sized chunks).
Commonly this means that you open a "handle" or "cursor" referring to
one particular blob and then read or write it through that handle.
If we had such I/O handles for bytea (and maybe text too), then we'd
have full-fledged blobs. Right now, bytea can store a large amount of
data, but you have to supply it all in one chunk, and you can only read
out subsets by means of SUBSTR, which isn't especially convenient or
efficient.
What we do have are Postgres-style large objects, which are not quite
the same concept, mainly because they are objects that have existence
independently of any particular row that might reference them. This is
a good thing for a few applications, but most people just find that it
creates a garbage-collection problem ... they'd rather a blob went away
when they deleted the table row that uses it. PG-style large objects
also lack any access permission checking; anyone can read or write any
LO in the database.
Some of the people at Red Hat are hot to create handles/cursors for
bytea and friends, so maybe it will happen for 7.5. I'd be inclined
to think about obsoleting Postgres-style large objects someday, if we
had the more common kind of blob support.
regards, tom lane
Quoting Tom Lane <tgl@sss.pgh.pa.us>: > Network Administrator <netadmin@vcsn.com> writes: > > I thought "bytea" was PG's version of BLOBs. > > Not really (or not yet). Usually when people talk about blobs, they are > thinking of a feature that lets them store a large amount of data in one > table entry, and that provides the ability to read and write that data > sequentially (ie, not all at once, but in conveniently-sized chunks). > Commonly this means that you open a "handle" or "cursor" referring to > one particular blob and then read or write it through that handle. > > If we had such I/O handles for bytea (and maybe text too), then we'd > have full-fledged blobs. Right now, bytea can store a large amount of > data, but you have to supply it all in one chunk, and you can only read > out subsets by means of SUBSTR, which isn't especially convenient or > efficient. Ok, I see. Without the I/O facilites for bytea and text, you have to write a "loader" in another language that access the filesystem. With BLOBs, the facilites are there natively. I would think in the case of binary files reading subsets would not be necessary for serving images (say for ads spot on a web page). Isn't it more efficient to use bytea of text type for large objects and let the the interface layer handle the trottling to the data to/from the user? > What we do have are Postgres-style large objects, which are not quite > the same concept, mainly because they are objects that have existence > independently of any particular row that might reference them. This is > a good thing for a few applications, but most people just find that it > creates a garbage-collection problem ... they'd rather a blob went away > when they deleted the table row that uses it. PG-style large objects > also lack any access permission checking; anyone can read or write any > LO in the database. Ok. I couldn't use that because of the security issue you mention. > Some of the people at Red Hat are hot to create handles/cursors for > bytea and friends, so maybe it will happen for 7.5. I'd be inclined > to think about obsoleting Postgres-style large objects someday, if we > had the more common kind of blob support. > > regards, tom lane I understand the value of handles but aren't cursors used to break up the large result sets from queries? -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Network Administrator <netadmin@vcsn.com> writes:
> Quoting Tom Lane <tgl@sss.pgh.pa.us>:
>> Commonly this means that you open a "handle" or "cursor" referring to
>> one particular blob and then read or write it through that handle.
> I understand the value of handles but aren't cursors used to break up
> the large result sets from queries?
I was using them to mean the same thing, ie, an I/O handle (or open blob
descriptor if you like). You could consider this to be analogous to an
SQL cursor --- a cursor is like a handle for a query result. Sorry if
I confused you rather than enlightened you.
regards, tom lane
Quoting Tom Lane <tgl@sss.pgh.pa.us>: > Network Administrator <netadmin@vcsn.com> writes: > > Quoting Tom Lane <tgl@sss.pgh.pa.us>: > >> Commonly this means that you open a "handle" or "cursor" referring to > >> one particular blob and then read or write it through that handle. > > > I understand the value of handles but aren't cursors used to break up > > the large result sets from queries? > > I was using them to mean the same thing, ie, an I/O handle (or open blob > descriptor if you like). You could consider this to be analogous to an > SQL cursor --- a cursor is like a handle for a query result. Ok, now its crystal > Sorry if > I confused you rather than enlightened you. > > regards, tom lane Nothing to be sorry about, I appreciate everyones help, input, suggestions, etc (especially since, I wasn't even the one who started this thread). Pg is a great product and I've become more active in promoting & recomending it so I just want to make sure I know what the heck I'm talking about. -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
>> There is no need for manually storing files on filesystem, because
>> large objects are doing that for You. I am storing whole binary files
>> in blobs(synonym for large objects from some other platforms), and I do
>> not remember that I had a single problem with that. Do not forget that
>> libpq has great support for large objects, and you can store large
>> object without actually storing them on server filesystem, so You do
>> not need any file permissions on "upload directory" or something like
>> that.
>
> The pictures are prepared for web. Storing in files is faster from
> that side. That system is calling db every 30 mins...
[sNip]
Has anyone done any benchmarks on this to know what the performance
differences are for downloads (I'm not concerned about uploads since they're
far less frequent) from a web server such as Apache HTTPd accessing the file
system directly vs. a CGI script/program sending it dynamically through
Apache HTTPd?
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
Randolf Richardson, DevNet SysOp 29 wrote: >>>There is no need for manually storing files on filesystem, because >>>large objects are doing that for You. I am storing whole binary files >>>in blobs(synonym for large objects from some other platforms), and I do >>>not remember that I had a single problem with that. Do not forget that >>>libpq has great support for large objects, and you can store large >>>object without actually storing them on server filesystem, so You do >>>not need any file permissions on "upload directory" or something like >>>that. >> >> The pictures are prepared for web. Storing in files is faster from >>that side. That system is calling db every 30 mins... > > [sNip] > > Has anyone done any benchmarks on this to know what the performance > differences are for downloads (I'm not concerned about uploads since they're > far less frequent) from a web server such as Apache HTTPd accessing the file > system directly vs. a CGI script/program sending it dynamically through > Apache HTTPd? CGI will be slow. Of course. If you write fastCGI, that would be hell lot fast. For ultrafast static content delivery, you should use small web servers like boa. It outperforms apache2 by upto 3 times for statis delivery.. Shridhar
[sNip]
> CGI will be slow. Of course.
>
> If you write fastCGI, that would be hell lot fast. For ultrafast static
Well, MOD_PERL and DBI.pm are part of the equation. So is NetWare,
which is still the undisputed champion of file and print servers.
> content delivery, you should use small web servers like boa. It
> outperforms apache2 by upto 3 times for statis delivery..
Do you know if BOA has been ported to NetWare? If not, then it's
simply not an option for me as one of my requirements is cross-platform
support. Apache is extremely flexible, and so it has been difficult for me
to justify anything else, but I am certainly open to looking at other
options for handling some aspects of this project.
Apache 2 also has options to load entire directories of files into RAM
for faster access. Although I haven't tested this yet, the concept seems
very good and I will probably use it for all the long-term static content
in my future projects.
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
[apologies -- this amounts to a cross-post on subject "DB with bytea
types reload problem" -- I'm fairly desperate!]
Hi Jonathan
I saved a db with some bytea columns to text using
pg_dump -a -d <db> > out.sql
If I go
psql: \i out.sql
multiple errors result, such as:
psql:out.sql:505: invalid command \N
psql:out.sql:505: invalid command \
The sql dump file has lines starting something like this for the photo table:
1 500 375 h
\\377\\330\\377\\340\\000\\020JFIF\\000\\001\\001\\001\\000H\\000H\\000\\000\\377\\341\\023>Exif\\000\\000MM\\000*\\000\\000\\000\\010\\000\\012\\00....
Is there any way I can retrieve the image data?
Rory
On 10/09/03, Jonathan Bartlett (johnnyb@eskimo.com) wrote:
> > > What is the size limit of bytea, I thought it was 8K?
>
> No limit that I've found. Some are several meg.
>
> > > How do you dump your database when you have bytea, do you need to do a
> > > binary dump?
>
> Nope. pg_dump automagically escapes everything.
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
It's always worked for me just by typing from the command line: psql -U USERNAME DBNAME < backup.sql Jon On Tue, 16 Dec 2003, Rory Campbell-Lange wrote: > [apologies -- this amounts to a cross-post on subject "DB with bytea > types reload problem" -- I'm fairly desperate!] > > Hi Jonathan > > I saved a db with some bytea columns to text using > > pg_dump -a -d <db> > out.sql > > If I go > > psql: \i out.sql > > multiple errors result, such as: > > psql:out.sql:505: invalid command \N > psql:out.sql:505: invalid command \ > > The sql dump file has lines starting something like this for the photo table: > 1 500 375 h \\377\\330\\377\\340\\000\\020JFIF\\000\\001\\001\\001\\000H\\000H\\000\\000\\377\\341\\023>Exif\\000\\000MM\\000*\\000\\000\\000\\010\\000\\012\\00.... > > Is there any way I can retrieve the image data? > > Rory > > On 10/09/03, Jonathan Bartlett (johnnyb@eskimo.com) wrote: > > > > What is the size limit of bytea, I thought it was 8K? > > > > No limit that I've found. Some are several meg. > > > > > > How do you dump your database when you have bytea, do you need to do a > > > > binary dump? > > > > Nope. pg_dump automagically escapes everything. > -- > Rory Campbell-Lange > <rory@campbell-lange.net> > <www.campbell-lange.net> >
Rory Campbell-Lange <rory@campbell-lange.net> writes: > I saved a db with some bytea columns to text using > pg_dump -a -d <db> > out.sql You should not need a binary dump for bytea. However, in versions before 7.3.5 there is a potential for problems if the data goes through any kind of locale or encoding translation --- see http://archives.postgresql.org/pgsql-bugs/2003-11/msg00140.php I'm not sure if that's what happened to you, or if there's any easy way to reverse the conversion on your file if that is what happened, but it's an avenue to pursue. regards, tom lane