Re: transaction blocking inserts in postgresql 7.3

Please note that if you have 2 parallel imports which insert records
locking referenced rows in reverse order, deadlocks can occur.
So you have to make sure that the imported records are sorted by
referenced foreign keys.

Cheers,
Csaba.

On Wed, 2003-03-26 at 23:46, Dennis Gearon wrote:
> Thanks for the input. I can see that you are going to have to have your web
> script split up the data.
>
> How about a buffer table that a chron job, every minute or so, pumps a number of
> records out of equal to 5% of what's in the buffer or some maximum value
> determined by experiment?
>
> It loads the CSV stuff lickety quick, doesn't lock up your other tasks, (for
> more than 5 seconds if it were my design).
>
> Chris Hutchinson 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
>