Re: transaction blocking inserts in postgresql 7.3

Chris,

Perhaps it'd be faster for the users to upload their data to a dynamically created temporary table (keeping the main table available). 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(at)hutchinsonsoftware(dot)com]
> Sent: Wednesday, March 26, 2003 2:35 PM
> To: gearond(at)cvc(dot)net
> Cc: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Dennis Gearon
> Sent: Thursday, 27 March 2003 6:51 AM
> To: Chris Hutchinson
> Cc: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>