Forum OpenACS Q&A: Re: Mysterious Crash, losing connection with Postgres

Both crashes are caused when restoring the db.  Not from a fresh install.  The tsearch2 problem can be easily resolved by droping the tsearch2 data model and restoring.  Use untsearch2.sql (remove the transaction block) and run tsearch2.sql again on pg contrib dir.

The other one happens on forums, well custom forums.  Has anyone exprienced this?

Here is the query, query above is the orig one.  Query below is a rewrite.

ctk=# explain    select f1.message_id as ancestor_message_id
    from forums_messages f1
    where f1.tree_sortkey in (select tree_ancestor_keys(f2.tree_sortkey)
                              from forums_messages f2
                              where f2.message_id = 1268350)
    and forum_id = 1204187;
ctk-# ctk-# ctk(# ctk(# ctk-#                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=5.69..10.98 rows=1 width=4)
  ->  Unique  (cost=5.69..5.69 rows=1 width=32)
        ->  Sort  (cost=5.69..5.69 rows=1 width=32)
              Sort Key: "IN_subquery".tree_ancestor_keys
              ->  Subquery Scan "IN_subquery"  (cost=0.00..5.68 rows=1 width=32)
                    ->  Index Scan using forums_messages_pk on forums_messages f2  (cost=0.00..5.67 rows=1 width=16)
                          Index Cond: (message_id = 1268350)
  ->  Index Scan using forums_mess_forum_sk_un on forums_messages f1  (cost=0.00..5.27 rows=1 width=20)
        Index Cond: ((f1.forum_id = 1204187) AND (f1.tree_sortkey = "outer".tree_ancestor_keys))
(9 rows)

ctk=# explain    select f1.message_id as ancestor_message_id
    from forums_messages f1,
        (select tree_ancestor_keys(tree_sortkey) as tree_sortkey from forums_messages where message_id = 1268350) parents
    where f1.tree_sortkey = parents.tree_sortkey
        and forum_id = 1204187;
ctk-# ctk-# ctk-# ctk-#                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------
Nested Loop  (cost=0.00..10.96 rows=1 width=4)
  ->  Subquery Scan parents  (cost=0.00..5.68 rows=1 width=32)
        ->  Index Scan using forums_messages_pk on forums_messages  (cost=0.00..5.67 rows=1 width=16)
              Index Cond: (message_id = 1268350)
  ->  Index Scan using forums_mess_forum_sk_un on forums_messages f1  (cost=0.00..5.27 rows=1 width=20)
        Index Cond: ((f1.forum_id = 1204187) AND (f1.tree_sortkey = "outer".tree_sortkey))
(6 rows)

BTW I excuted both queries on a freshly installed system.  Executing the first one on a restored system, gets you booted off psql.  Is the query rewrite ok?

How can tree_ancestor_keys placed on a query causes the pg server to disconnect?  Placing the tree_ancestor_keys outside the "in" does not boot you off.  I still can't understand the exact cause of it.