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