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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
