Hi,

are BLOBs deleted that are not referenced any more?

Imagine, I have a table with a column

  | text   | oid  |     4 |

when this row is deleted, will postgres throw the BLOB away?

---
   _  _
 _(_)(_)_  David Wetzel, Turbocat's Development,
(_) __ (_) Buchhorster Strasse, D-16567 Muehlenbeck/Berlin, FRG,
  _/  \_   Fax +49 33056 82835 NeXTmail dave@turbocat.de
 (______)  http://www.turbocat.de/
           DEVELOPMENT * CONSULTING * ADMINISTRATION

Fri,  3 Jul 98 11:28:08 +0200
David Wetzel <dave@turbocat.de>
are BLOBs deleted that are not referenced?
On Fri, 3 Jul 1998, David Wetzel wrote:

> Hi,
> 
> are BLOBs deleted that are not referenced any more?

No, they remain in the database, and become 'orphaned'. We are looking at
ways of removing this problem, which occurs mainly with JDBC & ODBC based
clients.

There is one method in src/contrib/lo which goes some way in solving this,
but there was also discussion about using vacuum to remove the orphaned
blobs.

> Imagine, I have a table with a column
> 
>   | text   | oid  |     4 |
> 
> when this row is deleted, will postgres throw the BLOB away?

The lo type (src/contrib/lo) handles row deletion (as well as updates) by
defining a trigger which deletes the associated blob as required.

However, DELETE TABLE doesn't fire a trigger, so if the table's contents
don't get deleted first, then the blobs are again orphaned.

-- 
Peter T Mount peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.retep.org.uk
************ Someday I may rebuild this signature completely ;-) ************
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk


Sun, 5 Jul 1998 11:06:50 +0100 (BST)
Peter T Mount <peter@retep.org.uk>
Re: [HACKERS] are BLOBs deleted that are not referenced?