GreenPlum日常整理

发表于 更新于 分类于

新知识结构，记录下日常使用

GP的函数创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- GP实现REGEXP_LIKE函数
CREATE OR REPLACE FUNCTION regexp_like(str character, reg character)
  RETURNS boolean AS
$BODY$
declare
v_match  text;
begin
select regexp_matches(str,reg) into v_match;
if v_match is not NULL then
return true;
else
return false;
end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE;

GP解锁操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# 查看segment锁情况
select gp_execution_dbid(),pid,relation::regclass,locktype,mode,granted
from gp_dist_random('pg_locks');

# 查看具体什么语句持有的锁
select gp_execution_dbid() dbid,procpid,current_query
from gp_dist_random('pg_stat_activity')  
where procpid in (
    select pid 
    from gp_dist_random('pg_locks')
    where locktype='relation' and mode='ExclusiveLock'
);

# 连接相关segment
PGOPTIONS="-c gp_session_role=utility" psql -h localhost -p 5432 -d gpdb

# 在segment查询相关锁情况
select
    w.current_query as waiting_query,
    w.procpid as w_pid,
    w.usename as w_user,
    l.current_query as locking_query,
    l.procpid as l_pid,
    l.usename as l_user,
    t.schemaname || '.' || t.relname as tablename
from pg_stat_activity w
join pg_locks l1 
on w.procpid = l1.pid 
and not l1.granted
join pg_locks l2 
on l1.relation = l2.relation 
and l2.granted
join pg_stat_activity l 
on l2.pid = l.procpid
join pg_stat_user_tables t 
on l1.relation = t.relid
where w.waiting;

# 处理持有锁的pid
select pg_terminate_backend('procpid');

# GP查看锁
select pid,rolname,rsqname,granted,current_query,datname
from pg_roles,gp_toolkit.gp_resqueue_status,pg_locks,pg_stat_activity
WHERE pg_roles.rolresqueue=pg_locks.objid
AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid
AND pg_stat_activity.procpid=pg_locks.pid;
 
# GP解除锁定
pg_cancel_backend(#pid)

GP用户权限

1
2
3
4
5
6
7
8
9
10
11
create role user_name with login;
alter role user_name with password 'password';

# schema使用权限
grant usage on schema schema_name to user_name;

# 查询权限赋权
grant select ON ALL tables in schema schema_name to user_name;

# 赋权后对新建表无权限需要执行一下操作
alter default privileges for user schema_owner in schema schema_name grant select on tables to user_name;