Thread: Can't delete - Need cascading update instead

For various reasons, I can't actually delete records from my database.
Instead, I have a boolean 'active' field for each table.  I need to
implement something like cascading delete, but instead of deleting, I
need to set active=false.

I've googled and haven't found a solution.  I had two ideas, neither of
which worked out.

One thing I tried is to set ON DELETE CASCADE for all of my foreign key
constraints.  Then I added a rule ON DELETE DO ALSO UPDATE ... and a
BEFORE DELETE trigger to stop the actual deletion.  Unfortunately, that
also stops the cascade.

My other idea involved an ON DELETE DO INSTEAD UPDATE ... rule and a
BEFORE UPDATE PL/pgSQL trigger that manually implemented the cascading.
  The problem with that is that the only way I can find to generate an
approproate UPDATE or DELETE statement is to create a string and then
EXECUTE it, but I need values from the NEW or OLD records, which
apparently aren't usable from an EXECUTE statement.  I'll include my
code at the end.

I haven't looked into using C.  If that's what it takes, it'll be faster
for me to just do it client side.

Are there any other potential server-side solutions that I'm missing?

Thanks for reading,

Adam Tomjack


--------------

My failed update trigger:

CREATE OR REPLACE FUNCTION my_cascading_deleter()
   RETURNS "trigger" LANGUAGE 'plpgsql' VOLATILE AS
$BODY$
DECLARE
    r RECORD;
    r2 RECORD;
    r3 RECORD;
    i RECORD;
    sql TEXT;
BEGIN
IF NEW.active=false AND OLD.active=true THEN
   -- Loop over each table that references this one.
   FOR r IN SELECT child.relname AS child, child.oid AS childid,
                   parent.oid AS parentid, c.conkey AS childkey,
                   c.confkey AS parentkey FROM pg_constraint c
     JOIN pg_class child ON (child.oid=c.conrelid)
     JOIN pg_class parent ON (parent.oid=c.confrelid)
     WHERE contype='f' and parent.oid=TG_RELID
   LOOP
     sql := 'DELETE FROM '||r.child||' WHERE ';
     -- Loop over every column in the primary key
     FOR i IN 1 .. array_upper(r.childkey, 1) LOOP
       SELECT INTO r2 attname FROM pg_attribute WHERE attrelid=r.childid
                                          AND attnum=r.childkey[i];
       SELECT INTO r3 attname FROM pg_attribute WHERE attrelid=r.parentid
                                          AND attnum=r.parentkey[i];
       sql := sql || ' ' || r2.attname || '=OLD.' || r3.attname;
     END LOOP;
     EXECUTE sql; -- ERROR, doesn't understand the OLD record
   END LOOP;
END IF;
RETURN NEW;
END;
$BODY$
;

Adam Tomjack wrote:
> For various reasons, I can't actually delete records from my database.
> Instead, I have a boolean 'active' field for each table.  I need to
> implement something like cascading delete, but instead of deleting, I
> need to set active=false.
>
> I've googled and haven't found a solution.  I had two ideas, neither of
> which worked out.
>
> One thing I tried is to set ON DELETE CASCADE for all of my foreign key
> constraints.  Then I added a rule ON DELETE DO ALSO UPDATE ... and a
> BEFORE DELETE trigger to stop the actual deletion.  Unfortunately, that
> also stops the cascade.

I'd be tempted to add triggers to the delete to copy old versions of the
data to a set of archive tables.

Alternatively, if you made "active" part of the primary and foreign-keys
on the tables concerned you could cascade updates.

> My other idea involved an ON DELETE DO INSTEAD UPDATE ... rule and a
> BEFORE UPDATE PL/pgSQL trigger that manually implemented the cascading.
>  The problem with that is that the only way I can find to generate an
> approproate UPDATE or DELETE statement is to create a string and then
> EXECUTE it, but I need values from the NEW or OLD records, which
> apparently aren't usable from an EXECUTE statement.  I'll include my
> code at the end.

>       sql := sql || ' ' || r2.attname || '=OLD.' || r3.attname;

You can't refer to the OLD.xxx or NEW.xxx in the query-string itself,
you need to add its value. Of course, that causes problems because you
can't dynamically refer to OLD[r3.attname] or whatever syntax you'd be
tempted by.

TCL or one of the other dynamic languages is better for this. I've
attached a sample of some code and history tables that do something
similar to what you're trying to do. I don't make any great claims for
my TCL coding skills - most of it was pieced together from tutorials.

HTH
--
   Richard Huxton
   Archonet Ltd
-- History Tracking Trigger-Functions
--

CREATE TABLE history (
    hid   SERIAL UNIQUE NOT NULL,
    cid   int4 NOT NULL CONSTRAINT valid_client REFERENCES client ON DELETE CASCADE,
    ts    timestamp(0) with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uid   int4 NOT NULL CONSTRAINT valid_uid REFERENCES user_info DEFAULT app_session_int_vol('UID'),
    tbl   varchar(32) NOT NULL,
    act   char(1) NOT NULL CONSTRAINT valid_act CHECK (act IN ('U','I','D')),
    PRIMARY KEY (hid)
);

CREATE TABLE history_detail (
    hid  integer NOT NULL CONSTRAINT valid_hid REFERENCES client_history,
    col  varchar(32) NOT NULL,
    was  text,
    PRIMARY KEY (hid,col)
);

-- tcl_track_history(TABLE-NAME)
--    Set TABLE-NAME when creating the trigger. Will automatically record change
--    details in tables history/history_detail
--
CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS '
    switch $TG_op {
        DELETE {
            if { [llength [array names OLD cid]] > 0 } {
                set clival $OLD(cid)
            } else {
                set clival "NULL"
            }
            spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')"
        }
        INSERT {
            if { [llength [array names NEW cid]] > 0 } {
                set clival $NEW(cid)
            } else {
                set clival "NULL"
            }
            spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')"
        }
        UPDATE {
            if { [llength [array names OLD cid]] > 0 } {
                set clival $OLD(cid)
            } else {
                set clival "NULL"
            }
            set inserted_main_history_row false
            foreach {col} $TG_relatts {
                # First result seems to be an empty string when stepping through columns
                if { $col > "" } {
                    # Check if OLD/NEW contain a value
                    if { [llength [array names OLD $col]] > 0 } {
                        set oldval $OLD($col)
                    } else {
                        set oldval "NULL"
                    }
                    if { [llength [array names NEW $col]] > 0 } {
                        set newval $NEW($col)
                    } else {
                        set newval "NULL"
                    }
                    if { $oldval != $newval } {
                        if { !$inserted_main_history_row } {
                            spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
                            set inserted_main_history_row true
                        }
                        spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')"
                    }
                }
            }
        }
    }
    return OK
' LANGUAGE pltcl;

CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE
tcl_track_history('client');