Search Postgresql Archives

Other queries locked out during long insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux