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
