Forum OpenACS Q&A: update returns 1, but no changes have been made

1: update returns 1, but no changes have been made

Posted by James Thornton on 12/03/01 12:58 AM

I have been trying to update a table -- the update statement returns 1
indicating that one row has been updated, but no changes were made.

Could I have a deadlock? Something else?

2: Response to update returns 1, but no changes have been made (response to 1)

Posted by Jerry Asher on 12/03/01 01:16 AM

Hi James,

Would you describe your configuration? Can you repeat this with a simple test?

I wonder if your problem is related to this one Bizarre Postgres Behavior that Carl Coryell-Martin reported here three weeks ago ("the WHERE clause returns two rows from a SELECT but deletes zero rows".)

(It's of course always helpful to report a good description of your environment, that is, OS, version of PG, and anything else (which direction you waved the chickens) that might seem relevant.)

3: Response to update returns 1, but no changes have been made (response to 1)

Posted by James Thornton on 12/03/01 03:21 AM

Hi Jerry -

This is happening on two servers:

my development server, running Postgres 7.0.3, RedHat 7.1
soon-to-be production server, hosted at Thinking-Minds.com, running Postgres 7.1.3, RedHat 6.2

The datamodel: http://jamesthornton.com/misc/wpp-product.sql.txt

insert statements...

set db [ns_db gethandle]

set sql_query "select product_type_id from wpp_product_type 
where product_type = '$product_type'"

set selection [ns_db 1row $db $sql_query]
set product_type_id [ns_set value $selection 0]

ns_db dml $db "begin"
ns_db dml $db "lock table wpp_product_question in exclusive mode"

set selection [ns_db 1row $db "select max(product_question_id)+1 
from wpp_product_question"]

set question_id [ns_set value $selection 0]
if [empty_string_p $question_id] {
        set question_id 1
}

set selection [ns_db 1row $db "select max(sort_order)+1 
from wpp_product_question 
where product_type_id = $product_type_id"]

set sort_order [ns_set value $selection 0]
if [empty_string_p $sort_order] {
        set sort_order 1
}

ns_db dml $db "insert into wpp_product_question (product_question_id,
product_type_id, product_question, sort_order, last_modified,
last_modifying_user, modified_ip_address)
    values ('$question_id', '$product_type_id', 
'[DoubleApos question]', '$sort_order',sysdate(), '$user_id', 
'[DoubleApos [ns_conn peeraddr]]')"

ns_db dml $db "end"

update statement...

set db [ns_db gethandle]

set sql_update "update wpp_product_question 
set product_question = '[DoubleApos $question]', 
editor_notes = '[DoubleApos $q_notes]', approved_p = '$approved_p',
last_modified = sysdate(), last_modifying_user = '$user_id',
modified_ip_address = '[DoubleApos [ns_conn peeraddr]]'
where product_question_id = '$q_id'"

ns_db dml $db "begin"
ns_db dml $db "$sql_update"
ns_db dml $db "end"

Here are some possibly relevant usenet postings:

4: Response to update returns 1, but no changes have been made (response to 1)

Posted by James Thornton on 12/06/01 10:43 PM

I found the problem -- it was with a trigger that that was intercepting the UPDATE so the changes were not actually getting to the database.

This was my first time writing triggers in Postgres so I wasn't sure as to how to do it (see previous post ERROR: control reaches end of trigger procedure without RETURN).

Because I was returning old from the UPDATE, no changes were being made (it takes the old values and puts them into the database rather than your new values).

So making a conditional fixes the problem (only returning old for deletes):

IF TG_OP=''UPDATE'' THEN
  RETURN NEW;
END IF;

RETURN OLD;