RI status report #4 (come and join) - Mailing list pgsql-hackers

Co-developers,

    I've  prepared  some  things  and  it's  time  now  to  start
    contributing to this subproject.

    What's done so far (I've included a little SQL script at  the
    end that show's what's working):

    -  The  parser  recognizes  the  new  syntax  for  constraint
       triggers and hands down all the new  attributes  into  the
       utility function for CREATE TRIGGER.

    -  The  utility  function  for CREATE TRIGGER handles all the
       new attributes so constraints can be defined with a  bunch
       of

       CREATE CONSTRAINT TRIGGER ...

       statements after CREATE TABLE.

    -  The parser recognizes the new SET CONSTRAINTS command.

    -  The   trigger  manager  handles  trigger  deferred  states
       correctly so that  circular  constraint  checks  would  be
       possible  by  deferring  trigger  invocation until COMMIT.
       Also it traces multiple operations on  the  same  row  and
       invokes   only  that  trigger  that  is  defined  for  the
       resulting operation if all operations during a transaction
       are condensed.

    -  In   backend/utils/adt/ri_triggers.c   are   some  support
       routines  and  the  first  real  trigger  procedures  that
       implement:

           FOREIGN KEY ... REFERENCES ... MATCH FULL
               (checks for FK existance in PK table on INSERT and
               UPDATE)

           FOREIGN KEY ... MATCH FULL ... ON DELETE CASCADE
               (constraint deletes references from  FK  table  on
               DELETE of PK row)

       I hope that's enough example implementation to get started
       for you.  If not, ask, ask, ask.

    What we need next (what y'all shall do) is:

    1.  Add all functionality to ri_triggers.c required for

            ON UPDATE CASCADE
            ON DELETE SET NULL
            ON UPDATE SET NULL
            ON DELETE SET DEFAULT
            ON UPDATE SET DEFAULT

    2.  Add full FOREIGN KEY syntax to  the  parser  and  arrange
        that the appropriate CREATE CONSTRAINT TRIGGER statements
        are executed at CREATE TABLE just like the  CREATE  INDEX
        is done for PRIMARY KEY.

    3.  Building  a  test  suite  for  FOREIGN KEY ... MATCH FULL
        support.

    Anyone who wants to contribute to this should at  least  drop
    us  a  note  on  which detail he's starting to work - just to
    avoid frustration.  Patches should be sent to me directly and
    I'll incorporate them into the CVS tree.

    I'll keep my hands off from all the above now and continue to
    work on the deferred  trigger  manager  (the  disk  buffering
    during huge transactions).


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



drop table t1;
drop table t2;


-- **********
-- * Create a PK and an FK table.
-- **********
create table t1 (a1 int4, b1 int4, c1 text, PRIMARY KEY (a1, b1));
create table t2 (a2 int4, b2 int4, c2 text);


-- **********
-- * Manually setup constraint triggers for t2 as if
-- *
-- *    CONSTRAINT check_t2_key
-- *        FOREIGN KEY (a2, b2) REFERENCES t1 (a1, b1)
-- *        MATCH FULL
-- *        ON DELETE CASCADE
-- *
-- * was specified in the table schema. These are the commands
-- * which should later be executed automatically during CREATE TABLE
-- * like done for the index t1_pkey due to the PRIMARY KEY constraint.
-- **********
create constraint trigger "check_t2_key" after insert on t2
    deferrable initially immediate
    for each row execute procedure
    "RI_FKey_check_ins" ('check_t2_key', 't2', 't1', 'FULL',
                        'a2', 'a1', 'b2', 'b1');
create constraint trigger "check_t2_key" after update on t2
    deferrable initially immediate
    for each row execute procedure
    "RI_FKey_check_upd" ('check_t2_key', 't2', 't1', 'FULL',
                        'a2', 'a1', 'b2', 'b1');
create constraint trigger "check_t2_key" after delete on t1
    deferrable initially immediate
    for each row execute procedure
    "RI_FKey_cascade_del" ('check_t2_key', 't2', 't1', 'FULL',
                        'a2', 'a1', 'b2', 'b1');

-- **********
-- * Insert some PK values
-- **********
insert into t1 values (1, 1, 'key 1');
insert into t1 values (2, 2, 'key 2');
insert into t1 values (3, 3, 'key 3');

-- **********
-- * Check FK on insert
-- **********
-- The first two are O.K.
insert into t2 values (1, 1, 'ref 1');
insert into t2 values (2, 2, 'ref 2');
-- This one must fail
insert into t2 values (4, 3, 'ref 4');
-- The following one is O.K. again since all FK attributes are NULL
insert into t2 (c2) values ('null');
-- This one not - MATCH FULL does not allow mixing of NULL/notNULL
insert into t2 (a2, c2) values (1, 'full violation');

-- **********
-- * Check FK on update
-- **********
-- These two should fail
update t2 set a2 = 4 where a2 = 1;
update t2 set a2 = 3 where a2 = 2;
-- These two should succeed
update t2 set a2 = 3, b2 = 3 where a2 = 2;
update t2 set c2 = '' where a2 = 1;

-- **********
-- * Check the cascaded delete
-- **********
select * from t2;
delete from t1 where a1 = 1 and b1 = 1;
select * from t2;

-- **********
-- * Now for deferred constraint checks
-- **********
-- First the case that doesn't work
begin;
insert into t2 values (6, 6, 'ref 6');
insert into t1 values (6, 6, 'key 6');
commit;
-- But it must work this way
begin;
set constraints check_t2_key deferred;
insert into t2 values (7, 7, 'ref 7');
insert into t1 values (7, 7, 'key 7');
commit;