Re: unreferenced primary keys: garbage collection

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com