Re: a heavy duty operation on an "unused" table kills my server

On Tue, Jan 12, 2010 at 9:59 PM, Eduardo Piombino <drakorg(at)gmail(dot)com> wrote:
...

> Now, with this experience, I tried a simple workaround.
> Created an empty version of "a" named "a_empty", identical in every sense.
> renamed "a" to "a_full", and "a_empty" to "a". This procedure costed me like
> 0 seconds of downtime, and everything kept working smoothly. Maybe a cpl of
> operations could have failed if they tried to write in the very second that
> there was actually no table named "a", but since the operation was
> transactional, the worst scenario was that if the operation should have
> failed, the client application would just inform of the error and ask the
> user for a retry. No big deal.
>
> Now, this table, that is totally unattached to the system in every way (no
> one references this table, its like a dumpster for old records), is not
> begin accessed by no other thread in the system, so an ALTER table on it, to
> turn a char(255) to char(250), should have no effect on the system.
>
> So, with this in mind, I tried the ALTER TABLE this time on the "a_full"
> (totally unrelated) table.
> The system went non-responsive again, and this time it had nothing to do
> with threads waiting for the alter table to complete. The pgAdmin GUI went
> non-responsive, as well as the application's server GUI, whose threads kept
> working on the background, but starting to take more and more time for every
> clients request (up to 25 seconds, which are just ridiculous and completely
> unacceptable in normal conditions).

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.