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.
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?