transaction block with "insert_lock" on postgresql

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
The Public Knowledge Project Support Forum is moving to

This forum will be maintained permanently as an archived historical resource, but all new questions should be added to the new forum. Questions will no longer be monitored on this old forum after March 30, 2015.
Posts: 4
Joined: Wed Jun 27, 2012 8:07 am

transaction block with "insert_lock" on postgresql

Postby digilio » Wed Jun 27, 2012 8:59 am

we tried to migrate the DB of our OJS istallation (version 2.3.3-3) to a cluster solution, configured as follows :

connection pooling + replication active with pgpoolII 3.1
db postgresql 8.4.9
"insert lock" actived

after migration we have a problem, in fact, after an article submission, when we try to display journal author index page we obtain this error :

DB Error: ERROR: current transaction is aborted, commands ignored until end of transaction block

looking on internet we found that the reason is the insert lock enabled, in fact If insert_lock is enabled, the regression tests for PostgreSQL 8.0 will fail in transactions, privileges, rules, and alter_table. The reason for this is that pgpool-II tries to LOCK the VIEW for the rule test, and will produce the above error message.

has anyone ever had this problem with "insert_lock"?? any suggestions for this??

this is the query that produce the error :

Code: Select all

(postgres7): SELECT a.*, COALESCE(atl.setting_value, atpl.setting_value) AS submission_title, aa.last_name AS author_name, (SELECT SUM(g.views) FROM article_galleys g WHERE (g.article_id = a.article_id AND g.locale = ?)) AS galley_views, COALESCE(stl.setting_value, stpl.setting_value) AS section_title, COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev FROM articles a LEFT JOIN authors aa ON (aa.submission_id = a.article_id AND aa.primary_contact = 1) LEFT JOIN article_settings atpl ON (atpl.article_id = a.article_id AND atpl.setting_name = ? AND atpl.locale = a.locale) LEFT JOIN article_settings atl ON (atl.article_id = a.article_id AND atl.setting_name = ? AND atl.locale = ?) LEFT JOIN sections s ON (s.section_id = a.section_id) LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = ? AND stpl.locale = ?) LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = ? AND stl.locale = ?) LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = ? AND sapl.locale = ?) LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = ? AND sal.locale = ?) WHERE a.user_id = ? AND a.journal_id = ? AND a.status = 1   
-1: ERROR: current transaction is aborted, commands ignored until end of transaction block

Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm

Re: transaction block with "insert_lock" on postgresql

Postby asmecher » Wed Jun 27, 2012 10:05 am

Hi digilio,

We're using ADODB as our database abstraction layer, meaning that we don't deal specifically with PostgreSQL quirks except in a few of the upgrade scripts. We're also not using transactions as part of OJS. I suspect that you may have a lock that occurred elsewhere, i.e. during the migration process, but unfortunately beyond that I don't have any suggestions to offer.

Alec Smecher
Public Knowledge Project Team

Return to “OJS Technical Support”

Who is online

Users browsing this forum: Yahoo [Bot] and 2 guests