Hi,

I am rather new to Postgresql and am having trouble with
some aspects of large objects.  I am coming from a MySQL
backgroun where a longblob could just be another column so
please excuse my ignorance.

I am creating objects using the DBI interface and inserting
data which works well.  I am however having trouble
deleting blob data from the db.
test=> \lo_list
    Large objects
  ID   | Description
-------+-------------
 89803 |
 90068 |
(2 rows)

test=> \lo_unlink 89803
ERROR:  pg_description: Permission denied.
test=>

Do I have to grant any user who needs blobs access to a
system table (pg_description)?  If so, how much damage can
they do?

Also, I have been reading about tforeign keys which looks
very neat.  Can these be extended to large objects.  For
example:
table files
filename varchar(120) not null,
data oid
delete from files where filename='badfile.txt';
Can I have the delete statement above knock out the
associated large object if one exists?

Thanks in advance,
Shane

-- 
Shane Wegner: shane@cm.nu
              http://www.cm.nu/~shane/
PGP:          1024D/FFE3035D
              A0ED DAC4 77EC D674 5487
              5B5C 4F89 9A4E FFE3 035D

Thu, 23 Aug 2001 17:19:14 -0700
Shane Wegner <shane@cm.nu>
Problems with large objects
Shane Wegner <shane@cm.nu> writes:
> test=> \lo_unlink 89803
> ERROR:  pg_description: Permission denied.

Hmm.  Maybe those client-side comment manipulations in psql aren't
such a hot idea.  I know I never tested them as non-superuser :-(

Shane, try that from a superuser Postgres userid.  Meanwhile,
it's back to the drawing board for us.

			regards, tom lane

Thu, 23 Aug 2001 22:57:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Permissions for large-object comments
On Thu, Aug 23, 2001 at 10:57:29PM -0400, Tom Lane wrote:
> Shane Wegner <shane@cm.nu> writes:
> > test=> \lo_unlink 89803
> > ERROR:  pg_description: Permission denied.
> 
> Hmm.  Maybe those client-side comment manipulations in psql aren't
> such a hot idea.  I know I never tested them as non-superuser :-(
> 
> Shane, try that from a superuser Postgres userid.  Meanwhile,
> it's back to the drawing board for us.

Yes an unlink works fine as the superuser.  I can't unlink
using the DBI interface either though which suggests it not
being psql's fault.

Shane

> 
> 			regards, tom lane

-- 
Shane Wegner: shane@cm.nu
              http://www.cm.nu/~shane/
PGP:          1024D/FFE3035D
              A0ED DAC4 77EC D674 5487
              5B5C 4F89 9A4E FFE3 035D

Thu, 23 Aug 2001 20:00:30 -0700
Shane Wegner <shane@cm.nu>
Re: Permissions for large-object comments
Tom Lane writes:

> Shane Wegner <shane@cm.nu> writes:
> > test=> \lo_unlink 89803
> > ERROR:  pg_description: Permission denied.
>
> Hmm.  Maybe those client-side comment manipulations in psql aren't
> such a hot idea.  I know I never tested them as non-superuser :-(

:-(

> Shane, try that from a superuser Postgres userid.  Meanwhile,
> it's back to the drawing board for us.

I'm not sure about the future of the large objects, so I'm less eager to
invent a new mechanism.  I'm open to ideas, however.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 25 Aug 2001 02:45:40 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Permissions for large-object comments
On Sat, Aug 25, 2001 at 02:45:40AM +0200, Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Shane Wegner <shane@cm.nu> writes:
> > > test=> \lo_unlink 89803
> > > ERROR:  pg_description: Permission denied.
> >
> > Hmm.  Maybe those client-side comment manipulations in psql aren't
> > such a hot idea.  I know I never tested them as non-superuser :-(
> 
> :-(
> 
> > Shane, try that from a superuser Postgres userid.  Meanwhile,
> > it's back to the drawing board for us.
> 
> I'm not sure about the future of the large objects, so I'm less eager to
> invent a new mechanism.  I'm open to ideas, however.

Well as I'm not a developer, this it out of my league. 
However, if the future of large objects is in question, is
there a better way I should be storing large chunks of
binary data in the database.  The text column doesn't seem
to support it.
Regards,
Shane

-- 
Shane Wegner: shane@cm.nu
              http://www.cm.nu/~shane/
PGP:          1024D/FFE3035D
              A0ED DAC4 77EC D674 5487
              5B5C 4F89 9A4E FFE3 035D

Fri, 24 Aug 2001 17:48:18 -0700
Shane Wegner <shane@cm.nu>
Re: Permissions for large-object comments