I am not able to drop old partition tables in a postgresql 8.4.4 live db. My drop statement hangs with a access share lock on those partition tables. Is there any way I can drop those tables?
A:
Have you tried this first?
ALTER TABLE ONLY <child_table> NO INHERIT <parent_table>
Matthew Wood
2010-09-27 14:31:35
yes, I did. But it's hangs as well.
APV
2010-09-27 14:55:53
If I restart the db I am able to drop the old partitions for few minutes then as soon as the connections is established again it hangs. I don't want to do it often as it's a live system. Any suggestions please?
APV
2010-09-28 09:14:03
It sounds like you have connections with open transactions running against these tables. If that's the case, there's not much you can do unless you can change the application or connection manager to run in AutoCommit mode unless told otherwise. The transactions will block the drops. You can verify this by checking pg_stat_activity in another session after issing the DROP command. If the record with the drop has "t" in the "waiting" field, then check to see if any other connections are "idle in transaction". If so, that's your problem.
Matthew Wood
2010-09-28 15:02:17