Re: Weird lock or bug maybe?

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.