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
