Importing blob

Patrick Nelson wrote:
----------------->>>>
Here is my table:

CREATE TABLE setimg (id serial, setid int8, image OID);

Here is my insert:

INSERT INTO setimg(setid, image) VALUES ('32334', lo_import('32334.jpg'));

And the error:

lo_import: can't open unix file "32334.jpg": no such file or directory

I have tried everything that I can think of to insert this file and every
time I get the same error. I have set the full path to the file. I have
change priv to 666. Confirmed proper ownership <user>:<user>. And still it
does not see it. What am I doing wrong?
----------------->>>>

Oopss!

RH73 with psql 7.2.1 as the client
RH72 running 7.1.3 as the server

If I add the entire path in the lo_import function then I get the following
error:

lo_import: can't open unix file "32334.jpg": Permission denied

The file has 666 as it's permissions and I've tried all sorts of ownership
configs. Still denied. I just must be doing something wrong here.

Maybe it isn't searching on the path you think it is. Try using a full path to
the file.

Regards,
Jeff Davis

lo_import: can't open unix file "32334.jpg": no such file or directory

I think your user has to be a postgres superuser to be allowed to import large objects.

Patrick Nelson wrote:
----------------->>>>
Here is my table:

CREATE TABLE setimg (id serial, setid int8, image OID);

Here is my insert:

INSERT INTO setimg(setid, image) VALUES ('32334', lo_import('32334.jpg'));

And the error:

lo_import: can't open unix file "32334.jpg": no such file or directory

I have tried everything that I can think of to insert this file and every
time I get the same error. I have set the full path to the file. I have
change priv to 666. Confirmed proper ownership <user>:<user>. And still it
does not see it. What am I doing wrong?
----------------->>>>

Oopss!

RH73 with psql 7.2.1 as the client
RH72 running 7.1.3 as the server

If I add the entire path in the lo_import function then I get the following
error:

lo_import: can't open unix file "32334.jpg": Permission denied

The file has 666 as it's permissions and I've tried all sorts of ownership
configs. Still denied. I just must be doing something wrong here.
----------------->>>>

Wow thanks all the replies got me thinking...

So I went to the server and put the file there in / dir and then went to my
client and tried the insert with /32334.jpg and darn it did work. Then I
moved it to a dir (/home/sql) and played around a bit...

So it is actually the server that imports the file so it has to be on the
server. Also, the directory (and subdirs) all have to have rx ability to
the PostgreSQL daemon user. The file needs min r for the PostgreSQL daemon
user.

This was a bit confusing.

Wow thanks all the replies got me thinking...

So I went to the server and put the file there in / dir and then went to my
client and tried the insert with /32334.jpg and darn it did work. Then I
moved it to a dir (/home/sql) and played around a bit...

So it is actually the server that imports the file so it has to be on the
server. Also, the directory (and subdirs) all have to have rx ability to
the PostgreSQL daemon user. The file needs min r for the PostgreSQL daemon
user.

This is a bit confusing, wonder why a client wouldn't be able to insert a
blob?

Patrick Nelson <pnelson@neatech.com> writes:

So it is actually the server that imports the file so it has to be on the
server. Also, the directory (and subdirs) all have to have rx ability to
the PostgreSQL daemon user. The file needs min r for the PostgreSQL daemon
user.

Yup.

This is a bit confusing, wonder why a client wouldn't be able to insert a
blob?

You can, but you have to call the lo_import function in the *client*
library, not the server-side function. See the client library docs.
Yes, it's slightly confusing until you figure it out.

-Doug