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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: Picture with Postgres and Delphi
Hi,
Edwin Quijada wrote:
[color=blue]
> 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??
>[/color]
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)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| | | | re: Picture with Postgres and Delphi
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:[color=blue]
> 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)[/color]
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
----- 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
[color=blue]
> Hi,
>
> Edwin Quijada wrote:
>[color=green]
> > 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[/color][/color]
aprox.[color=blue][color=green]
> > What is the way more optimus?
> > That 's table will have 500000 records around. Somebody said the best[/color][/color]
way to[color=blue][color=green]
> > do that was encoder the picture to field bytea but I dont know about[/color][/color]
this.[color=blue][color=green]
> > 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??
> >[/color]
> 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.
>[/color]
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 !
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
----- 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
[color=blue]
> 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[/color]
to[color=blue]
> 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??[/color]
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 !
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
On Tue, 9 Sep 2003, Darko Prenosil wrote:
[color=blue]
>
> ----- 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
>
>[color=green]
> > 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[/color]
> to[color=green]
> > 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??[/color]
>
> 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.[/color]
for portability, I've always base64 encoded and stored them as straight
text. That works well too.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: Picture with Postgres and Delphi
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:
[color=blue]
> 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)
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster | | | | re: Picture with Postgres and Delphi
--- Jonathan Bartlett <johnnyb@eskimo.com> wrote:[color=blue]
> I'm a big fan of bytea. In every case where I've
> done the filesystem
> method I wished I hadn't.[/color]
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 | | | | re: Picture with Postgres and Delphi
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:[color=blue]
> 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)[/color]
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
----- 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
[color=blue]
> Hi,
>
> Edwin Quijada wrote:
>[color=green]
> > 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[/color][/color]
aprox.[color=blue][color=green]
> > What is the way more optimus?
> > That 's table will have 500000 records around. Somebody said the best[/color][/color]
way to[color=blue][color=green]
> > do that was encoder the picture to field bytea but I dont know about[/color][/color]
this.[color=blue][color=green]
> > 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??
> >[/color]
> 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.
>[/color]
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 !
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
----- 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
[color=blue]
> 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[/color]
to[color=blue]
> 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??[/color]
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 !
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
> For the education of me and maybe others too, why was[color=blue]
> that? i.e. what problems did you run into, that bytea avoids?
>[/color]
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
[color=blue]
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: Picture with Postgres and Delphi
On Tue, 9 Sep 2003, Darko Prenosil wrote:
[color=blue]
>
> ----- 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
>
>[color=green]
> > 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[/color]
> to[color=green]
> > 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??[/color]
>
> 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.[/color]
for portability, I've always base64 encoded and stored them as straight
text. That works well too.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: Picture with Postgres and Delphi
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:
[color=blue]
> 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)
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster | | | | re: Picture with Postgres and Delphi
--- Jonathan Bartlett <johnnyb@eskimo.com> wrote:[color=blue]
> I'm a big fan of bytea. In every case where I've
> done the filesystem
> method I wished I hadn't.[/color]
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 | | | | re: Picture with Postgres and Delphi
> For the education of me and maybe others too, why was[color=blue]
> that? i.e. what problems did you run into, that bytea avoids?
>[/color]
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
[color=blue]
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: 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:
[color=blue][color=green]
>>For the education of me and maybe others too, why was
>>that? i.e. what problems did you run into, that bytea avoids?
>>
>>
>>[/color]
>
>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
>
>
>
>[color=green]
>>__________________________________
>>Do you Yahoo!?
>>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>> http://sitebuilder.yahoo.com
>>
>>
>>[/color]
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>[/color]
--
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 | | | | re: Picture with Postgres and Delphi
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
[color=blue]
> 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:
>[color=green][color=darkred]
> >>For the education of me and maybe others too, why was
> >>that? i.e. what problems did you run into, that bytea avoids?
> >>
> >>
> >>[/color]
> >
> >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
> >
> >
> >
> >[color=darkred]
> >>__________________________________
> >>Do you Yahoo!?
> >>Yahoo! SiteBuilder - Free, easy-to-use web site design software
> >> http://sitebuilder.yahoo.com
> >>
> >>
> >>[/color]
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >
> >[/color]
>
> --
> 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[/color]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend | | | | re: Picture with Postgres and Delphi
> > What is the size limit of bytea, I thought it was 8K?
No limit that I've found. Some are several meg.
[color=blue][color=green]
> > How do you dump your database when you have bytea, do you need to do a
> > binary dump?[/color][/color]
Nope. pg_dump automagically escapes everything.
[color=blue][color=green]
> > What are you using to insert the binary data?[/color][/color]
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
[color=blue][color=green]
> >
> > Jonathan Bartlett wrote:
> >[color=darkred]
> > >>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
> > >
> > >
> > >
> > >[/color]
> >
> > --
> > 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[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: 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:
[color=blue][color=green]
>>For the education of me and maybe others too, why was
>>that? i.e. what problems did you run into, that bytea avoids?
>>
>>
>>[/color]
>
>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
>
>
>
>[color=green]
>>__________________________________
>>Do you Yahoo!?
>>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>> http://sitebuilder.yahoo.com
>>
>>
>>[/color]
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>[/color]
--
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 | | | | re: Picture with Postgres and Delphi
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:
[color=blue]
> 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
>
>[color=green]
> > 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:
> >[color=darkred]
> > >>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
> > >
> > >
> > >
> > >[/color]
> >
> > --
> > 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[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
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
[color=blue]
> 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:
>[color=green][color=darkred]
> >>For the education of me and maybe others too, why was
> >>that? i.e. what problems did you run into, that bytea avoids?
> >>
> >>
> >>[/color]
> >
> >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
> >
> >
> >
> >[color=darkred]
> >>__________________________________
> >>Do you Yahoo!?
> >>Yahoo! SiteBuilder - Free, easy-to-use web site design software
> >> http://sitebuilder.yahoo.com
> >>
> >>
> >>[/color]
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >
> >[/color]
>
> --
> 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[/color]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend | | | | re: Picture with Postgres and Delphi
> > What is the size limit of bytea, I thought it was 8K?
No limit that I've found. Some are several meg.
[color=blue][color=green]
> > How do you dump your database when you have bytea, do you need to do a
> > binary dump?[/color][/color]
Nope. pg_dump automagically escapes everything.
[color=blue][color=green]
> > What are you using to insert the binary data?[/color][/color]
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
[color=blue][color=green]
> >
> > Jonathan Bartlett wrote:
> >[color=darkred]
> > >>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
> > >
> > >
> > >
> > >[/color]
> >
> > --
> > 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[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: Picture with Postgres and Delphi
"Derrick Betts" <Derrick@grifflink.com> writes:
[color=blue]
> Is the size limit 8K for 'text' field types as well?[/color]
There is no size limit (OK, a very very large one) on either "text" or
"bytea" in modern versions of Postgres.
-Doug
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
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:
[color=blue]
> 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
>
>[color=green]
> > 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:
> >[color=darkred]
> > >>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
> > >
> > >
> > >
> > >[/color]
> >
> > --
> > 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[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings | | | | re: Picture with Postgres and Delphi
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:
[color=blue][color=green]
>> For the education of me and maybe others too, why was
>> that? i.e. what problems did you run into, that bytea avoids?
>>[/color]
>
> 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
>
>[color=green]
>> __________________________________
>> Do you Yahoo!?
>> Yahoo! SiteBuilder - Free, easy-to-use web site design software
>> http://sitebuilder.yahoo.com
>>[/color]
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html[/color]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster | | | | re: Picture with Postgres and Delphi
On Wednesday 10 September 2003 17:17, Derrick Betts wrote:[color=blue]
> Is the size limit 8K for 'text' field types as well?
>[color=green]
> > What is the size limit of bytea, I thought it was 8K?[/color][/color]
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: Picture with Postgres and Delphi
"Derrick Betts" <Derrick@grifflink.com> writes:
[color=blue]
> Is the size limit 8K for 'text' field types as well?[/color]
There is no size limit (OK, a very very large one) on either "text" or
"bytea" in modern versions of Postgres.
-Doug
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
Darko Prenosil wrote:[color=blue]
>
> 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.
>[/color]
The pictures are prepared for web. Storing in files is faster from
that side. That system is calling db every 30 mins...
[color=blue]
> If You are using Delphi, there is great project called "Zeos objects", and
> if I remember correctly it has support for large objects.
>[/color]
Zeos are useing a lot of memory...
Regards,
Micha³ Zaborowski (TeXXaS)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
| | | | re: Picture with Postgres and Delphi
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:
[color=blue][color=green]
>> For the education of me and maybe others too, why was
>> that? i.e. what problems did you run into, that bytea avoids?
>>[/color]
>
> 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
>
>[color=green]
>> __________________________________
>> Do you Yahoo!?
>> Yahoo! SiteBuilder - Free, easy-to-use web site design software
>> http://sitebuilder.yahoo.com
>>[/color]
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html[/color]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster | | | | re: Picture with Postgres and Delphi
On Wednesday 10 September 2003 17:17, Derrick Betts wrote:[color=blue]
> Is the size limit 8K for 'text' field types as well?
>[color=green]
> > What is the size limit of bytea, I thought it was 8K?[/color][/color]
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) | | | | re: Picture with Postgres and Delphi
Thanks that is extremely helpfull.
Guy
Jonathan Bartlett wrote:
[color=blue][color=green][color=darkred]
>>>What is the size limit of bytea, I thought it was 8K?
>>>
>>>[/color][/color]
>
>No limit that I've found. Some are several meg.
>
>
>[color=green][color=darkred]
>>>How do you dump your database when you have bytea, do you need to do a
>>>binary dump?
>>>
>>>[/color][/color]
>
>Nope. pg_dump automagically escapes everything.
>
>
>[color=green][color=darkred]
>>>What are you using to insert the binary data?
>>>
>>>[/color][/color]
>
>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
>
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
Darko Prenosil wrote:[color=blue]
>
> 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.
>[/color]
The pictures are prepared for web. Storing in files is faster from
that side. That system is calling db every 30 mins...
[color=blue]
> If You are using Delphi, there is great project called "Zeos objects", and
> if I remember correctly it has support for large objects.
>[/color]
Zeos are useing a lot of memory...
Regards,
Micha³ Zaborowski (TeXXaS)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
| | | | re: Picture with Postgres and Delphi
Richard Huxton <dev@archonet.com> writes:[color=blue]
> On Wednesday 10 September 2003 17:17, Derrick Betts wrote:[color=green][color=darkred]
>>> What is the size limit of bytea, I thought it was 8K?[/color][/color][/color]
[color=blue]
> Not for some time now - the TOAST system (Tom Lane's work IIRC)[/color]
Not my work, Jan Wieck's. But yes, the 8K limit is ancient history.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: Picture with Postgres and Delphi
> What is the size limit of bytea, I thought it was 8K?
No limit
[color=blue]
> How do you dump your database when you have bytea, do you need to do a
> binary dump?[/color]
Just dump it normally.
[color=blue]
> What are you using to insert the binary data?[/color]
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
[color=blue]
>
> Thank you in advance.
>
> Guy
>
> Jonathan Bartlett wrote:
>[color=green][color=darkred]
> >>For the education of me and maybe others too, why was
> >>that? i.e. what problems did you run into, that bytea avoids?
> >>
> >>
> >>[/color]
> >
> >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
> >
> >
> >
> >[color=darkred]
> >>__________________________________
> >>Do you Yahoo!?
> >>Yahoo! SiteBuilder - Free, easy-to-use web site design software
> >> http://sitebuilder.yahoo.com
> >>
> >>
> >>[/color]
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >
> >[/color]
>
> --
> 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
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org | | | | re: Picture with Postgres and Delphi
Thanks that is extremely helpfull.
Guy
Jonathan Bartlett wrote:
[color=blue][color=green][color=darkred]
>>>What is the size limit of bytea, I thought it was 8K?
>>>
>>>[/color][/color]
>
>No limit that I've found. Some are several meg.
>
>
>[color=green][color=darkred]
>>>How do you dump your database when you have bytea, do you need to do a
>>>binary dump?
>>>
>>>[/color][/color]
>
>Nope. pg_dump automagically escapes everything.
>
>
>[color=green][color=darkred]
>>>What are you using to insert the binary data?
>>>
>>>[/color][/color]
>
>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
>
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
Richard Huxton <dev@archonet.com> writes:[color=blue]
> On Wednesday 10 September 2003 17:17, Derrick Betts wrote:[color=green][color=darkred]
>>> What is the size limit of bytea, I thought it was 8K?[/color][/color][/color]
[color=blue]
> Not for some time now - the TOAST system (Tom Lane's work IIRC)[/color]
Not my work, Jan Wieck's. But yes, the 8K limit is ancient history.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html | | | | re: Picture with Postgres and Delphi
On Wednesday 10 September 2003 21:36, Micha³ Zaborowski wrote:[color=blue]
> Darko Prenosil wrote:[color=green]
> > 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.[/color]
>
> The pictures are prepared for web. Storing in files is faster from
> that side. That system is calling db every 30 mins...
>[color=green]
> > If You are using Delphi, there is great project called "Zeos objects",
> > and if I remember correctly it has support for large objects.[/color]
>
> Zeos are useing a lot of memory...
>
> Regards,
> Micha³ Zaborowski (TeXXaS)
>[/color]
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 !
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
| | | | re: Picture with Postgres and Delphi
> What is the size limit of bytea, I thought it was 8K?
No limit
[color=blue]
> How do you dump your database when you have bytea, do you need to do a
> binary dump?[/color]
Just dump it normally.
[color=blue]
> What are you using to insert the binary data?[/color]
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
[color=blue]
>
> Thank you in advance.
>
> Guy
>
> Jonathan Bartlett wrote:
>[color=green][color=darkred]
> >>For the education of me and maybe others too, why was
> >>that? i.e. what problems did you run into, that bytea avoids?
> >>
> >>
> >>[/color]
> >
> >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
> >
> >
> >
> >[color=darkred]
> >>__________________________________
> >>Do you Yahoo!?
> >>Yahoo! SiteBuilder - Free, easy-to-use web site design software
> >> http://sitebuilder.yahoo.com
> >>
> >>
> >>[/color]
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >
> >[/color]
>
> --
> 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
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org | | | | re: Picture with Postgres and Delphi
>> If You are using Delphi, there is great project called "Zeos[color=blue][color=green]
>> objects", and if I remember correctly it has support for large
>> objects.
>>[/color]
> Zeos are useing a lot of memory...[/color]
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
On Wednesday 10 September 2003 21:36, Micha³ Zaborowski wrote:[color=blue]
> Darko Prenosil wrote:[color=green]
> > 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.[/color]
>
> The pictures are prepared for web. Storing in files is faster from
> that side. That system is calling db every 30 mins...
>[color=green]
> > If You are using Delphi, there is great project called "Zeos objects",
> > and if I remember correctly it has support for large objects.[/color]
>
> Zeos are useing a lot of memory...
>
> Regards,
> Micha³ Zaborowski (TeXXaS)
>[/color]
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 !
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
| | | | re: Picture with Postgres and Delphi
>> If You are using Delphi, there is great project called "Zeos[color=blue][color=green]
>> objects", and if I remember correctly it has support for large
>> objects.
>>[/color]
> Zeos are useing a lot of memory...[/color]
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
>> There is no need for manually storing files on filesystem, because[color=blue][color=green]
>> 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.[/color]
>
> The pictures are prepared for web. Storing in files is faster from
> that side. That system is calling db every 30 mins...[/color]
[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. | | | | re: Picture with Postgres and Delphi
Randolf Richardson, DevNet SysOp 29 wrote:
[color=blue][color=green][color=darkred]
>>>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.[/color]
>>
>> The pictures are prepared for web. Storing in files is faster from
>>that side. That system is calling db every 30 mins...[/color]
>
> [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?[/color]
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
Randolf Richardson, DevNet SysOp 29 wrote:
[color=blue][color=green][color=darkred]
>>>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.[/color]
>>
>> The pictures are prepared for web. Storing in files is faster from
>>that side. That system is calling db every 30 mins...[/color]
>
> [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?[/color]
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org | | | | re: Picture with Postgres and Delphi
[sNip][color=blue]
> CGI will be slow. Of course.
>
> If you write fastCGI, that would be hell lot fast. For ultrafast static[/color]
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.
[color=blue]
> content delivery, you should use small web servers like boa. It
> outperforms apache2 by upto 3 times for statis delivery..[/color]
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. | | | | re: Picture with Postgres and Delphi
[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\\00 0\\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:[color=blue][color=green][color=darkred]
> > > What is the size limit of bytea, I thought it was 8K?[/color][/color]
>
> No limit that I've found. Some are several meg.
>[color=green][color=darkred]
> > > How do you dump your database when you have bytea, do you need to do a
> > > binary dump?[/color][/color]
>
> Nope. pg_dump automagically escapes everything.[/color]
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
| | | | re: Picture with Postgres and Delphi
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:
[color=blue]
> [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\\00 0\\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:[color=green][color=darkred]
> > > > What is the size limit of bytea, I thought it was 8K?[/color]
> >
> > No limit that I've found. Some are several meg.
> >[color=darkred]
> > > > How do you dump your database when you have bytea, do you need to do a
> > > > binary dump?[/color]
> >
> > Nope. pg_dump automagically escapes everything.[/color]
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
| | | | re: Picture with Postgres and Delphi
Rory Campbell-Lange <rory@campbell-lange.net> writes:[color=blue]
> I saved a db with some bytea columns to text using
> pg_dump -a -d <db> > out.sql[/color]
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...1/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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match |  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
