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