From: Jan Wieck <janwieck@Yahoo.com>
To: Jan Wieck <janwieck@Yahoo.com>
Cc: Forest Wilkinson <fspam@home.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: unreferenced primary keys: garbage collection
Date: Mon, 22 Jan 2001 17:19:48 -0500 (EST)
Message-ID: <200101222219.RAA15009@jupiter.jw.home> (raw)
In-Reply-To: <200101202213.RAA12172@jupiter.jw.home>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: janwieck@Yahoo.com, fspam@home.com
Subject: Re: unreferenced primary keys: garbage collection
In-Reply-To: <200101222219.RAA15009@jupiter.jw.home>
References: <200101202213.RAA12172@jupiter.jw.home> <200101222219.RAA15009@jupiter.jw.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox