Forum OpenACS Development: Response to Weird error invoking a plpgsql function (content_revision__delete)

How timely. I am trying to construct a regression test of an acs service (I'm helping out Rafael port acs-events). I can trigger the data change violation if, as Dan pointed out, in one transaction I insert and delete a row in a table whose primary key is a foreign key of another table. This happens even if the other table is not involved at all. Here's a simplified script that will reproduce the data change violation.

create table ut_time_intervals (
    interval_id         integer
                        primary key,
    start_date          timestamp,
    end_date            timestamp,
    constraint ut_time_interval_date_order_ck
    check(start_date <= end_date)
);

-- Need a source of primary keys
create sequence ut_timespan_seq;

-- Make a table that references the primary key of above
-- If this table is not created, no data-change violation is
-- triggered. Note that this table is not used by the functions below.
create table ut_timespans (
    timespan_id     integer not null,
    interval_id     integer
                    references ut_time_intervals on delete cascade
);


create  function ut_time_interval__new (timestamp,timestamp)
returns integer as '
declare
        new__start_date  alias for $1; -- default null,
        new__end_date    alias for $2; -- default null
        v_interval_id    ut_time_intervals.interval_id%TYPE;
begin
        select nextval(''ut_timespan_seq'') into v_interval_id;

        insert into ut_time_intervals
            (interval_id, start_date, end_date)
        values
            (v_interval_id, new__start_date, new__end_date);

        return v_interval_id;

end;' language 'plpgsql';
-- end new;

create function ut_time_interval__delete (
        integer               -- in time_intervals.interval_id%TYPE
)
returns integer as '
declare
        delete__interval_id     alias for $1;
begin

        delete from ut_time_intervals
        where  interval_id = delete__interval_id;

        return 0;

end;' language 'plpgsql';
-- end delete;



create function simple_regression()
returns integer as '
declare
        date1   timestamp := ''2001-01-01'';
        date2   timestamp := ''2001-01-02'';
        v_id    time_intervals.interval_id%TYPE;
begin

        -- Do insertion into the table and deletion in same
        -- transaction to trigger a data change violation

	-- Create an entry
        v_id := ut_time_interval__new(date1,date2);

	-- Other regression tests presumably done here.

	-- Delete the entry.  Will trigger a data change violation
	-- if a table exists (i.e., ut_timespans) that references 
        -- the primary key
        PERFORM ut_time_interval__delete(v_id);

        return 0;

end;' language 'plpgsql';

select (case when simple_regression() = 0
             then
                 'Regression test successful'
             end) as regression_result;

-- Clean up
drop table ut_timespans;
drop table ut_time_intervals;
drop sequence ut_timespan_seq;
drop function ut_time_interval__new(timestamp,timestamp);
drop function ut_time_interval__delete(integer);
drop function simple_regression();

I guess the point is that the "bug" can also affect regression testing, not only audit tables. Dan, is there a regression toolkit similar to utplsql in Oracle? Since a service does not have any "interface", the only way to test it is to actually construct a test. BTW, I am using PG 7.1.1.