Обсуждение: Postgres, Perl, and Large Objects

Hi-

As practice before I begin a larger project, I'm trying to create a
simple photo album.  I want to be able to save and retrieve images in
a postgres database.  I can insert image data as large objects (data
type oid) using the psql utility and I can write perl CGI scripts to
retrieve the image data and display it in a web browser.  The thing
that is giving me trouble is writing a perl CGI to load the image
data into the database.

My initial attempt was to use a regular insert statement, like the
one I used with psql.  When I do that I get messages saying:

You must have Postgres superupser privilege to use server-side
lo_import().  Anyone can use the client-side lo_import provided by
libpq.

and

please use DBI functions for transaction handling at
/usr/lib/perl5/site_perl/5.6.0/i386-linux/DBD/Pg.pm line 114.

After sifting through many FAQs, man pages, "Programming the Perl
DBI", and the PostgreSQL Programmer's Guide, it seems that my problem
may be that I need to put the insert statement into a transaction
block.  I am not sure I am doing that correctly.

The code I've used to do this includes the following lines:
$dbname = "photo_album";
$username = "nobody";
$dbh = DBI->connect("dbi:Pg:dbname=$dbname", $username, undef,
{AutoCommit => 0,RaiseError => 1,}) || die "$!";

eval {
   $querytext = "INSERT INTO image (image_trip_id, image_scene,
image_thumb_mime_type,
                                    image_thumb_length,
image_thumb_image, image_original_mime_type,
                                    image_original_length,
image_original_image)
                            VALUES (3, 1, 'image/jpeg',
                                    2368,
lo_import('/home/stacey/training/images/buffalo.tmb'), 'image/jpeg',
                                    29822,
lo_import('/home/stacey/training/images/buffalo.JPG')
                  ); ";

   $sth = $dbh->prepare( $querytext )
          or die "$mod_name $cur_time Can't prepare SQL statement for
insert to image: $DBI::errstr\n";
   $num_returned_rows = $sth->execute()
          or die "$mod_name $cur_time Can't execute SQL statement for
insert to image: $DBI::errstr\n";
   print "<BR>num returned rows from insert:
$num_returned_rows<BR>\n";

   $dbh->commit;   # commit the changes if we get this far
};
if ($@) {
    warn "Transaction aborted because $@";
    $dbh->rollback; # undo the incomplete changes
# add other application on-error-clean-up code here
}


This didn't work.
I also tried adding the following in the eval block, before the
insert statement.  It didn't work either.
   $querytext = "BEGIN WORK";
   $sth = $dbh->prepare( $querytext )
          or die "$mod_name $cur_time Can't prepare SQL statement
BEGIN WORK: $DBI::errstr\n";
   $num_returned_rows = $sth->execute()
          or die "$mod_name $cur_time Can't execute SQL statement
BEGIN WORK: $DBI::errstr\n";

Trying this line of code in the eval block, before the insert
statement also didn't work.
$dbh->do("BEGIN WORK");

Do I need a transaction block?  If so, what is the correct syntax for
establishing it?  Do I need to do something else?  If so, what?  It
seems like it should be so simple...

I'm using Postgres 7.0.2, Perl 5.6.0, and Linux Mandrake 7.1.

Thanks in advance for any help.

Stacey



__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/

S Vahey <savahey@yahoo.com> writes:
> You must have Postgres superupser privilege to use server-side
> lo_import().  Anyone can use the client-side lo_import provided by
> libpq.

That means exactly what it says: you can't use the lo_import (or
lo_export) SQL functions unless you are superuser.  This is a security
issue, not a transaction issue: those functions let you read or scribble
on any file that's accessible to the Postgres userid.

I don't know whether the DBI interface provides access to client-side
large-object functions; you might have to use the Pg "perl5" interface
library instead.  Or run your application connected as the database
superuser, but that seems like a risky way to fly.

            regards, tom lane