Received: from smtp1b.mail.yahoo.com (smtp3.mail.yahoo.com [128.11.68.135]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0MMFr075718 for ; Mon, 22 Jan 2001 17:15:53 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from j13.us.greatbridge.com (HELO jupiter.jw.home) (216.54.52.153) by smtp.mail.vip.suc.yahoo.com with SMTP; 22 Jan 2001 23:05:24 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id RAA15009; Mon, 22 Jan 2001 17:19:48 -0500 From: Jan Wieck Message-Id: <200101222219.RAA15009@jupiter.jw.home> Subject: Re: unreferenced primary keys: garbage collection In-Reply-To: <200101202213.RAA12172@jupiter.jw.home> from Jan Wieck at "Jan 20, 2001 05:13:27 pm" To: Jan Wieck Date: Mon, 22 Jan 2001 17:19:48 -0500 (EST) CC: Forest Wilkinson , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/279 X-Sequence-Number: 948 Jan Wieck wrote: > 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. > > While this behaviour makes sense in your case, it's not > subject to referential integrity constraints. You could > arrange for it with custom trigger procedures, checking all > the five tables on DELETE or UPDATE on one of them. > > I'll make up a little example and post it the other day - > need to take a nap now and tomorrow will be one of these > 30-hour days (from MET to EST), so don't expect anything > before Monday afternoon (EST). Here it is: CREATE TABLE t_addr ( a_id integer PRIMARY KEY, a_name text ); CREATE TABLE t_customer ( c_id integer PRIMARY KEY, c_address integer REFERENCES t_addr ); CREATE TABLE t_order ( o_id integer PRIMARY KEY, o_customer integer REFERENCES t_customer ON DELETE CASCADE, o_shipaddr integer REFERENCES t_addr ); CREATE FUNCTION tidy_up_addr(integer) RETURNS bool AS ' DECLARE chk_addr ALIAS FOR $1; BEGIN -- -- Check if address is still referenced from t_customer -- IF count(c_address) > 0 FROM t_customer WHERE c_address = chk_addr THEN RETURN ''f''; END IF; -- -- Check if address is still referenced from t_order -- IF count(o_shipaddr) > 0 FROM t_order WHERE o_shipaddr = chk_addr THEN RETURN ''f''; END IF; -- -- Address not required any more - get rid of it. -- DELETE FROM t_addr WHERE a_id = chk_addr; RETURN ''t''; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION customer_upd_or_del() RETURNS opaque AS ' BEGIN PERFORM tidy_up_addr(old.c_address); RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER customer_upd_or_del AFTER UPDATE OR DELETE ON t_customer FOR EACH ROW EXECUTE PROCEDURE customer_upd_or_del(); CREATE FUNCTION order_upd_or_del() RETURNS opaque AS ' BEGIN PERFORM tidy_up_addr(old.o_shipaddr); RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER order_upd_or_del AFTER UPDATE OR DELETE ON t_order FOR EACH ROW EXECUTE PROCEDURE order_upd_or_del(); We have two tables referencing the address table. Each of get's it's own trigger, simply calling the tidy-up function that removes the address if it's not referenced any more. Thus, adding a 3rd referencing table to the schema needs to add the check for reference to one central function, plus a very simple trigger on the new table. Hope this works for you. Have fun, Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com