Re: unreferenced primary keys: garbage collection - Mailing list pgsql-sql

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
EXECUTEPROCEDURE 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
PROCEDUREorder_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