pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (7+ messages)  latest in thread

Message-ID: <200101222219.RAA15009@jupiter.jw.home>
Permalink:  ../200101222219.RAA15009@jupiter.jw.home/
Also on:    postgresql.org/message-id/200101222219.RAA15009@jupiter.jw.home

 · 

reply

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