On 2/28/2008 9:17 PM, Craig A. James wrote:
> Jan Wieck wrote:
>> On 2/25/2008 9:59 AM, Craig A. James wrote:
>>> I sent the attached email ony to Jan by mistake, here it is, I hope 
>>> Jan or somebody has an idea what's going on.
>> 
>> What I still don't understand is what that stored procedure moveSet() is 
>> waiting for. Are there any pg_locks entries with granted='f' at all?
> 
> Forget the whole question, the mystery is solved.
> 
> A rogue process with a bug in it had created five million tables (that's right, five MILLION tables) in the database.  The process was creating about 50,000 tables per day, and had been running for almost three months.

Thanks for sharing this. Good to know that it after all wasn't caused by 
Slony.

However, this should raise another question on your side. The time 
consuming part inside of either lockSet() or moveSet() is a loop over a 
join between Slony's sl_table and Postgres' pg_class. The join condition 
between the two is sl_table.tab_reloid -> pg_class.oid. Also involved in 
the query is pg_namespace, but I think we can ignore it in this case. 
Your pg_class had grown into a 5M row table, which suggests that the 
join should have led to an outer loop over sl_table performing index 
scans via pg_class_oid_index into pg_class. Is it possible that your 
vacuum strategy misses to vacuum the system catalog?


> 
> Now we can't even do a single "pg_dump --table" because getting an exclusive lock seems to take forever and a day.  So we can't dump it, and we can't use Slony to move the node.  Fortunately, we already had the most important tables replicated via Slony, which was still working well, so we were able to just drop Slony replication and transfer the whole load to the backup database.  We'll simply discard the corrupted database and recreate it with Slony.

An ANALYZE of pg_class might get you some relief on that.


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin