Re: MDB - Delete LOB

Hello, On 06/08/2003 04:48 AM, Davide Pj Caironi wrote:

Hi all, I'm working on a project involving Large Object inside a DB. I started using postgresql and PEAR:MDB as db abstraction layer (by the way, very good package!), because PEAR:DB (that I used in the past) doesn't support LOB. The LOB handling works well, at least the insert in db and retrive from db. I'm looking for a simlpe way to delete lob from db... Let's suppose to have table like this Table file_system

item_id       int
item_oid     oid
item_name    text

If I simply make a query like "DELETE from file_system where item_id = x", the row x disappear from the table file_system, but the large object (referred by oid) still remain in the database (as you can see using a \dl command inside psql shell). So, using MDB is possible to delete also the Large Object witout using direcly the php command pg_lounlink($oid)? (i.e., MDB has an "abstract" function for this?)

There is no good solution for this in PostgreSQL. The problem is that PostgreSQL does not reclaim no longer referenced objects. So, when you either delete a row with LOB or update the LOB column row, it may leave some objects behind. IMHO this is a PostgreSQL design flaw because there is no use for unreclaimed objects. It is not quite viable to make MDB delete the object because it would have to know it has to do that. The only want to know it is to parse the DELETE or UPDATE query and realize that there are LOB about to be deleted. This is quite tricky and you would have to deal with all the possibilities that may go wrong. Personally I would not use LOB if I had to use PosgreSQL or other databases that have an inferior support to LOB, like MySQL and MS-SQL just to mention the more popular. All these have their own set of problems with LOBs. For a decent LOB support use a really high-end database like Oracle or maybe Interbase. A more portable and probably faster solution is to store the LOB data in the local file system and then just store the file names in the database. -- Regards, Manuel Lemos Free ready to use OOP components written in PHP http://www.phpclasses.org/