The words Under construction in black text on a yellow background with diagonal black stipes surrounding it
I'm in the process of moving my site. It's still a work in progress. Please excuse the mess and broken links.

Find A Deadlock Blocking Query In Redshift

TODO: Pull subtitle into page object
Code
find-a-deadlock-blocking-query-in-redshift

find-a-deadlock-blocking-query-in-redshift-tmp

If you have something deadlock blocking a table in Redshift, you can run this query to help find out what's happening:

Code
select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
    from svv_transactions a 
    left join (select pid,relation,granted from pg_locks group by 1,2,3) b 
    on a.relation=b.relation and a.granted='f' and b.granted='t' 
    left join (select * from stv_tbl_perm where slice=0) c 
    on a.relation=c.id 
    left join pg_class d on a.relation=d.oid
    where  a.relation is not null;

Kill it with:

Code
select pg_terminate_backend(323);

Debugging Stuff

I'm moving stuff around right now. All this below is helping me figure out where to put stuff

        -- title

Find A Deadlock Blocking Query In Redshift

NOTE: There are two of theses files, need to 
figure out which is which:

-- code

find-a-deadlock-blocking-query-in-redshift

find-a-deadlock-blocking-query-in-redshift-tmp

-- p

If you have something deadlock blocking a table in 
Redshift, you can run this query to help find out 
what's happening:

-- code

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
    from svv_transactions a 
    left join (select pid,relation,granted from pg_locks group by 1,2,3) b 
    on a.relation=b.relation and a.granted='f' and b.granted='t' 
    left join (select * from stv_tbl_perm where slice=0) c 
    on a.relation=c.id 
    left join pg_class d on a.relation=d.oid
    where  a.relation is not null;

-- p

Kill it with:

-- code

select pg_terminate_backend(323);


-- metadata
-- date: 2021-02-09 15:54:59
-- id: 20enuj0h
-- status: published
-- type: redshift