Index: openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql,v
diff -u -r1.6 -r1.7
--- openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql	28 Nov 2001 18:39:39 -0000	1.6
+++ openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql	30 Nov 2001 00:50:22 -0000	1.7
@@ -392,11 +392,13 @@
     message_rec record;
 begin
     for message_rec in
-        select object_id as message_id
-          from acs_objects
-         where object_type = 'acs_message'
-           and object_id not in (select message_id 
-                                   from bboard_forum_message_map)
+        select o.object_id as message_id
+          from acs_objects o
+         where o.object_type = 'acs_message'
+            and not exists (select 1
+                            from bboard_forum_message_map bfmm, acs_objects o2
+                            where o2.object_id = bfmm.message_id and
+                              o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey))
     loop
         perform bboard_message__remove(message_rec.message_id);
     end loop;
Index: openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs.tcl,v
diff -u -r1.6 -r1.7
--- openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs.tcl	31 Oct 2001 20:42:07 -0000	1.6
+++ openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs.tcl	30 Nov 2001 00:50:22 -0000	1.7
@@ -999,22 +999,17 @@
     Works around postgresql bugs that make it impossible
     to delete multiple items in a single transaction
 
-    WARNING The Postgres version of these queries
-    assume that acs-messaging is only used by bboard
-    and general-comments,  and will delete entries made
-    by any other package.
-    If this poses a problem, revisit the oracle queries
-    and port the CONNECT BY query faithfully.
+    DRB fixed the assumption that only general-comments and bboard use acs_messaging
+
 } {
     db_foreach get_orphans {
-        select object_id as message_id
-          from acs_objects
-         where object_type = 'acs_message'
-           and object_id not in (select message_id
-                                 from bboard_forum_message_map
-                                 union
-                                 select comment_id
-                                 from general_comments)
+          select object_id as message_id
+          from acs_objects o
+          where o.object_type = 'acs_message'
+            and not exists (select 1
+                            from bboard_forum_message_map bfmm, acs_objects o2
+                            where o2.object_id = bfmm.message_id and
+                            o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey))
     } {
         db_dml clear_revision_references {
             update cr_items
Index: openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql,v
diff -u -r1.15 -r1.16
--- openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql	29 Nov 2001 22:19:47 -0000	1.15
+++ openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql	30 Nov 2001 00:50:22 -0000	1.16
@@ -513,22 +513,27 @@
 
 end;' language 'plpgsql' with (iscachable);
 
--- DRB: Use tree_left() and tree_right() to do selects against
--- tree_sortkey.
+-- DRB: Use tree_right() to do selects against tree_sortkey.  LIKE with
+-- a non-constant right-hand operand never uses an index.  The following
+-- queries will use an index and run much faster.
 
--- To find a node's children in "t":
--- tree_sortkey between tree_left(t.tree_sortkey) and tree_right(t.tree_sortkey)
-
 -- To get the children and the node (i.e. the subtree starting with the node):
+
 -- tree_sortkey between t.tree_sortkey and tree_right(t.tree_sortkey)
 
-create function tree_left(varchar) returns varchar as '
-declare
-  p_tree_sortkey alias for $1;
-begin
-  return p_tree_sortkey;
-end;' language 'plpgsql' with (iscachable);
+-- To find a node's children in "t":
 
+-- tree_sortkey between t.tree_sortkey and tree_right(t.tree_sortkey) and
+-- tree_sortkey <> t.tree_sortkey
+
+-- A directly executed query like this
+
+-- tree_sortkey between t.tree_sortkey || chr(0) and tree_right(t.tree_sortkey)
+
+-- works but the trailing chr(0) disappears if you put the concat in a 
+-- PL/pgSQL function.  In fact the inline expression might well be considered a
+-- bug and fixed by the PG team in the future so we shouldn't depend on it.
+
 create function tree_right(varchar) returns varchar as '
 declare
   p_tree_sortkey alias for $1;