Received: from frithjof.reiner (a13.dialin.tuwien.ac.at [192.35.240.23]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0PH29q73363 for ; Thu, 25 Jan 2001 12:02:10 -0500 (EST) (envelope-from areiner@tph.tuwien.ac.at) Received: (qmail 275 invoked by uid 500); 24 Jan 2001 19:36:39 -0000 From: "Albert REINER" Date: Wed, 24 Jan 2001 20:36:39 +0100 To: pgsql-sql@postgresql.org Subject: Re: unreferenced primary keys: garbage collection Message-ID: <20010124203639.A203@frithjof> Mail-Followup-To: pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: ; from mfork@toledolink.com on Wed, Jan 24, 2001 at 12:26:58AM -0500 X-Archive-Number: 200101/356 X-Sequence-Number: 1029 > On Tue, 23 Jan 2001, Forest Wilkinson wrote: > > > Jan, > > > > Thanks for the reply, but your solution is rather unattractive to me. It > > requires that, any time a reference to an address id is changed, five > > tables be searched for the address id. This will create unwanted overhead If - and I think this is the case for you - it is no problem for you to have some superfluous adresses in your tables, but you only want to avoid that those adresses remain there for a long time, you could simply run the function Jan sent from a cron job. That seems to be likely to be more efficient not only than the triggers but also to the ON DELETE RESTRICT solution, I guess. Trivial, but I HTH - Albert. > > Forest Wilkinson wrote: > > >> > I have a database in which five separate tables may (or may not) reference > > >> > any given row in a table of postal addresses. I am using the primary / > > >> > foreign key support in postgres 7 to represent these references. > > >> > > > >> > My problem is that, any time a reference is removed (either by deleting or > > >> > updating a row in one of the five referencing tables), no garbage > > >> > collection is being performed on the address table. That is, when the > > >> > last reference to an address record goes away, the record is not removed > > >> > from the address table. Over time, my database will fill up with > > >> > abandoned address records. -- -------------------------------------------------------------------------- Albert Reiner Deutsch * English * Esperanto * Latine --------------------------------------------------------------------------