Обсуждение: Other queries locked out during long insert

I've read the Pg docs about MVCC and possible locks that indexes can
create.  Even so, I can't figure out why my code is causing all other
queries to block while it is running.  I'm reading data in from a file
(line by line, its CSV), doing a little pre-processing, and inserting
it into a table.

I'm dropping the indexes for both speed and to eliminate sources of
possible locks.  Here is, effectively, what I'm doing:

Connect to database MYDATA

Begin
Drop a couple indexes and constraints
Commit

Begin
Truncate table1, table2

for row in file1:
    insert into table1
    sleep(0.001) # see note below

for row in file2:
    insert into table2
    sleep(0.001) # see note below

Commit

Begin
create index on table1
create constraint on table2
Commit

During the time where the insert loops are running, I cannot do any
queries against table1 and table2.  At first I thought it was a
performance thing, as without the sleeps postmaster was taking 60% of a
CPU (this is an eight core system).  After putting in the sleeps, I
still can't query against table1 and table2 without the query blocking.

Other information:
The query that is blocking is a stored procedure in MYDATA, which is
nothing but a couple selects.
The second insert loop uses a couple ip4r functions (as does the
aforementioned stored procedure).

My understanding of MVCC is that I should be able to query against those
tables while these insert loops are in their transaction.

Can someone show me (or point me to docs that show me) the error of my
thinking?

Thank you!

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/  ID 0xDB26D7CE

On Wed, Dec 17, 2008 at 01:18:04AM -0900, Joshua J. Kugler wrote:
> I've read the Pg docs about MVCC and possible locks that indexes can
> create.  Even so, I can't figure out why my code is causing all other
> queries to block while it is running.  I'm reading data in from a file
> (line by line, its CSV), doing a little pre-processing, and inserting
> it into a table.

Normal updates and inserts indeed use MVCC, but TRUNCATE does not. It
takes an exclusive lock. If you want to be able to continue doing
queries you need to do a DELETE nistead of TRUNCATE.

Another common trick is to load into another table and then rename it
into place.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Wednesday 17 December 2008 12:18:04 Joshua J. Kugler wrote:
> Begin
> Truncate table1, table2
>
> for row in file1:
>     insert into table1
>     sleep(0.001) # see note below
>
> for row in file2:
>     insert into table2
>     sleep(0.001) # see note below
>
> Commit

> During the time where the insert loops are running, I cannot do any
> queries against table1 and table2.

Because the TRUNCATE commands are taking out an exclusive lock on the tables.

> My understanding of MVCC is that I should be able to query against those
> tables while these insert loops are in their transaction.

You get the MVCC behavior if you use DELETE instead of TRUNCATE.  TRUNCATE is
specifically designed for better speed and less concurrency.  It's your
choice.

On Wednesday 17 December 2008, Peter Eisentraut said something like:
> Because the TRUNCATE commands are taking out an exclusive lock on the
> tables.
>
> > My understanding of MVCC is that I should be able to query against
> > those tables while these insert loops are in their transaction.
>
> You get the MVCC behavior if you use DELETE instead of TRUNCATE.
> TRUNCATE is specifically designed for better speed and less
> concurrency.  It's your choice.

OK, that makes sense.  There was nothing on the TRUNCATE page to suggest
that TRUNCATE would lock the tables.  Maybe an addition to the
documentation is in order?  Where do I go to suggest that?

Thanks again!

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/  ID 0xDB26D7CE

Joshua J. Kugler wrote:
> OK, that makes sense.  There was nothing on the TRUNCATE page to suggest
> that TRUNCATE would lock the tables.  Maybe an addition to the
> documentation is in order?  Where do I go to suggest that?

I have added something to document this.