Re: BUG #16145: Not able to terminate active session

Thanks Tom for the update.

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.

Is there any other alternative option available.?

Regards

Mohan

6670 4751

On Wed, Dec 4, 2019 at 9:10 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> MOHAN KUMAR DORAIRAJ <mohankumar(dot)dorairaj(at)globalfoundries(dot)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.
>
> regards, tom lane
>