Re: transaction blocking inserts in postgresql 7.3 - Mailing list pgsql-general

You could try converting your data into SQL insert
statements (shouldn't be too hard to write a script
that does that), and load that.  Your import will take
quite a bit longer because of the transaction overhead
required for each new record, but would solve your
blocking problem.

--- Chris Hutchinson <chris@hutchinsonsoftware.com>
wrote:
> 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


__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com