Forum OpenACS Q&A: General Comments Error - Help Please...

Ok I'm stumped...I'm using 4.6.2/pg 7.3.2. Have lars-blogger installed and everything works fine except for "Add Comment". Once the comment is entered on the form generated from "comment-add", I go just fine to comment-add-2. On confirm I get the following error:

Request Error
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {
	     select acs_message__new (
		:comment_id,		-- 1  p_message_id
		NULL, 			-- 2  p_reply_to
		current_timestamp,	-- 3  p..."
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"
    invoked from within
"db_exec 0or1row $db $full_statement_name $sql"
    invoked from within
"if {[regexp -nocase -- {^\s*select} $test_sql match]} {
            ns_log Debug "PLPGSQL: bypassed anon function"
            set selection [db_exec ..."

I've tried several things including searching the forums for about 45 minutes and can't find anything related to this. All dependencies are installed etc. Does anyone have any ideas? I had this working in 4.6.1. TIA for your help.

Hi Caroline - APM says - 0.8.5 for lars-blogger, 4.0 for general-comments and 4.5 for acs-messaging.

Maybe so. I'm using the same version #'s of general comments, lars-blogger and acs-message on postgres 7.2.3 and it works.

What is the cvs version on acs-messaging/sql/postgresql/acs-messaging-packages.sql

http://cvs.openacs.org/cvs/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql

I'm running on 1.10.4.2

But maybe you should be on 1.11. The log message says:

"Patches to make OpenACS PG 7.3 compatible"

Caroline - The 1.11 file mostly changes "timestamptz" to "timestamp with with time zone".

The changes seem to have been made after Don's changes. (???) After looking at it, I'm a bit confused since it seems that the changes may be contrary to Don's notes:
See here

Notes from the repository:

"I've changed "timestamp" to "timestamptz" and have begun fixing some code that used to be legal but as of PG 7.3 is not. You can no longer subtract an integer from a timestamp but must cast to date, "timestamp" disappeared (must use "interval" now), "datetime" disappeared ("timestamptz"), and suchwhat. Not done but closer than it was before ..."

And from another post:

"This is the problem. They are now stored WITHOUT timezone information in PG 7.3. This change is not transparent to the code. In order for us to support both PG 7.2 and PG 7.3 - not to mention dump-restore upgrades from 7.2 to 7.3 - we need to change all "timestamp" decls in the toolkit to "timestamptz". Grrr..."

That's right - Grrrrrrrrrrr!!!!!!!! Based on this then the change to timestamptz from timestamp is correct. So what's up with "timestamptz" to "timestamptz"? The PG 7.3 docs say that "timestamptz" is a valid data type though. So - I need to read up some more on 7.2 -> 7.3 changes and then take another look at the code tomorrow. Perhaps Don or someone else can chime in here with a clarification. Tomorrow I'll also experiment with the data model and give "timestamptz" a try and see what happens. I'm working on a project where date manipulations are core critical - lots of date calcs etc. - so I need to get this stuff clear asap. Thanks for the assist! 😊)

I've used "timestamptz" instead of "timestamp with timezone" because the PL/pgSQL parser doesn't seem to accept "timestamp with timezone" in all cases.  At least I couldn't figure it out and being in a hurry settled on "timestamptz" since that was a change I could script that worked everywhere.

In this context it shouldn't matter though (did someone change it back from "timestamptz" to "timestamp with timezone" after I changed it???  If so, why???)

Came across this thread while searching for something else. I just ran into this same issue a week or so ago when upgrading a site. I uploaded a patch a few days ago which changes the parameter types for acs_message__edit and solved the problem for me. I also fixed a version problem with the last acs-messaging upgrade script. The patch is at:

https://openacs.org/bugtracker/openacs/patch?patch_number=204

I know Dave's removing GC's acs-messaging dependencies soon but it'd be great if someone could take a look at this for 4.6.4.