Forum OpenACS Development: Re: db_transaction hanging a thread how to detect, how to kill...

Since the pg version of acs_permission__grant_permission, unlike the oracle version, is not able to detect duplicate inserts into acs_permissions, it uses a crude work-around that involves a table lock.  To get around this you would need to manually perform the table locking and inserting into acs_permissions from tcl.  See acs_permission__grant_permission for details.

Also, now that I think about it, I seem to recall that this work-around no longer works correctly in newer versions of pg (newer being > 7.1 maybe).  In practice, this hasn't been a problem since permissions are generally not manipulated much outside of the package installation phase.

Jade is correct in saying that you shouldn't mix db_foreach and db_dml in a transaction, since the two statements will use separate db handles, and you won't get the intended results. But, even if you make this change, you will still have the hanging problem, since the first permission grant statement outside the db_foreach loop will still be holding the lock until the transaction closes.