Re: transaction blocking inserts in postgresql 7.3 - Mailing list pgsql-general
From | David Olbersen |
---|---|
Subject | Re: transaction blocking inserts in postgresql 7.3 |
Date | |
Msg-id | E7E213858379814A9AE48CA6754F5ECB0D6C6F@mail01.stbernard.com Whole thread Raw |
In response to | transaction blocking inserts in postgresql 7.3 ("Chris Hutchinson" <chris@hutchinsonsoftware.com>) |
List | pgsql-general |
Chris, Perhaps it'd be faster for the users to upload their data to a dynamically created temporary table (keeping the main tableavailable). The web application should choose/create one. Once that upload is done, copy to the main table. I think this would be faster since the copy is within the database. Any ideas? -------------------------- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Chris Hutchinson [mailto:chris@hutchinsonsoftware.com] > Sent: Wednesday, March 26, 2003 2:35 PM > To: gearond@cvc.net > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] transaction blocking inserts in postgresql 7.3 > > > Dennis, > > I develop web-based genetic data management systems for agricultural > research. One facility of the system is importing large > CSV-format data > files into the database through a web interface. > > On a postgresql backend, while an import is running (which > can take several > minutes due to the quantity of data imported) users cannot > make changes to > other tables which share a common foreign key with the > species table. This > locks users out of numerous administrative operations, and is > something of a > pain. > > One example of the problem occurs when users open a form to > add a new trait > definition for a experimental study, and the form save never > returns. Their > browser times out because the form won't return until the > background import > job is complete. Essentially users see the system freeze, > with no clue as to > why. > > Possible workarounds I'm investigating are to split imports > into smaller > transactions to give other tasks a chance to run, but I > forsee issues when > multiple simultaneous imports are running. > > So in brief answer to your question, database operations wait > their turn, > but with a long transaction in a web environment this can > cause browser > timeouts and a problematic user experience. > > Regards, > Chris > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon > Sent: Thursday, 27 March 2003 6:51 AM > To: Chris Hutchinson > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] transaction blocking inserts in postgresql 7.3 > > > Actually, > I need a clarification of this since it might affect my > design. When you > mean > blocked, is an error thrown, or does it just wait its turn? > > Chris Hutchinson wrote: > > Dennis, > > > > > >>I'm really curious how you got these two transactions to occur > >>simultaneously, i.e. how does one DO the test that you DID? > > > > > > Open two terminal sessions, run a copy of psql in both. In > one run the > 'begin; insert...', in the other run 'insert'. The second is > blocked until a > commit (or rollback) is entered in the first. > > > > Regards, > > Chris > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >
pgsql-general by date: