Thread: Picture with Postgres and Delphi

Picture with Postgres and Delphi

From
"Edwin Quijada"
Date:
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

Re: Picture with Postgres and Delphi

From
"listy.mailowe"
Date:
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)




Re: Picture with Postgres and Delphi

From
Daniel Schuchardt
Date:
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)


Re: Picture with Postgres and Delphi

From
"Darko Prenosil"
Date:
----- 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 !


Re: Picture with Postgres and Delphi

From
"Darko Prenosil"
Date:
----- 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 !


Re: Picture with Postgres and Delphi

From
"scott.marlowe"
Date:
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.


Re: Picture with Postgres and Delphi

From
Jonathan Bartlett
Date:
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)
>


Re: Picture with Postgres and Delphi

From
Jeff Eckermann
Date:
--- 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

Re: Picture with Postgres and Delphi

From
Jonathan Bartlett
Date:
> 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
>


Re: Picture with Postgres and Delphi

From
Guy Fraser
Date:
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.





Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
"Derrick Betts"
Date:
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


Re: Picture with Postgres and Delphi

From
Dennis Gearon
Date:
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.


Re: Picture with Postgres and Delphi

From
Jonathan Bartlett
Date:
> 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
>


Re: Picture with Postgres and Delphi

From
Jonathan Bartlett
Date:
> > 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
>


Re: Picture with Postgres and Delphi

From
"scott.marlowe"
Date:
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
>


Re: Picture with Postgres and Delphi

From
"scott.marlowe"
Date:
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
> >
>
>


Re: Picture with Postgres and Delphi

From
Andrew Ayers
Date:
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. 

Re: Picture with Postgres and Delphi

From
Doug McNaught
Date:
"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

Re: Picture with Postgres and Delphi

From
Brian Hirt
Date:
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


Re: Picture with Postgres and Delphi

From
Richard Huxton
Date:
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

Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
Jeff Eckermann
Date:
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

Re: Picture with Postgres and Delphi

From
Michał Zaborowski
Date:
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)


Re: Picture with Postgres and Delphi

From
Michał Zaborowski
Date:
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)



Re: Picture with Postgres and Delphi

From
Guy Fraser
Date:
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
>
>


Re: Picture with Postgres and Delphi

From
Tom Lane
Date:
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

Re: Picture with Postgres and Delphi

From
Jonathan Bartlett
Date:
> 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
>


Re: Picture with Postgres and Delphi

From
Darko Prenosil
Date:
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 !

Re: Picture with Postgres and Delphi

From
Date:
>> 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




Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
Jonathan Bartlett
Date:
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
>


Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
Jonathan Bartlett
Date:
> 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
>


Re: Picture with Postgres and Delphi

From
Alvaro Herrera
Date:
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)

Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
Tom Lane
Date:
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

Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
Tom Lane
Date:
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

Re: Picture with Postgres and Delphi

From
Network Administrator
Date:
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

Re: Picture with Postgres and Delphi

From
"Randolf Richardson, DevNet SysOp 29"
Date:
>> 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.


Re: Picture with Postgres and Delphi

From
Shridhar Daithankar
Date:
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


Re: Picture with Postgres and Delphi

From
"Randolf Richardson, DevNet SysOp 29"
Date:
[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.


Reload bytea [was Re: Picture with Postgres and Delphi]

From
Rory Campbell-Lange
Date:
[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>

Re: Reload bytea [was Re: Picture with Postgres and Delphi]

From
Jonathan Bartlett
Date:
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>
>


Re: Reload bytea [was Re: Picture with Postgres and Delphi]

From
Tom Lane
Date:
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