Forum OpenACS Development: Trying to fix problem with Last Post date in forums

Collapse
1: Trying to fix problem with Last Post date in forums
Posted by Janine Ohmer on 09/08/03 10:12 PM
Sometimes I think the computers are playing tricks on me...

When you look at the forum view, the date shown under Last Post is not the date of the last post, but the date of the first post. I thought this would be easy to fix.  Unfortunately, it turned out to be a head-scratcher instead.

This code looks like it should do the trick (from forums_message.new):

        update forums_messages
        set last_child_post = posting_date
        where forum_id = forums_message.new.forum_id
          and tree_sortkey = tree.ancestor_key(v_sortkey, 1);

However, it does not update the value of last_child_post.  I've checked the values of all the variables and they appear correct;  I've also checked the table for any row with the date set to today's date, and did not find any.  I've even added select statements before and after this update to check the value of last_child_post in the row this should be updating, and it does not change after the update.

What the new function does is basically this:
- the trigger forums_mess_insert_tr fires, which updates the  sortkey
- insert a new row into the forums_messages table;  each row contains both forum_id and message_id
- update the forums_forums table with the last post date (this works)
- update the top level row in the tree for this forum_id with the last post date

I don't claim to fully understand how the sortkey stuff works, but clearly it is working since messages are ending up in the right place.

The only way I can imagine that this update just vanishing into thin air is some kind of problem with transactions, but I don't see what that could be. Even with the trigger firing, nothing here looks wrong to me.  What am I overlooking???

Collapse
2: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Dave Bauer on 09/08/03 10:18 PM
Janine,

The lastest forums code includes a last_child_post field. OpenACS.org is using this in the message display to show the last reply and to sort the messages by the last reply, so the most recently replied to messages show up on top of the list.

Collapse
3: Re: Trying to fix problem with Last Post date in forums (response to 2)
Posted by Janine Ohmer on 09/08/03 10:26 PM
Hi Dave,

Did you have to do anything to make that work?  This code does have the last_child_post field; that's the field that's not getting updated.  On several sites, fwiw - maybe it's me. :)

Collapse
4: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Janine Ohmer on 09/09/03 08:19 PM
I just looked at the source dir for openacs.org site but I'm a bit perplexed:

[janines@samoyed packages]$ find . -type f -exec fgrep -l last_child_post {} \;
./forums/www/forum-view-postgresql.xql
./forums/www/forum-view.adp

Where is the code that sets the value of last_child_post?  Both of these files just query it.

Collapse
5: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Dave Bauer on 09/09/03 08:42 PM
Hmmm, I hope its a trigger on forums_messages. I can check. I was just prodividing proof that it works in at least one installation :)
Collapse
6: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Janine Ohmer on 09/09/03 08:52 PM
More information:

I simplified the code to this:

        select forums_message.root_message_id(v_message_id) into v_root_message_id from dual;

        update forums_messages
        set last_child_post = posting_date
        where message_id = v_root_message_id;

I used a raise_application_error call to verify that the v_root_message_id was correct.

I then selected the current value of last_child_post from forums_messages before the update, and verified that it was 09-09-2003 11:46:03 (time of the first post).

I then did the same thing *after* the update - got the same value back (time of the first post).  It's as though that update is not taking place - but I have no idea why???

Suggestions appreciated.  I have looked into this several times now and have been baffled each time.

PS Thanks, Dave, any help would be appreciated.

Collapse
7: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Tom Jackson on 09/09/03 09:50 PM

Can you run this from sqlplus/psql, updating the table directly? Maybe a trigger procedure is not doing the right thing?

Collapse
8: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Janine Ohmer on 09/10/03 06:23 PM
Hi Tom,

Yes, I can update it from sqlplus and it works.  But, when I post another reply to the thread, the value reverts back to the date of the first post.  This is very odd!  I have verified that the value of posting_date is correct before the update; that is, it's set to sysdate.

I just went through forums_messages.new(), adding in raise statements after each code block to check the value of last_child_post.  And sure enough, this

        update forums_messages
        set last_child_post = posting_date
        where message_id = v_root_message_id;

is where it reverts.  There's nothing wrong with the data:

J9c: v_root_message_id = 51542, last_child_post = 09-10-2003 10:53:36, posting_date = 09-10-2003 11:39:23

It's using the right message_id, and the posting_date is set to sysdate.  But the value of last_child_post has reverted back to the date of the first post (this data is from after the update).

Since it seems at this point like it must be trigger related, I tried dropping forums_mess_insert_tr, the only trigger in forums, and sure enough the problem went away:

J9c: v_root_message_id = , last_child_post = 09-10-2003 11:22:14, posting_date = 09-10-2003 11:57:22

This time, the value of last_child_post is still what I had set it to for testing purposes.

But this is very bizarre!  The code being executed in that trigger is

    else

        select nvl(tree_sortkey, ''), max_child_sortkey
        into v_parent_sortkey, v_max_child_sortkey
        from forums_messages
        where message_id = :new.parent_id
        for update of max_child_sortkey;

        v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);

        update forums_messages
        set max_child_sortkey = v_max_child_sortkey
        where message_id = :new.parent_id;

    end if;

    :new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;

Where :new.parent_id is set to the message_id of the message being replied to (the most recent in the thread).  There is nothing here that should affect the value of last_child_post!  Especially since the parent message is not the first message in the thread, which is where last_child_post is set.

I should also mention that I have another site that is exhibiting the same problem, so it's not just this one.  They are both Oracle sites, though that "shouldn't" matter.

Does this ring any bells for anyone?

Collapse
9: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Raad Al-Rawi on 09/12/03 05:22 PM
Janine

I came across the "last post date" issue with the instance of dotLRN/SloanSpace we started running back in January.
I fixed it by changing the "messages_select" query in "default-view-oracle" to pick up the latest post to the thread. The fix is not as proper as the real solution, and there is undoubtedly a small performance hit; but if you are struggling and getting nowhere you could use the query for display purposes until you find the cause. I've posted the whole query, including the bit for last_modified.

Of course it might not fit in with how things currently stand in forums, but it was just a thought 😊

<Raad>

select fm.message_id,
fm.subject,
fm.user_id,
person.name(fm.user_id) as user_name,
fm.posting_date,
fm.state,
(
select count(*)
from forums_messages_approved fm1
where fm1.forum_id = :forum_id
and fm1.tree_sortkey between tree.left(fm.tree_sortkey)
and
tree.right(fm.tree_sortkey)
) as n_messages,
nvl((
select to_char(ao2.last_modified, 'Mon DD YYYY HH24:MI:SS')
from acs_objects ao2,
forums_messages_approved fm2
where ao2.object_id = fm2.message_id
and fm2.forum_id = fm.forum_id
and fm2.tree_sortkey =
(
select max(fm3.tree_sortkey)
from forums_messages_approved fm3
where fm3.forum_id = fm2.forum_id
and fm.parent_id is null
and fm3.tree_sortkey between tree.left(fm.tree_sortkey)
and
tree.right(fm.tree_sortkey)
)
), to_char(ao.last_modified, 'Mon DD YYYY HH24:MI:SS')) as last_modified,
case when ao.last_modified > (sysdate - 1) then 't' else 'f' end as new_p
from forums_messages_approved fm,
acs_objects ao
where fm.forum_id = :forum_id
and fm.parent_id is null
and fm.message_id = ao.object_id
order by fm.posting_date desc
Collapse
10: Re: Trying to fix problem with Last Post date in forums (response to 1)
Posted by Janine Ohmer on 09/12/03 10:28 PM
Thanks, Raad.  You're right, I could do it that way, but it *bugs* me that I can't figure this out!

Hasn't anyone else looked into this issue, or run into similar puzzling behavior?  There has to be a logical answer, I'm just not seeing it.