Index: openacs-4/packages/acs-content-repository/acs-content-repository.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/acs-content-repository.info,v
diff -u -r1.101 -r1.102
--- openacs-4/packages/acs-content-repository/acs-content-repository.info	31 Jul 2011 23:11:45 -0000	1.101
+++ openacs-4/packages/acs-content-repository/acs-content-repository.info	4 Aug 2011 08:26:20 -0000	1.102
@@ -7,7 +7,7 @@
     <initial-install-p>t</initial-install-p>
     <singleton-p>t</singleton-p>
 
-    <version name="5.8.0d2" url="http://openacs.org/repository/download/apm/acs-content-repository-5.8.0d2.apm">
+    <version name="5.8.0d3" url="http://openacs.org/repository/download/apm/acs-content-repository-5.8.0d3.apm">
         <owner url="http://openacs.org">OpenACS</owner>
         <summary>The canonical repository for OpenACS content.</summary>
         <release-date>2011-06-12</release-date>
@@ -20,7 +20,7 @@
         <license>GPL</license>
         <maturity>3</maturity>
 
-        <provides url="acs-content-repository" version="5.8.0d2"/>
+        <provides url="acs-content-repository" version="5.8.0d3"/>
         <requires url="acs-kernel" version="5.8.0d1"/>
         <requires url="acs-service-contract" version="5.8.0d1"/>
         <requires url="search" version="5.8.0d1"/>
Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v
diff -u -r1.58 -r1.59
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql	7 Jul 2011 10:46:02 -0000	1.58
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql	4 Aug 2011 08:26:20 -0000	1.59
@@ -748,9 +748,12 @@
 --------------------------------------------------------------
 
 create table cr_item_publish_audit (
-  item_id            integer, 
-  old_revision       integer, 
-  new_revision       integer, 
+  item_id            integer 
+                     constraint cr_item_publish_audit_item_fk references cr_items,
+  old_revision       integer
+                     constraint cr_item_publish_audit_orev_fk references cr_revisions (revision_id),
+  new_revision       integer
+                     constraint cr_item_publish_audit_nrev_fk references cr_revisions (revision_id),
   old_status         varchar(40),
   new_status         varchar(40),
   publish_date       timestamptz
@@ -759,6 +762,8 @@
 );
 
 create index cr_item_publish_audit_idx on cr_item_publish_audit(item_id);
+create index cr_item_publish_audit_orev_idx on cr_item_publish_audit(old_revision);
+create index cr_item_publish_audit_nrev_idx on cr_item_publish_audit(new_revision);
 
 comment on table cr_item_publish_audit is '
   An audit table (populated by a trigger on cr_items.live_revision)
Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql	4 Aug 2011 08:26:20 -0000	1.1
@@ -0,0 +1,16 @@
+-- content_revision__del() uses cr_item_publish_audit.old_revision and
+-- cr_item_publish_audit.new_revision to delete entries from
+-- cr_item_publish_audit. This takes forever on non-toy databases.
+
+create index cr_item_publish_audit_orev_idx on cr_item_publish_audit(old_revision);
+create index cr_item_publish_audit_nrev_idx on cr_item_publish_audit(new_revision);
+
+-- make sure, we can add the foreign keys
+delete from cr_item_publish_audit where item_id not in (select item_id from cr_items);
+delete from cr_item_publish_audit where old_revision not in (select revision_id from cr_revisions);
+delete from cr_item_publish_audit where new_revision not in (select revision_id from cr_revisions);
+
+-- add the foreign keys
+alter table cr_item_publish_audit add constraint cr_item_publish_audit_item_fk foreign key (item_id) references cr_items (item_id);
+alter table cr_item_publish_audit add constraint cr_item_publish_audit_orev_fk foreign key (old_revision) references cr_revisions (revision_id);
+alter table cr_item_publish_audit add constraint cr_item_publish_audit_nrev_fk foreign key (new_revision) references cr_revisions (revision_id);