Thread: 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.
Attachment
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.