Greenplum问题杂记

获取最新文档：http://www.200yi.com

一、单机模式问题

1. “select * from pg_resqueue_status; ” crash

crash重现方法：

[gpadmin@bogon ~]$ gpstart -a -m
[gpadmin@bogon ~]$ PGOPTIONS='-c gp_session_role=utility' psql -d postgres
[gpadmin@bogon gpdb-6X_STABLE]$ PGOPTIONS='-c gp_session_role=utility' psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# select * from pg_resqueue_status;
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

原因：

单机模式下，Gp_role == GP_ROLE_UTILITY，InitResScheduler()函数不为0，导致ResQueueHash没有初始化：

void
ResManagerShmemInit(void)
{
	if (IsResQueueEnabled() && Gp_role == GP_ROLE_DISPATCH)
	{
		InitResScheduler();
		InitResPortalIncrementHash();
	}
	else if (IsResGroupEnabled() && !IsUnderPostmaster)
	{
		ResGroupControlInit();
	}
}

在查询pg_resqueue_status视图时，访问ResQueueHash空指针：

/**
 * This copies out the current state of resource queues.
 */
static void
BuildQueueStatusContext(QueueStatusContext *fctx)
{
……
	/* Initialize for a sequential scan of the resource queue hash. */
	hash_seq_init(&status, ResQueueHash);
	num_calls = hash_get_num_entries(ResQueueHash);
	Assert(num_calls == ResScheduler->num_queues);

2. “gp_toolkit.gp_param_setting” crash

crash重现方法：

[gpadmin@bogon ~]$ gpstart -a -m
[gpadmin@bogon ~]$ PGOPTIONS='-c gp_session_role=utility' psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# select * FROM gp_toolkit.gp_param_setting('max_connections');
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

原因：未知。

二、未分类问题

1. 移除角色superuser属性

NOSUPERUSER可以移除超级用户权限。

tpch1s=# \du
                                       List of roles
 Role name |                              Attributes                        | Member of 
-----------+----------------------------------------------------------------+-----------
 gpadmin   | Superuser, Create role, Create DB, Ext http Table, Replication | {}
 yz        | Superuser, Create role, Create DB, Replication                 | {gpadmin}
 
tpch1s=# ALTER ROLE yz with NOSUPERUSER;
tpch1s=# \du
                                       List of roles
 Role name |                              Attributes                        | Member of 
-----------+----------------------------------------------------------------+-----------
 gpadmin   | Superuser, Create role, Create DB, Ext http Table, Replication | {}
 yz        | Create role, Create DB, Replication                            | {gpadmin}

2. ERROR: deadlock detected, locking against self

资源队列设置memory_limit后，执行SQL，出现以下问题：

tpch1s=>  select * from nation;
ERROR:  deadlock detected, locking against self

解决方法是，调小statement_mem配置参数值，使：
$$statement\_mem<=资源队列的memory\_limit$$

tpch1s=> set statement_mem='10MB';

参考：Conflict with Resource Queue memory_limit and statement_mem results in “ERROR: deadlock detected, locking against self”

3. 资源队列不起作用

使用queue（资源队列）管理资源时，发现资源管理队列不起作用。以下视图，rsqcountvalue和rsqmemoryvalue始终为0：

tpch1s=#  select * from gp_toolkit.gp_resqueue_status;
 queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders 
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+------------
    6055 | pg_default |            20 |             0 |           -1 |            0 |    1.04858e+07 |              0 |          0 |          0

经查，是因为“超级用户不受资源队列限制”：

Roles with the SUPERUSER attribute are exempt from resource queue limits. Superuser queries always run immediately regardless of limits imposed by their assigned resource queue.

4. 查询用户与资源组/资源队列之间的关系

用户对应的资源队列：

SELECT rolname, rsqname FROM pg_roles,
gp_toolkit.gp_resqueue_status
WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

用户对应的资源组：

SELECT rolname, rsgname FROM pg_roles, pg_resgroup
WHERE pg_roles.rolresgroup=pg_resgroup.oid;