Re: [GENERAL] Deleting large objects sans index

On Wed, 16 Feb 2000 mathprof(at)bigfoot(dot)com wrote:

> What's the quickest way to delete all PostgreSQL large objects? Is there a
> system table that lists the oids of existing large objects? I expect the
> command is something like:
>
> => select lo_unlink(SOME_ATTRIBUTE) from SOME_SYSTEM_TABLE
>
> but I don't which system table and which attribute! I'm tempted to do:
>
> % /bin/rm -f /usr/local/pgsql/base/DBNAME/xin[xv]*
>
> but I suspect that's a bad idea. In desperation, I might have to do:
>
> % destroydb DBNAME
>
> but I want to save that as a last resort.

i dont know if this is 'safe', but:

select lo_unlink( int4( substr(relname,5) ) )
from pg_class
where relname like 'xinv%';

select substr(relname,5) from pg_class where relname like 'xinv%';
produces:

ircbot=> select relname,substr(relname,5) from pg_class where relname like
'xinv%';
relname | substr
-----------+-------
xinv6576385|6576385
xinv6576402|6576402
xinv6576449|6576449
xinv6576479|6576479
xinv6605697|6605697
xinv6690177|6690177
xinv6690206|6690206
xinv6690253|6690253
xinv6690268|6690268
xinv6788971|6788971
(10 rows)

although the oid ( by itself ) should be present in one of the pg_*
tables...

> More general question: I ended up in this quandry because of a goof-- I
> created a table with an oid field and then created several large objects
> "linked" to the table (of course, the large objects weren't part of the
> table-- the table just contained the oids of the large objects--
> nonetheless, I thought of the large objects as 'belonging' to the table).
> Then I foolishly did a "delete from table;" without deleting the large
> objects first-- this left me with a whole bunch of large objects to which
> I had no reference. Is there a general way to a) avoid this sort of thing
> (triggers??) and/or b) clean up the mess after something like this
> happens?

there's a library called 'lo' in $PGSQL_SRC_ROOT/contrib/lo/ that handles
automagic deletes of LO's if the corresponding oid is removed. the
release in 6.5.2 had a bug where it didnt check for NULL oids, though. i
havent taken the time to patch it and mail it back to the dev team.

---
Howie <caffeine(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"You do not have the right to free health care. That would be nice, but
from the looks of public housing, we're just not interested in health care."