Support SHOW PROCESSLIST/KILL-like feature for PostgreSQL/openGauss databases

[strongduanmu]

Feature Request

Is your feature request related to a problem?

#20299
#19606
#9568

Describe the feature you would like.

In version 5.2.0, ShardingSphere supports MySQL SHOW PROCESSLIST and KILL statements, which makes it more convenient for users to manage SQL.

The following figure shows the execution process of the current SHOW PROCESSLIST statement. It can be seen that it is a general process that can support different types of databases.

So we need to investigate whether PostgreSQL and openGauss databases have statements similar to MySQL SHOW PROCESSLIST/KILL, and then adapt these statements for PostgreSQL and openGauss databases.

• investigate whether the PostgreSQL database has a statement similar to MySQL SHOW PROCESSLIST/KILL and support it @ICannerxy ——https://stackoverflow.com/questions/35319597/how-to-stop-kill-a-query-in-postgresql
• investigate whether the openGauss database has a statement similar to MySQL SHOW PROCESSLIST/KILL and support it

[ICannerxy]

I want to do the first requirement .(Adaptation PostgreSQL database)

[strongduanmu]

Welcome @ICannerxy, I will assign this issue to you.

[ICannerxy]

Postgresql supports functions similar to show processlist and kill <processID>.
Use SELECT * FROM pg_stat_activity WHERE state = 'active'; to query running SQL, and use SELECT pg_cancel_backend(<pid of the process>) to kill the running SQL.

[ICannerxy]

1. create table t1(id integer, name text);
2. insert into t1 values(1, 'a'); insert into t1 values(2, 'b');

> `51293` is pid

[strongduanmu]

@ICannerxy Can you try with sharding feature, the result looks like PostgreSQL original result.