Re: LONG delete with LOTS of FK's

On 05/09/2013 04:22 PM, Larry Rosenman wrote:

> It's been on various tables, and they are all bigints.

Hey, ya never know. I've gotten tripped up similarly. In that case, I
defer to Tom's suggestion. If there are any '<IDLE> in transaction'
statements, or your long delete is marked as waiting in
pg_stat_activity, something is up. I've also found this query extremely
helpful in tracking down things like this:

SELECT DISTINCT l1.pid AS blocker_pid, a.current_query AS blocker_query,
        a.usename AS blocker_user, a.client_addr AS blocker_client,
        l2.pid AS blocked_pid, a2.current_query AS blocked_query,
        a2.usename AS blocked_user, a2.client_addr AS blocked_client
   FROM pg_locks l1
   JOIN pg_stat_activity() a on (a.procpid = l1.pid)
   JOIN pg_locks l2 ON (l1.relation = l2.relation AND l1.pid != l2.pid)
   JOIN pg_stat_activity() a2 on (a2.procpid = l2.pid)
  WHERE l1.granted
    AND NOT l2.granted;


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email