Forum OpenACS Development: Re: Res: Duplicate keys on assessment - possible CR bug

Posted by Gustaf Neumann on
Rocael, the thread deals with two different bugs: (a) duplicate key issue due to the pg rules semantics, and (b) the deadlock topic with share locks for the foreign keys? Is there some documentation about the "bug" and the "fix" in postgres?
is related to the deadlock, so no longer needed the explicit lock in 8.2.x series.
i just did some tests on xowiki and xotcl-core, which has its own db-queries. The test creates 20 threads, each of these creates 100 content items with 10 revisions each in an eager fashion. So every thread creates 1100 acs_objects, altogether 22000. While earlier versions created deadlocks, 8.2.0 does not.

This are great news, but this does not simplify release management. Interestingly enough, since OpenACS 5.3 seems to work under Oracle, i deduce that

db_dml lock_objects "LOCK TABLE acs_objects IN SHARE ROW EXCLUSIVE MODE"

works under oracle as well. I would think, that in oracle, this locking is not needed at all, and in the postgres case, we should only do this test if the following returns 1:

select 1 where substring(version() from 'PostgreSQL #"[0-9]+.[0-9+]#".%' for '#') < 8.2;

I would hate to do the version check on every insert operation. not sure, what the best approach for acs-content-repository is. On the xotcl-side, the newest version of xotcl-core creates now depending on postgres+version conditionally a locking rule; so there is no runtime penalty for version checking.

I would do the following:

a) Require PG 8.2 or higher for OpenACS 5.4
b) For 5.3 keep the lock table statements in, or, alternatively, provide a parameter in acs-content-repository "OldPGP" which is turned on if upon installation (or upgrade) you find that the system is running on an Old DB version. Or store it in an nsv_array and check upon starting the AOLserver.

Interestingly I got problems with "lock table acs-objects in share row exclusive mode" on a farily busy site running against PG 8.2.3.

my tests just showed that some deadlock situations disappeared (it tests mostly busy content revision creation). As for every test, it does not prove that there are some more deadlock situations, which are not tested.

So, if someone experiences more deadlocks, a simple test case would be certainly helpful to nail this down.

oacs 5.4 can rollback the explicit lock, although the only ones that were experimenting the deadlock are here, and will be probably aware that pg 8.2.x upgrade is recommended, but still works oacs in 8.x versions.

We did test and everything is fine on this regard, now we have in production that.

I´m sure oracle did not have this silly deadlock.