Referential cascade technique 
Author Message
 Referential cascade technique

A table of lookup codes has many tables which reference it via foreign-key
declarations, and I want to be able to merge two codes into one.

For example lets say we have a CUSTOMER table and it uses a lookup 'code'
field from the CUSTOMER_TYPE table.

create table customer (
    ...
    typeCode text not null,
    ...
);

create table customerType (
    code text not null,
    ...
    constraint pk primary key (code)
);

alter table customer add constraint fk
 foreign key (typeCode)
 references customerType(code)
 on update cascade;

Everything will work just fine if we change the value of a code in the
customerType table the values should propagate through to the customers.  
But, if we have two codes in the code table, say 'wholesale' and
'distributor' and decide that the distributor type is no longer needed, we
need to set all customers (and about a dozen other tables) that were
distributor to wholesale.  Although updating the entry of 'distributor' to
'wholesale' would update all the foreign references we can't since there is a
primary key on code in the customerType table.

The best way I can think of doing this is to write a procedure which will
lock the old code in the code table, search the data dictionary for all
dependents, loop through each of them and construct/execute dynamic update
queries to alter the appropriate dependent records, and then deletes the old
code from the code table (this is how we did it in oracle).

Anyone have a better approach?

Assuming this is the right way to do this can someone advise me where/how to
extract the required data from the pg_xxx tables?

thanks,
Mike.

===================
Mike Finn
Tactical Executive Systems

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sat, 10 Jan 2004 02:44:36 GMT
 Referential cascade technique

Mike

I use the following PLPERL/select "code" to view all FK's in my database
.  I guess the "select" could be made into a pg_fkeys view.  What do
people think...

Just a note.  I used PLPERL because the fkey data is stored in a BYTEA
data field and other then a "C" function PLPERL works fine for me...

Let me know if it works for you..
Jim

--
-- I called this function "j" during development and never changed
-- it.
--
CREATE FUNCTION j(bytea,varchar) RETURNS text AS '

$a = $data[0] if $_[1] eq "FKNAME";
$a = $data[1] if $_[1] eq "FTAB";
$a = $data[2] if $_[1] eq "TTAB";


$a =~ s/,+$//g;
return $a;
' LANGUAGE 'plperl';

select  a.tgconstrname,
        j(tgargs,'FTAB'::varchar) || '(' || j(tgargs,'FCOLS'::varchar)
|| ')' as from,
        j(tgargs,'TTAB'::varchar) || '(' || j(tgargs,'TCOLS'::varchar)
|| ')' as references
,
        cd as "cascade_delete",
        cu as cascade_update
from ( pg_trigger a left join
(select tgconstrname,'Y' as cd from pg_trigger where tgfoid = 1646) b on
(a.tgconstrname =
b.tgconstrname) )
left join
(select tgconstrname,'Y' as cu from pg_trigger where tgfoid = 1647) c on
(a.tgconstrname =
b.tgconstrname)
where
        tgfoid = 1644
        and
        tgisconstraint;

Quote:

> A table of lookup codes has many tables which reference it via
foreign-key
> declarations, and I want to be able to merge two codes into one.

> For example lets say we have a CUSTOMER table and it uses a lookup
'code'
> field from the CUSTOMER_TYPE table.

> create table customer (
>     ...
>     typeCode text not null,
>     ...
> );

> create table customerType (
>     code text not null,
>     ...
>     constraint pk primary key (code)
> );

> alter table customer add constraint fk
>  foreign key (typeCode)
>  references customerType(code)
>  on update cascade;

> Everything will work just fine if we change the value of a code in the
> customerType table the values should propagate through to the
customers.  
> But, if we have two codes in the code table, say 'wholesale' and
> 'distributor' and decide that the distributor type is no longer
needed, we
> need to set all customers (and about a dozen other tables) that were
> distributor to wholesale.  Although updating the entry of
'distributor' to
> 'wholesale' would update all the foreign references we can't since
there is a
> primary key on code in the customerType table.

> The best way I can think of doing this is to write a procedure which
will
> lock the old code in the code table, search the data dictionary for
all
> dependents, loop through each of them and construct/execute dynamic
update
> queries to alter the appropriate dependent records, and then deletes
the old
> code from the code table (this is how we did it in oracle).

> Anyone have a better approach?

> Assuming this is the right way to do this can someone advise me
where/how to
> extract the required data from the pg_xxx tables?

> thanks,
> Mike.

> ===================
> Mike Finn
> Tactical Executive Systems

> ---------------------------(end of

broadcast)---------------------------

Quote:
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 10 Jan 2004 08:28:42 GMT
 Referential cascade technique

Quote:

> I use the following PLPERL/select "code" to view all FK's in my database
> .  I guess the "select" could be made into a pg_fkeys view.  What do
> people think...

The following was posted to the list a while ago, compliments of Michael
Fork:

   SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred,
       pg_proc.proname, pg_proc_1.proname FROM pg_class pc,
       pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger,
       pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt
   WHERE  pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid
       AND pg_trigger.tgconstrrelid = pc.oid
       AND pg_proc.oid = pg_trigger.tgfoid
       AND pg_trigger_1.tgfoid = pg_proc_1.oid
       AND pg_trigger_1.tgconstrrelid = pc.oid
       AND ((pc.relname= '<< TABLENAME >>>')
       AND (pp.proname LIKE '%%ins')
       AND (pg_proc.proname LIKE '%%upd')
       AND (pg_proc_1.proname LIKE '%%del')
       AND (pg_trigger.tgrelid=pt.tgconstrrelid)
       AND (pg_trigger_1.tgrelid = pt.tgconstrrelid));

This will show all foreign keys on a table.

Quote:
> Just a note.  I used PLPERL because the fkey data is stored in a BYTEA
> data field and other then a "C" function PLPERL works fine for me...

[snip]

--
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sat, 10 Jan 2004 08:50:56 GMT
 Referential cascade technique
while the below query does product all foreign keys,  my PLPERL/query
looks much better...

Quote:

> > I use the following PLPERL/select "code" to view all FK's in my
database
> > .  I guess the "select" could be made into a pg_fkeys view.  What do
> > people think...

> The following was posted to the list a while ago, compliments of
Michael
> Fork:

>    SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred,
>        pg_proc.proname, pg_proc_1.proname FROM pg_class pc,
>        pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger,
>        pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt
>    WHERE  pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid
>        AND pg_trigger.tgconstrrelid = pc.oid
>        AND pg_proc.oid = pg_trigger.tgfoid
>        AND pg_trigger_1.tgfoid = pg_proc_1.oid
>        AND pg_trigger_1.tgconstrrelid = pc.oid
>        AND ((pc.relname= '<< TABLENAME >>>')
>        AND (pp.proname LIKE '%%ins')
>        AND (pg_proc.proname LIKE '%%upd')
>        AND (pg_proc_1.proname LIKE '%%del')
>        AND (pg_trigger.tgrelid=pt.tgconstrrelid)
>        AND (pg_trigger_1.tgrelid = pt.tgconstrrelid));

> This will show all foreign keys on a table.

> > Just a note.  I used PLPERL because the fkey data is stored in a
BYTEA
> > data field and other then a "C" function PLPERL works fine for me...

> [snip]

> --
> Dominic J. Eidson
>                                         "Baruk Khazad! Khazad
ai-menu!" - Gimli

-------------------------------------------------------------------------------
Quote:
> http://www.the-infinite.org/            

http://www.the-infinite.org/~dominic/
Quote:

> ---------------------------(end of

broadcast)---------------------------

Quote:
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Sat, 10 Jan 2004 22:56:19 GMT
 Referential cascade technique

Quote:
> I use the following PLPERL/select "code" to view all FK's in my database
> ..  I guess the "select" could be made into a pg_fkeys view.  What do
> people think...

I think what you've done is quite good.  I have had to adapt what you've done
since I really don't want to use perl.  I want to minimize the dependencies
the the db has so I've opted to figure out how to do it in pgplsql (and yes
those byte arrays a pain).

One assumption I have made is that the foreign key references are all single
field (not compound key).  This works in my system since this whole issue is
really for code tables, where we migrate the actual data to the referring
table so as to avoid a ton of joins for simple little code lookups.

I am including the whole shebang here in case it is of use to others, or
simple as a non-trivial example of plpgsql (which I have just had a crash
course in!).

-- a view to display foreign key references
-- BUG: assumes that all fk relationships are sigle key
drop view foreignKeyReference;
create view foreignKeyReference as
    select  
      --field order is 0. procName
      --               1. referringTable
      --               2. localTable
      --               3. UNSPECIFIED
      --               4. referringField
      --               5. localField
      proc.proname as proc,
      trig.tgnargs = 6 as isSingleFieldKey,
      extractParmFromBytea(trig.tgargs, 0) as name,
      extractParmFromBytea(trig.tgargs, 2) as tableName,
      extractParmFromBytea(trig.tgargs, 5) as fieldName,
      extractParmFromBytea(trig.tgargs, 1) as referringTable,
      extractParmFromBytea(trig.tgargs, 4) as referringField
    from
       pg_class    class,
       pg_trigger  trig,
       pg_proc     proc
    where
         class.relname not like 'pg_%' and class.relkind = 'r'
     and trig.tgrelid = class.oid
     and trig.tgisconstraint = true
     and trig.tgfoid = proc.oid
     --and proc.proname like 'RI_FKey%_del'
    order by
        class.relname, trig.tgname
    ;

-- Get the Nth parm from a parameter set stored as null delimited
-- bytea as a string.  Parameters are numbered from 0
drop function extractParmFromBytea(bytea, int);
create function extractParmFromBytea(bytea, int) returns text as '
    declare
        bparm   alias for $1;  --bytea parameters
        parmIdx alias for $2;

        parmCount  int = 0;
        charCount  int = 0;
        c          int;
        parm       text = '''';

    begin
        while parmCount < parmIdx loop
            c := get_byte(bparm, charCount);
            charCount := charCount + 1;
            if c = 0 then
                parmCount := parmCount + 1;
            end if;
        end loop;

        while parmCount = parmIdx loop
            c := get_byte(bparm, charCount);
            charCount := charCount + 1;
            if c = 0 then
                parmCount := parmCount + 1;
            else
                parm := parm || chr(c);
            end if;
        end loop;

        return parm;
    end;
' language 'plpgsql';

-- For a given table (localTable) and field (localField) which are known
-- to be referred to via one or more foregin key relationships, update all
-- the dependant foreign references from oldValue to newValue.  This will
-- effectively move the dependencies from one record in localTable to another
-- record in localTable.
-- BUG: assumes that all fk relationships are sigle key
drop   function moveDependants(text,text,text,text);
create function moveDependants(text,text,text,text) returns boolean as '
    declare
        --parameters
        localTable alias for $1;
        localField alias for $2;
        oldValue   alias for $3;
        newValue   alias for $4;

        --translation from bytea to parms
        referringTable text;
        referringField text;

        --main part
        query      text;
        fks        record;
        rc         int;

    begin
        --lock the source oldValue exclusively
        query := '' select null''
              || '' from ''
              ||      localTable
              || '' where ''
              ||      localField || '' = '' || quote_literal(oldValue)
              || '' for update ''
              ;
        execute query;

        --lock the source newValue exclusively
        query := '' select null''
              || '' from ''
              ||      localTable
              || '' where ''
              ||      localField || '' = '' || quote_literal(newValue)
              || '' for update ''
              ;
        execute query;

        get diagnostics rc = ROW_COUNT;
        if rc <= 0 then
            raise exception ''newValue of % does not exist in %.%'',
newValue, localTable, localField;
        end if;

        --find the parameters for the del triggers
        query := '' select ''
              || ''   tgargs ''
              || '' from ''
              || ''   pg_class    class, ''
              || ''   pg_trigger  trig,  ''
              || ''   pg_proc     proc   ''
              || '' where ''
              || ''       class.relname = '' ||
quote_literal(lower(localTable))
              || ''   and class.relkind = ''''r'''' ''
              || ''   and trig.tgrelid = class.oid ''
              || ''   and trig.tgisconstraint = true ''
              || ''   and trig.tgfoid = proc.oid ''
              || ''   and proc.proname like ''''RI_FKey%_del'''' ''
              ;

        --field order is 0. procName
        --               1. referringTable
        --               2. localTable
        --               3. UNSPECIFIED
        --               4. referringField
        --               5. localField
        for fks in execute query loop
            if lower(localField) = extractParmFromBytea(fks.tgargs, 5) then
                --okay this reference is for localTable and localField
                --so update the referring values from oldValue to newValue
                query := '' update ''
                      ||      extractParmFromBytea(fks.tgargs, 1)
                      || '' set ''
                      ||      extractParmFromBytea(fks.tgargs, 4) || '' =''
|| quote_literal(newValue)
                      || '' where ''
                      ||      extractParmFromBytea(fks.tgargs, 4) || '' =''
|| quote_literal(oldValue)
                      ;
                --raise notice ''processing %'', query;
                execute query;
            end if;
        end loop;
        return true; --bogus return value
    end;
' language 'plpgsql';

-- an example
-- believe it or not the postal abbreviation for  Quebec
-- is actually 'QC'... its true, go figure!
/*
begin
    select moveDependants('codeProv{*filter*}ate','code','PQ','QC');
    delete from codeProv{*filter*}ate
        where code = 'Q.C.';
commit;
*/

===================
Mike Finn
Tactical Executive Systems

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Sun, 11 Jan 2004 06:26:06 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Referential integrity and cascading deletes.....

2. Cascaded Referential Integrity

3. Referential Integrity and Cascades

4. Cascading deletes with referential integrity catching me out!

5. Execution order in triggers and referential cascade updates

6. Cascade deletes to enfor referential entegrity

7. NEWBIE: Cascading referential integrity constraint questions

8. Cascading Deletes with Referential Integrity

9. MS SQL 6.5: Declarative Referential Integrity / Cascading Update

10. Cascading Referential Integrity Problems (Updates)

11. Referential Integrity VS Cascade Deletes

12. Cascade Referential Integrity - SQLServer2000


 
Powered by phpBB® Forum Software