You are viewing the PKP Support Forum | PKP Home Wiki

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
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

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: 4
Joined: Wed Jun 27, 2012 8:07 am

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
Posts: 9214
Joined: Wed Aug 10, 2005 12:56 pm

Return to OJS Technical Support

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 4 guests