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. #
#================================================== [EMAIL PROTECTED] #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
