Thread: Weird lock or bug maybe?
Hi All, I got 2 very simple tables, one hold few values and got a unique field to be used as identifier and the other table got a reference to that table (foreign key). At my last testing before the problem I inserted to each one of the tables about 1 million rows, for the process of inserting new information I begin a transaction, delete all the previous information, insert the new information and commit the transaction. So far it never gave me any problems (though its the first time that I let a foreign key to exist) but now from some reason it "locks" a specific row from being deleted.... for example: If ill query: DELETE FROM table1; it will just get stuck... If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in most cases but for some rows it will just get stuck! Anyone know anything about this weird problem? By the way when I restarted the DB server I was able to delete the current row which stucked the process but then I got stuck at some other row at the table.... Thanks in advance, Ben-Nes Yonatan
Hi there I have much problems with wierd locking too. I thing that it problem is connected with localisation - when i update a "problem" row (which have text fields) with any different text data in text fields - all locks dissapper. I hope this info can help to solve this bug (because in PG 7.2.3 i didnt saw this problems//) -- Thanks BNY> Hi All, BNY> I got 2 very simple tables, one hold few values and got a unique field BNY> to be used as identifier and the other table got a reference to that BNY> table (foreign key). BNY> At my last testing before the problem I inserted to each one of the BNY> tables about 1 million rows, for the process of inserting new BNY> information I begin a transaction, delete all the previous information, BNY> insert the new information and commit the transaction. BNY> So far it never gave me any problems (though its the first time that I BNY> let a foreign key to exist) but now from some reason it "locks" a BNY> specific row from being deleted.... for example: BNY> If ill query: DELETE FROM table1; it will just get stuck... BNY> If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in BNY> most cases but for some rows it will just get stuck! BNY> Anyone know anything about this weird problem? BNY> By the way when I restarted the DB server I was able to delete the BNY> current row which stucked the process but then I got stuck at some other BNY> row at the table.... BNY> Thanks in advance, BNY> Ben-Nes Yonatan BNY> ---------------------------(end of broadcast)--------------------------- BNY> TIP 9: In versions below 8.0, the planner will ignore your desire to BNY> choose an index scan if your joining column's datatypes do not BNY> match -- С уважением, Игорь mailto:go@ugnn.ru
Ben-Nes Yonatan wrote: > If ill query: DELETE FROM table1; it will just get stuck... > If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in > most cases but for some rows it will just get stuck! > Anyone know anything about this weird problem? > By the way when I restarted the DB server I was able to delete the > current row which stucked the process but then I got stuck at some other > row at the table.... What do you mean by "get stuck"? Are you sure it's not scanning one of the tables to check references before deleting? If you don't have an index on the table in question then PG will have to scan the entire table. To check for locks, try: SELECT * FROM pg_stat_activity; SELECT * FROM pg_locks; Let's see what's actually happening. -- Richard Huxton Archonet Ltd
go wrote: > Hi there > > I have much problems with wierd locking too. I thing > that it problem is connected with localisation - when > i update a "problem" row (which have text fields) with > any different text data in text fields - all locks > dissapper. I hope this info can help to solve this bug > (because in PG 7.2.3 i didnt saw this problems//) Doesn't sound likely. Are you seeing this problem with PostgreSQL, or perhaps with Access talking to PostgreSQL over ODBC. If you can provide a simple, repeatable example then I'm sure the developers will be interested. -- Richard Huxton Archonet Ltd
Well what I mean at getting stuck is that some rows can be deleted fast when you delete them with their specific id while there are rows which when I tried to delete them it just didnt respond (seems like working endlessly so i stop it after an hour or so). Interesting that this morning I was able to delete all of the data in a reasonable time... dunno whats the diffrence... the only diffrence that I can think of is that I deleted the content of table2 and that from some reason cleared the "locks" on this talbe though I remember testing it afterwards and it didnt help. Maybe its connected to the fact that I connected both of the tables with a foreign key? I also got second thoughts about using foreign keys between my tables at the DB that I currently build... I always use foreign keys when I can but I noticed at the DB which im working on now (will contain millions of rows) that its making the process of deleting the content way too slow and I need to do it each day.... am I correct with what im doing? Thanks again, Yonatan Richard Huxton wrote: > Ben-Nes Yonatan wrote: > >> If ill query: DELETE FROM table1; it will just get stuck... >> If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in >> most cases but for some rows it will just get stuck! >> Anyone know anything about this weird problem? >> By the way when I restarted the DB server I was able to delete the >> current row which stucked the process but then I got stuck at some >> other row at the table.... > > > What do you mean by "get stuck"? > Are you sure it's not scanning one of the tables to check references > before deleting? If you don't have an index on the table in question > then PG will have to scan the entire table. > > To check for locks, try: > SELECT * FROM pg_stat_activity; > SELECT * FROM pg_locks; > > Let's see what's actually happening.