OK, I'm not entirely sure this table is not still locking something
else. If you make a copy by doing something like:
select * into test_table from a;
and then alter test_table do you still get the same problems? If so,
then it is an IO issue, most likely. If not, then there is some
client connection still referencing this table or something and that
could cause this type of behaviour as well.
I can guarantee you that the table is not being referenced by any other thread, table or process, and that it is totally unrelated to everything else in the system.
Its just a plain table, with 1.8 million records, that no thread knows it exists. It has no foreign keys that would allow thinking of a possible "lock" on the parent table, nor it is being referenced by any other table in the model. It has no triggers associated, and no indexes. It could very well even be on another database on the same physical server, and still do the same damage. I did not try this, but I'm pretty sure of the outcome. I would'nt like to bring the server down just to prove this, but I will do it if I find it necessary.
The only things that are common to this table and other tables in the system, as I see are:
RAM, IO, and CPU, at a very low level. One of these is being stressed out by the thread executing the ALTER, and the other threads (not just pgsql application threads, but system processes in general) suffer from the lack of this resource. All the previous discussions tend to induce that the resource we are talking about is IO.
The fact that the Task Manager does not come up, would also not be explained by a lock in a client thread.
Besides all that, all the client queries are NO WAIT, thus any lock would just return immediately, and no retry would be done until the response gets back to the user and the user confirms it. In that case, all the errors presented to the final users would be "The element is being processed some other place", as my default handler to pgsql error code "55P03", instead of the horrible "Operation timed out", that is what final users got during the huge slowdown/downtime.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
