Forum OpenACS Development: content revision new problem

Collapse
Posted by Malte Sussdorff on
Here is the code from content::revision::new

db_transaction {
if {[string equal "" $revision_id]} {
set revision_id [db_nextval "acs_object_id_seq"]
}
db_dml insert_revision $query_text
update_content \
-revision_id $revision_id \
-content $content
}

If we execute the code without a revision_id at exactly the same time, which value will the db_nextval have? My assumption is:

Exactly the same.

Why? The increase in the value of the acs_object_id_seq will not be executed for other transactions until the item has been generated. But in the meantime another transaction accesses the acs_object_id_seq and retrieve the same value and try to insert it which results in foreign key constraints.

Collapse
Posted by Nis Jørgensen on
The documented behavior of "next_val ('sequence')" - which is what db_nextval calls - is that is never returns the same value twice, not even across transactions.

If you can reproduce something different (an ns_log call in db_nextval should do) I belie ve you have a bug report for thePG team.

/Nis

Collapse
Posted by Malte Sussdorff on
Not entirely sure how I would do this, as I need to run two seperate queries at the same time each with the transaction. I can only say that two session_ids tried to insert the same object_id in acs-objects, though this is probably not what the PG team would like to hear. Any other idea how I could show this ( with minimal effort :-)).

Byte the way, it is considerably better to provide a object/revision/item_id to the ::new functions anyway instead of relying on them to figure it out (at least in my opinion).

Collapse
Posted by Alex Kroman on
I have been able to reproduce this problem by doing this:

http://openacs.org/bugtracker/openacs/bug?bug%5fnumber=2992

Collapse
Posted by Alex Kroman on
Looks like the patch to fix this is located here:

http://cvs.openacs.org/cvs/openacs-4/packages/acs-content-repository/tcl/content-revision-procs.tcl?r1=1.14&r2=1.15

It is PostgreSQL 8 only.

Does anyone know of a way to fix this problem that would work in PostgreSQL 7?

Collapse
Posted by Jun Yamog on
Hi Alex,

Locking the table in share row exclusive mode seems to be documented on pg 7.3. You may want to try and test if it works on postgres 7.

Jun

Collapse
Posted by Jun Yamog on
Alex,

Discussion of bug and fix is in this thread:

http://openacs.org/forums/message-view?message_id=461759