GBase 8a集群通过SQL获得集群锁showlock和容错showfailover信息

虽然GBase 8a集群当前版本获得集群锁信息和容错信息，必须通过gcadmin的命令行执行才能获得，但由于支持python udf，所以可以通过这个特性来用SQL获得这些。

参考

南大通用GBase8a MPP Cluster查看集群锁 showlock

南大通用GBase 8a查看和清理故障恢复状态Failover的方法

南大通用GBase 8a通过python UDF从客户端执行SQL语句 

集群锁showlock的python UDF

drop function if exists showlock;
create function showlock()
returns varchar
$$
try:
    import commands
    output = commands.getoutput("gcadmin showlock")
except:
    return None
return str(output)

$$ language plpythonu;

执行效果

gbase> select showlock();
+-------------------+
| showlock()        |
+-------------------+
|  +=================================================================+
 |                          GCLUSTER LOCK                          |
 +=================================================================+
 +-------------+----------+-------------+--------------+------+----+
 |  Lock name  |  owner   |   content   | create time  |locked|type|
 +-------------+----------+-------------+--------------+------+----+
 |gc-event-lock|10.0.2.201|global master|20250113120320| TRUE | E  |
 +-------------+----------+-------------+--------------+------+----+
 Total : 1 |
+--------------------+
1 row in set (Elapsed: 00:00:00.63)

容错信息showfailover的python udf样例

drop function if exists showfailover;
create function showfailover()
returns varchar
$$
try:
    import commands
    output = commands.getoutput("gcadmin showfailover")
except:
    return None
return str(output)

$$ language plpythonu;

执行效果

gbase> drop function if exists showfailover;
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.00)

gbase> create function showfailover()
    -> returns varchar
    -> $$
    -> try:
    ->     import commands
    ->     output = commands.getoutput("gcadmin showfailover")
    -> except:
    ->     return None
    -> return str(output)
    ->
    -> $$ language plpythonu;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select showfailover();
+--------------------------------------------------------------+
| showfailover()                                               |
+--------------------------------------------------------------+
|
gcadmin showfailover: no gcluster failover information now
 |
+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.63)

gbase>

总结

通过python udf，在不方便命令行访问的情况下，可以提高数据库的易用性和安全性。

输出内容可以增加 f参数实现xml格式，详情看gcadmin --help的介绍。