We need to wait for the postgres upgrade, so a temporary solution had to be made. All the foreign key constraints were dropped and added again with INITIALLY DEFERRED flag. Everything seems to be working fine now. If anyone is interested I can provide files with sql queries I used.
Pawel Pawel Sztromwasser wrote: > Thank you very much. > I thought about altering database schema somehow to set the constraints > 'initially deferred' and check if that works (in small examples it > worked fine), but if a new version makes no trouble it doesn't make > sense any more. Thanks once again for your help. > > Pawel Sztromwasser > > > Nicklas Nordborg wrote: > >> Nicklas Nordborg wrote: >> >> >>> I am taking this back to the mailing list in the hope that there is some >>> Postgres expert that can shed some light on this. >>> >>> I found this http://www.thescripts.com/forum/thread174537.html >>> >>> It is about a rather old Postgres version (7.4). I am not sure if the >>> same problem is still around. The problem is that when inserting data >>> Postgres is locking all rows that are referenced by foreign keys. This >>> means that if your five imports, for example, inserts data with >>> references to the same reporter or feature on an array design, only one >>> can proceed at a time. >>> >>> Actually, if this problem is still around in Postgres 8.2 I think this >>> makes Postgres more or less unusable in a true multi-user setup. Since >>> almost any data import or analysis plug-in will insert data and in many >>> cases the new data will reference a reporter, an experiment or some >>> other common item there is a high probability that only one job is able >>> to proceed at a time. >>> >>> There is a tip about a "DEFERRED" mode that can overcome some >>> limitations, but I am not very familiar with Postgres and don't really >>> know how to set it. It would be good if there is a global server >>> configuration option for this. >>> >>> Do you have the possibility to investigate this further? >>> >>> >> I did some investigation on my own. I can verify that the locking >> problem exists on Postgres 8.0.4. I tested this with only two jobs using >> the internal job queue and only one could work at a time. >> >> After installing Postgres 8.2.5 (which is the latest version) the >> locking problem disappeared. Checking the documentation on their website >> it seems like a new shared row-level lock mode has been introduced some >> time ago. This means that Postgres no longer has to use a write-lock >> which can only be held by one transaction at a time. >> >> So, unless some new information about this issue comes up I will not >> investigate this any more. Postgres users are encouraged to upgrade to >> Postgres 8.2.5. >> >> /Nicklas >> >> ------------------------------------------------------------------------- >> This SF.net email is sponsored by: Splunk Inc. >> Still grepping through log files to find problems? Stop. >> Now Search log events and configuration files using AJAX and a browser. >> Download your FREE copy of Splunk now >> http://get.splunk.com/ >> _______________________________________________ >> basedb-devel mailing list >> basedb-devel@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/basedb-devel >> >> > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > basedb-devel mailing list > basedb-devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/basedb-devel > ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ basedb-devel mailing list basedb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/basedb-devel
