postgresql interrupt unkillable queries using ProcessInterrupts() via gdb

gistfile1.txt

# reference https://www.cybertec-postgresql.com/en/cancel-hanging-postgresql-query/

postgres@db1:~$ pg_config --libdir
/opt/postgresql/postgresql-14.3/lib
postgres@db1:~$ cat loop.c
#include "postgres.h"
#include "fmgr.h"
 
#include <unistd.h>
 
PG_MODULE_MAGIC;
 
PG_FUNCTION_INFO_V1(loop);
 
Datum loop(PG_FUNCTION_ARGS)
{
    /* an endless loop */
    while(1)
        sleep(2);
}
postgres@db1:~$ gcc -I /opt/postgresql/postgresql-14.3/include/server -fPIC -shared -o loop.so loop.c 
postgres@db1:~$ cp loop.so /opt/postgresql/postgresql-14.3/lib/
postgres@db1:~$ psql <<EOF
> CREATE OR REPLACE FUNCTION loop() RETURNS void
> LANGUAGE c AS 'loop';
> EOF
CREATE FUNCTION

#inifinite loop, could not be interrupted using pg_cancel_backend()
postgres@db1:~$ psql -c 'select loop();' &
[1] 61898

postgres@db1:~$ psql -c "select query,pid from pg_stat_activity where query like '%loop%';"
                               query                               |  pid  
-------------------------------------------------------------------+-------
 select loop();                                                    | 61899
 select query,pid from pg_stat_activity where query like '%loop%'; | 61902
(2 rows)

postgres@db1:~$ psql -c "select pg_cancel_backend(61899);" &
[2] 61905
postgres@db1:~$  pg_cancel_backend 
-------------------
 t
(1 row)


[2]+  Done                    psql -c "select pg_cancel_backend(61899);"

# although pg_cancel_backend returned true, postmaster could not processinterrupts and and hence it is still running/hanging
postgres@db1:~$ psql -c "select query,pid from pg_stat_activity where query like '%loop%';"
                               query                               |  pid  
-------------------------------------------------------------------+-------
 select loop();                                                    | 61899
 select query,pid from pg_stat_activity where query like '%loop%'; | 61908
(2 rows)

###############
#from another terminal
postgres@db1:~$ sudo gdb /opt/postgresql/postgresql-14.3/bin/postgres -p 61899
[sudo] password for postgres: 
GNU gdb (Ubuntu 9.2-0ubuntu1~20.04.1) 9.2
Copyright (C) 2020 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Type "show copying" and "show warranty" for details.
This GDB was configured as "aarch64-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:
    <http://www.gnu.org/software/gdb/documentation/>.

For help, type "help".
Type "apropos word" to search for commands related to "word"...
Reading symbols from /opt/postgresql/postgresql-14.3/bin/postgres...
Attaching to program: /opt/postgresql/postgresql-14.3/bin/postgres, process 61899
Reading symbols from /lib/aarch64-linux-gnu/libssl.so.1.1...
(No debugging symbols found in /lib/aarch64-linux-gnu/libssl.so.1.1)
Reading symbols from /lib/aarch64-linux-gnu/libcrypto.so.1.1...
(No debugging symbols found in /lib/aarch64-linux-gnu/libcrypto.so.1.1)
Reading symbols from /lib/aarch64-linux-gnu/libpthread.so.0...
Reading symbols from /usr/lib/debug/.build-id/9e/e85bfaafff46d2fae9ac6c7ff06a68e9080cc8.debug...
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/aarch64-linux-gnu/libthread_db.so.1".
Reading symbols from /lib/aarch64-linux-gnu/librt.so.1...
Reading symbols from /usr/lib/debug/.build-id/98/f3772542f72acef62cf88faa9eb9a2e8326405.debug...
Reading symbols from /lib/aarch64-linux-gnu/libdl.so.2...
Reading symbols from /usr/lib/debug/.build-id/e6/bacb58f1852797463ae6de925c26bd634492bf.debug...
Reading symbols from /lib/aarch64-linux-gnu/libm.so.6...
Reading symbols from /usr/lib/debug/.build-id/ef/5f79ec54a7fe0dbb8f0cf2a360c360978c8d39.debug...
Reading symbols from /lib/aarch64-linux-gnu/libc.so.6...
Reading symbols from /usr/lib/debug/.build-id/b7/b30ba2c0f618615a8d32d86813718bb2fdb567.debug...
Reading symbols from /lib/ld-linux-aarch64.so.1...
Reading symbols from /usr/lib/debug/.build-id/b6/7648e0ab1141e21a749711cde7a7b250b9e1d3.debug...
Reading symbols from /lib/aarch64-linux-gnu/libnss_files.so.2...
Reading symbols from /usr/lib/debug/.build-id/64/717a87a20c4917fa46775553215497233379a6.debug...
Reading symbols from /opt/postgresql/postgresql-14.3/lib/loop.so...
(No debugging symbols found in /opt/postgresql/postgresql-14.3/lib/loop.so)
0x0000ffffa47e4540 in __GI___clock_nanosleep (clock_id=<optimized out>, clock_id@entry=0, flags=flags@entry=0, 
    req=req@entry=0xffffebf55e08, rem=rem@entry=0xffffebf55e08) at ../sysdeps/unix/sysv/linux/clock_nanosleep.c:78
78	../sysdeps/unix/sysv/linux/clock_nanosleep.c: No such file or directory.

# invoke ProcessInterrupts() manually to trigger pg_cancel_backend completition
(gdb) print ProcessInterrupts()
[Inferior 1 (process 61899) exited normally]
The program being debugged exited while in a function called from GDB.
Evaluation of the expression containing the function
(ProcessInterrupts) will be abandoned.
(gdb) quit

######################
on first terminal, you can see query getting cancelled

postgres@db1:~$ ERROR:  canceling statement due to user request

[1]+  Exit 1                  psql -c 'select loop();'
postgres@db1:~$ psql -c "select query,pid from pg_stat_activity where query like '%loop%';"
                               query                               |  pid  
-------------------------------------------------------------------+-------
 select query,pid from pg_stat_activity where query like '%loop%'; | 61922
(1 row)