Evern pg_terminate_backend (force method) also did not work. This has become a nightmare for us and every time we are restarting the database to fix this issue.
MOHAN KUMAR DORAIRAJ <mohankumar.dorairaj@globalfoundries.com> writes: > (gdb) bt > #0 0x00007f5503e826e0 in __read_nocancel () from /lib64/libpthread.so.0 > #1 0x00007f54eb392490 in snttread () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #2 0x00007f54eb391420 in nttfprd () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #3 0x00007f54eb387be0 in nsbasic_brc () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #4 0x00007f54eb37e654 in nioqrc () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #5 0x00007f54eb3987f9 in ttcdrv () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #6 0x00007f54eb382809 in nioqwa () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #7 0x00007f54eb36ce90 in upirtrc () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #8 0x00007f54eb378f86 in kpurcsc () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #9 0x00007f54eb3710c9 in kpuexec () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #10 0x00007f54eb36c639 in OCIStmtExecute () from > /db/oraclnt/12.2.0/lib/libclntsh.so.12.1 > #11 0x00007f54ec10eb32 in oracleExecuteQuery (session=0x1a16838, > oraTable=0x1a0e558, paramList=<optimized out>) at oracle_utils.c:1964 > #12 0x00007f54ec105940 in oracleIterateForeignScan (node=<optimized out>) > at oracle_fdw.c:1432 > #13 0x000000000062e58b in ForeignNext () > ...
OK, so that's pretty much what we were afraid of: it's stuck in some I/O in the Oracle FDW, which means that said I/O code isn't aware of the conventions for query termination in Postgres. There isn't anything that PG as a whole can do about this. Perhaps talking to the Oracle FDW authors would yield something useful --- but they may not be able to fix it either, because it looks like this is way way down inside an Oracle-supplied library.