Postgres Pro
Downloads
Home   >   mailing lists

Thread: Deleting BLOBs

Deleting BLOBs

From
Neanderthelle Jones
Date:
I'd like to delete all the image files from a table by issuing an
interactive psql command.  Given

CREATE TABLE image (
    name text,
    rast oid
);

with most tuples having images in rast, wanna do something like

=> select lo_unlink('select rast from image');

Can such a thing be done, and how?  I guess I've forgotten the syntax.

Or will an update that sets the oids to null or 0 work?

(pgsql 9.1.2)

Thanks.

Re: Deleting BLOBs

From
Andreas Kretschmer
Date:
If i where you i would try:

select lo_unlink(rest) from image

but i'm never used this function...





Neanderthelle Jones <elle@view.net.au> hat am 12. August 2012 um 12:16
geschrieben:
> I'd like to delete all the image files from a table by issuing an
> interactive psql command.  Given
>
> CREATE TABLE image (
>    name text,
>    rast oid
> );
>
> with most tuples having images in rast, wanna do something like
>
> => select lo_unlink('select rast from image');
>
> Can such a thing be done, and how?  I guess I've forgotten the syntax.
>
> Or will an update that sets the oids to null or 0 work?
>
> (pgsql 9.1.2)
>
> Thanks.
>
> -
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Deleting BLOBs

From
Andreas Kretschmer
Date:
>
> select lo_unlink(rest) from image
>
> but i'm never used this function...

i've ...

Re: Deleting BLOBs

From
Neanderthelle Jones
Date:
On Sun, 12 Aug 2012, Neanderthelle Jones wrote:

> I'd like to delete all the image files from a table by issuing an
> interactive psql command.  Given
>
> CREATE TABLE image (
>     name text,
>     rast oid
> );
>
> with most tuples having images in rast, wanna do something like
>
> => select lo_unlink('select rast from image');
>
> Can such a thing be done, and how?  I guess I've forgotten the syntax.
>
> Or will an update that sets the oids to null or 0 work?
>
> (pgsql 9.1.2)

One attempt.  Is the error mine or PostgreSQL's?

$ for i in $(psql -q -t -U elle -d my_db \
 -c "SELECT raster FROM images where raster > 0"); do
 echo $i
 psql -q -U elle -d my_db -c "SELECT lo_unlink($i)"
done

21234
ERROR:  large object 21234 does not exist
21235
ERROR:  large object 21235 does not exist
21236
ERROR:  large object 21236 does not exist
21237
ERROR:  large object 21237 does not exist
21238
ERROR:  large object 21238 does not exist
21239
ERROR:  large object 21239 does not exist
21240
ERROR:  large object 21240 does not exist
21241
ERROR:  large object 21241 does not exist
21242
ERROR:  large object 21242 does not exist
21243
ERROR:  large object 21243 does not exist

etc. etc.

Re: Deleting BLOBs

From
Neanderthelle Jones
Date:
On Mon, 13 Aug 2012, Neanderthelle Jones wrote:

>
> $ for i in $(psql -q -t -U elle -d my_db \
>  -c "SELECT raster FROM images where raster > 0"); do
>  echo $i
>  psql -q -U elle -d my_db -c "SELECT lo_unlink($i)"
> done

"BEGIN; SELECT lo_unlink($i); COMMIT" makes no difference.

Re: Deleting BLOBs

From
Tom Lane
Date:
Neanderthelle Jones <elle@view.net.au> writes:
> One attempt.  Is the error mine or PostgreSQL's?

> $ for i in $(psql -q -t -U elle -d my_db \
>  -c "SELECT raster FROM images where raster > 0"); do
>  echo $i
>  psql -q -U elle -d my_db -c "SELECT lo_unlink($i)"
> done

> 21234
> ERROR:  large object 21234 does not exist
> 21235
> ERROR:  large object 21235 does not exist
> [etc]

That script looks reasonable enough, if perhaps not too fast.  Are you
sure the table actually does reference live large objects?

            regards, tom lane

Re: Deleting BLOBs

From
Neanderthelle Jones
Date:
On Sun, 12 Aug 2012, Tom Lane wrote:
> Neanderthelle Jones <elle@view.net.au> writes:
> > One attempt.  Is the error mine or PostgreSQL's?
>
> > $ for i in $(psql -q -t -U elle -d my_db \
> >  -c "SELECT raster FROM images where raster > 0"); do
> >  echo $i
> >  psql -q -U elle -d my_db -c "SELECT lo_unlink($i)"
> > done
>
> > 21234
> > ERROR:  large object 21234 does not exist
> > 21235
> > ERROR:  large object 21235 does not exist
> > [etc]
>
> That script looks reasonable enough, if perhaps not too fast.  Are you
> sure the table actually does reference live large objects?

Thanks, Tom.  No, because I fumbled a bit, and didn't vacuum.  They
may have gone, but I don't understand why the oids get echoed in that
case.

What does "live" mean?

Elle

Re: Deleting BLOBs

From
Tom Lane
Date:
Neanderthelle Jones <elle@view.net.au> writes:
> On Sun, 12 Aug 2012, Tom Lane wrote:
>> That script looks reasonable enough, if perhaps not too fast.  Are you
>> sure the table actually does reference live large objects?

> What does "live" mean?

Well, my point is that the OIDs in the table are just numbers.  They
might reference large objects, or they might not ... and your results
suggest not.

One way to verify what large objects actually exist is

SELECT DISTINCT loid FROM pg_largeobject;

(Depending on your PG version, you might need to be superuser to do
that.)

            regards, tom lane