On Wed, 16 Feb 2000 [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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." ************
