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

Here's the response that I got from Stephan Szabo:

> It should be fixed at some point.  I'm not 100% sure what's 
> going to be involved, I'm planning on looking at this for the
> deferred state anyway.  I was hoping that one of the hackers
> would read my proposed solution and tell me if they thought it
> was a good idea.
>
> However, this may actually technically be an error case if 
> the function runs as one statement.  I've got to figure out 
> the triggered data change stuff in SQL99. :(

Here's the thread where I described the bug and Stephan responded. Unfortunately, this is way over my head, so if someone with more experience understands what is going on, feel free to jump in!

I also found another (more minor) bug in pg 7.1.2 which I mentioned earlier to Dan. That has been fixed in 7.2 (in development) and will be fixed in 7.1.3, if they decide to put out another 7.1.x release. So, for the moment, we should just work around it.

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();

[05/Jul/2001:10:28:24][6730.24581][-conn3-] Notice: Querying '
select content_item__delete('2364');'
NOTICE:  Deleting associated workflows...
NOTICE:  Deleting symlinks...
NOTICE:  Unscheduling item...
NOTICE:  Deleting associated revisions...
NOTICE:  Deleting associated item templates...
NOTICE:  Deleting item relationships...
NOTICE:  Deleting child relationships...
NOTICE:  Deleting parent relationships...
NOTICE:  Deleting associated permissions...
NOTICE:  Deleting keyword associations...
NOTICE:  Deleting associated comments...
NOTICE:  identifier "journal_entry__delete_for_object" will be truncated to "journal_entry__delete_for_objec"
NOTICE:  Deleting content item...
[05/Jul/2001:10:28:24][6730.24581][-conn3-] Error: Ns_PgExec: result
status: 7 message: ERROR:  cr_items_latest_fk referential integrity
violation - key referenced from cr_items not found in cr_revisions

I implemented a successful workaround, which is to set latest_revision and live_revision to null in a separate transaction, and then calling content_item__delete.

Is there any way to work around this problem in the definition of content_item__delete, or are we stuck waiting for the bug to get fixed in postgresql?

create function content_item__delete (integer)
returns integer as '
declare
  delete__item_id                alias for $1;  
  v_wf_cases_val                 record;
  v_symlink_val                  record;
  v_revision_val                 record;
  v_rel_val                      record;
  v_rec                          record;
begin

  raise NOTICE ''Deleting associated workflows...'';
  -- 1) delete all workflow cases associated with this item
  for v_wf_cases_val in select
                          case_id
                        from
                          wf_cases
                        where
                          object_id = delete__item_id 
  LOOP
    PERFORM workflow_case__delete(v_wf_cases_val.case_id);
  end loop;

  raise NOTICE ''Deleting symlinks...'';
  -- 2) delete all symlinks to this item
  for v_symlink_val in select 
                         symlink_id
                       from 
                         cr_symlinks
                       where 
                         target_id = delete__item_id 
  LOOP
    PERFORM content_symlink__delete(v_symlink_val.symlink_id);
  end loop;

  raise NOTICE ''Unscheduling item...'';
  delete from cr_release_periods
    where item_id = delete__item_id;

  raise NOTICE ''Deleting associated revisions...'';
  -- 3) delete all revisions of this item
  delete from cr_item_publish_audit
    where item_id = delete__item_id;

  for v_rec in select revision_id
               from cr_revisions 
               where item_id = delete__item_id 
  LOOP
        PERFORM acs_object__delete(v_rec.revision_id);
  end LOOP;

  raise NOTICE ''Deleting associated item templates...'';
  -- 4) unregister all templates to this item
  delete from cr_item_template_map
    where item_id = delete__item_id; 

  raise NOTICE ''Deleting item relationships...'';
  -- Delete all relations on this item
  for v_rel_val in select
                     rel_id
                   from
                     cr_item_rels
                   where
                     item_id = delete__item_id
                   or
                     related_object_id = delete__item_id 
  LOOP
    PERFORM acs_rel__delete(v_rel_val.rel_id);
  end loop;  

  raise NOTICE ''Deleting child relationships...'';
  for v_rel_val in select
                     rel_id
                   from
                     cr_child_rels
                   where
                     child_id = delete__item_id 
  LOOP
    PERFORM acs_rel__delete(v_rel_val.rel_id);
  end loop;  

  raise NOTICE ''Deleting parent relationships...'';
  for v_rel_val in select
                     rel_id, child_id
                   from
                     cr_child_rels
                   where
                     parent_id = delete__item_id 
  LOOP
    PERFORM acs_rel__delete(v_rel_val.rel_id);
    PERFORM content_item__delete(v_rel_val.child_id);
  end loop;  

  raise NOTICE ''Deleting associated permissions...'';
  -- 5) delete associated permissions
  delete from acs_permissions
    where object_id = delete__item_id;

  raise NOTICE ''Deleting keyword associations...'';
  -- 6) delete keyword associations
  delete from cr_item_keyword_map
    where item_id = delete__item_id;

  raise NOTICE ''Deleting associated comments...'';
  -- 7) delete associated comments
  PERFORM journal_entry__delete_for_object(delete__item_id);

  -- context_id debugging loop
  --for v_error_val in c_error_cur loop
  --  raise NOTICE ''ID='' || v_error_val.object_id || '' TYPE='' 
  --    || v_error_val.object_type);
  --end loop;

  raise NOTICE ''Deleting content item...'';
  PERFORM acs_object__delete(delete__item_id);

  return 0; 
end;' language 'plpgsql';