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

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
>
>