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

On Wed, Jan 13, 2010 at 10:54 AM, Eduardo Piombino <drakorg(at)gmail(dot)com> wrote:
>
>> 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.

If you rename a table that WAS being referenced by other threads, then
it might still be being accessed or waited on etc by those threads, as
their transaction would have started earlier.

The only way you can guarantee it's not being reference in some way is
to create it fresh and new as I suggested and test on that. Until
then, your guarantee is based on a belief, not verifiable fact. I too
tend to believe this is an IO problem btw, but claiming that it can't
be a problem with some locks without looking at pg_locks at least, is
a bit premature.