Received: from cr322781-a.ym1.on.wave.home.com (cr322781-a.ym1.on.wave.home.com [24.112.37.89]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eASMVrt81374 for ; Tue, 28 Nov 2000 17:31:53 -0500 (EST) (envelope-from svanegmond@bang.dhs.org) Received: from svanegmond by cr322781-a.ym1.on.wave.home.com with local (Exim 3.16 #1 (Debian)) id 140tJk-00086G-00; Tue, 28 Nov 2000 17:33:40 -0500 Date: Tue, 28 Nov 2000 17:33:40 -0500 To: Chris Cc: Pgsql-Php Subject: Re: Question re large objects Message-ID: <20001128173340.A31096@bang.dhs.org> Reply-To: svanegmond@home.com References: <00d101c0596c$0d339c00$0200000a@windows> <4.3.2.7.2.20001129085951.00b07d20@203.25.173.1> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <4.3.2.7.2.20001129085951.00b07d20@203.25.173.1>; from csmith@squiz.net on Wed, Nov 29, 2000 at 09:02:54AM +1100 From: Stephen van Egmond X-Archive-Number: 200011/33 X-Sequence-Number: 41 Chris (csmith@squiz.net) wrote: > I'm in the (slow) process of writing a tutorial for this sort of thing (ie > how to do it etc). > This has probably been discussed before, but I'm looking for some positives > & negatives for storing files in the database (I think you can use a switch > on dump which dumps EVERYTHING, but I could be wrong), and some positives & > negatives for outside the database in a seperate directory. > Can someone send me some pointers & experiences :) (It doesn't have to go > back to the list if no-one else is interested). Pros to using large objects: - makes your system scalable (i.e. many HTTP servers) - clean Cons: - not currently dumpable - user interface is a tiny bit tricky (possibly due to gaps in the php docs) - introduces performance bottlenecks if you have many files coming out of the RDBMS, as opposed to using flat files. pg_dump will not dump large objects under any circumstances. Read the manual. All of the above cons can be eliminated with some amount of work. You can avoid unpleasant surprises by building an abstraction on top of lowrite and loread, e.g. pg_associate('Product', 'product_id', $product_id, 'product_image', $image_filename); pg_find_association('Product', 'product_id', $product_id, 'product_image'); would be for this table: CREATE TABLE Product ( product_id (...) primary key, product_image oid ... ); You can implement associate() to store the file and association information somewhere in the filesystem, and make up a restore script that will pump this saved information back into the db.