Other queries locked out during long insert

From: "Joshua J(dot) Kugler" <joshua(at)eeinternet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Other queries locked out during long insert
Date: 2008-12-17 10:18:04
Message-ID: 200812170118.04997.joshua@eeinternet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2008-12-17 10:31:06 Re: A bit confused about Postgres Plus
Previous Message Grzegorz Jaśkiewicz 2008-12-17 10:10:35 Re: Maximum reasonable free space map