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(at)optionshouse(dot)com

______________________________________________

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

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general