RI status report #4 (come and join)

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(at)debis(dot)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;