2024年1月24日发(作者：)

greenplum处理锁等待修改greenplum的视图，可能是版本差异 procpid, current_querypg_stat_activity b这表中的两个字段需要修改create view v_locks_monitor as

with

t_wait as

(

select ,pe,se,on,,,d,d,

,id,,ctionid,sionid,riter,_segment_id,

procpid,_id,g_reason, current_query,_start,_start,e,e,_addr,_port,ation_name

from pg_locks a,pg_stat_activity b where sionid=_id and not d

),

t_run as

(

select ,pe,se,on,,,d,d,

,id,,ctionid,sionid,riter,_segment_id,

procpid,_id,g_reason, current_query,_start,_start,e,e,_addr,_port,ation_name

from pg_locks a,pg_stat_activity b where sionid=_id and d

),

t_overlap as

(

select r.* from t_wait w join t_run r on

(

pe is not distinct from pe and

se is not distinct from se and

on is not distinct from on and

is not distinct from and

is not distinct from and

ctionid is not distinct from ctionid and

d is not distinct from d and

is not distinct from and

id is not distinct from id and

sionid <> sionid

)

),

t_unionall as

(

select r.* from t_overlap r

union all

select w.* from t_wait w

)

select locktype,datname,relation::regclass,page,tuple,textin(xidout(transactionid)),classid::regclass,objid,objsubid,

string_agg(

'Gp_Segment_Id: '||case when gp_segment_id is null then 'NULL' else gp_segment_id::text end||chr(10)||

'MppIsWriter: '||case when mppiswriter is null then 'NULL' when mppiswriter is true then 'TRUE' else 'FALSE' end||chr(10)||

'MppSessionId: '||case when mppsessionid is null then 'NULL' else mppsessionid::text end||chr(10)||

'ProcPid: '||case when procpid is null then 'NULL' else procpid::text end||chr(10)||

'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||

'Lock_Granted: '||case when granted is null then 'NULL' when granted is true then 'TRUE' else 'FALSE' end||' , Mode: '||case when mode is null then 'NULL' else mode::text en'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_E'SQL (Current SQL in Transaction): '||chr(10)||

case when current_query is null then 'NULL' else current_query::text end,

chr(10)||'--------'||chr(10)

order by

( case mode

when 'INVALID' then 0

when 'AccessShareLock' then 1

when 'RowShareLock' then 2

when 'RowExclusiveLock' then 3

when 'ShareUpdateExclusiveLock' then 4

when 'ShareLock' then 5

when 'ShareRowExclusiveLock' then 6

when 'ExclusiveLock' then 7