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

Dear Tomas

Please find the info

-bash-4.2$ gdb -p 16647
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-115.el7
Copyright (C) 2013 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 "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 16647
Reading symbols from /usr/pgsql-11/bin/postgres...Reading symbols from
/usr/pgsql-11/bin/postgres...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols
found)...done.
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libxml2.so.2...Reading symbols from
/lib64/libxml2.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libxml2.so.2
Reading symbols from /lib64/libpam.so.0...Reading symbols from
/lib64/libpam.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libpam.so.0
Reading symbols from /lib64/libssl.so.10...Reading symbols from
/lib64/libssl.so.10...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libssl.so.10
Reading symbols from /lib64/libcrypto.so.10...Reading symbols from
/lib64/libcrypto.so.10...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypto.so.10
Reading symbols from /lib64/libgssapi_krb5.so.2...Reading symbols from
/lib64/libgssapi_krb5.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libgssapi_krb5.so.2
Reading symbols from /lib64/librt.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libldap-2.4.so.2...Reading symbols from
/lib64/libldap-2.4.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libldap-2.4.so.2
Reading symbols from /lib64/libicui18n.so.50...Reading symbols from
/lib64/libicui18n.so.50...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libicui18n.so.50
Reading symbols from /lib64/libicuuc.so.50...Reading symbols from
/lib64/libicuuc.so.50...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libicuuc.so.50
Reading symbols from /lib64/libsystemd.so.0...Reading symbols from
/lib64/libsystemd.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libsystemd.so.0
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libz.so.1...Reading symbols from
/lib64/libz.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libz.so.1
Reading symbols from /lib64/liblzma.so.5...Reading symbols from
/lib64/liblzma.so.5...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/liblzma.so.5
Reading symbols from /lib64/libaudit.so.1...Reading symbols from
/lib64/libaudit.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libaudit.so.1
Reading symbols from /lib64/libkrb5.so.3...Reading symbols from
/lib64/libkrb5.so.3...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libkrb5.so.3
Reading symbols from /lib64/libcom_err.so.2...Reading symbols from
/lib64/libcom_err.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcom_err.so.2
Reading symbols from /lib64/libk5crypto.so.3...Reading symbols from
/lib64/libk5crypto.so.3...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libk5crypto.so.3
Reading symbols from /lib64/libkrb5support.so.0...Reading symbols from
/lib64/libkrb5support.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libkrb5support.so.0
Reading symbols from /lib64/libkeyutils.so.1...Reading symbols from
/lib64/libkeyutils.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libkeyutils.so.1
Reading symbols from /lib64/libresolv.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libresolv.so.2
Reading symbols from /lib64/liblber-2.4.so.2...Reading symbols from
/lib64/liblber-2.4.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/liblber-2.4.so.2
Reading symbols from /lib64/libsasl2.so.3...Reading symbols from
/lib64/libsasl2.so.3...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libsasl2.so.3
Reading symbols from /lib64/libssl3.so...Reading symbols from
/lib64/libssl3.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libssl3.so
Reading symbols from /lib64/libsmime3.so...Reading symbols from
/lib64/libsmime3.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libsmime3.so
Reading symbols from /lib64/libnss3.so...Reading symbols from
/lib64/libnss3.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss3.so
Reading symbols from /lib64/libnssutil3.so...Reading symbols from
/lib64/libnssutil3.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libnssutil3.so
Reading symbols from /lib64/libplds4.so...Reading symbols from
/lib64/libplds4.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libplds4.so
Reading symbols from /lib64/libplc4.so...Reading symbols from
/lib64/libplc4.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libplc4.so
Reading symbols from /lib64/libnspr4.so...Reading symbols from
/lib64/libnspr4.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libnspr4.so
Reading symbols from /lib64/libstdc++.so.6...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libstdc++.so.6
Reading symbols from /lib64/libgcc_s.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libgcc_s.so.1
Reading symbols from /lib64/libicudata.so.50...Reading symbols from
/lib64/libicudata.so.50...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libicudata.so.50
Reading symbols from /lib64/libcap.so.2...Reading symbols from
/lib64/libcap.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcap.so.2
Reading symbols from /lib64/libselinux.so.1...Reading symbols from
/lib64/libselinux.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libselinux.so.1
Reading symbols from /lib64/liblz4.so.1...Reading symbols from
/lib64/liblz4.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/liblz4.so.1
Reading symbols from /lib64/libgcrypt.so.11...Reading symbols from
/lib64/libgcrypt.so.11...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libgcrypt.so.11
Reading symbols from /lib64/libgpg-error.so.0...Reading symbols from
/lib64/libgpg-error.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libgpg-error.so.0
Reading symbols from /lib64/libdw.so.1...Reading symbols from
/lib64/libdw.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libdw.so.1
Reading symbols from /lib64/libcap-ng.so.0...Reading symbols from
/lib64/libcap-ng.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcap-ng.so.0
Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libattr.so.1...Reading symbols from
/lib64/libattr.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libattr.so.1
Reading symbols from /lib64/libpcre.so.1...Reading symbols from
/lib64/libpcre.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libpcre.so.1
Reading symbols from /lib64/libelf.so.1...Reading symbols from
/lib64/libelf.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libelf.so.1
Reading symbols from /lib64/libbz2.so.1...Reading symbols from
/lib64/libbz2.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libbz2.so.1
Reading symbols from /lib64/libfreebl3.so...Reading symbols from
/lib64/libfreebl3.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libfreebl3.so
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /usr/pgsql-11/lib/plpgsql.so...Reading symbols from
/usr/pgsql-11/lib/plpgsql.so...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /usr/pgsql-11/lib/plpgsql.so
Reading symbols from /usr/pgsql-11/lib/oracle_fdw.so...done.
Loaded symbols for /usr/pgsql-11/lib/oracle_fdw.so
Reading symbols from /db/oraclnt/12.2.0/lib/libclntsh.so.12.1...(no
debugging symbols found)...done.
Loaded symbols for /db/oraclnt/12.2.0/lib/libclntsh.so.12.1
Reading symbols from /db/oraclnt/12.2.0/lib/libmql1.so...(no debugging
symbols found)...done.
Loaded symbols for /db/oraclnt/12.2.0/lib/libmql1.so
Reading symbols from /db/oraclnt/12.2.0/lib/libipc1.so...(no debugging
symbols found)...done.
Loaded symbols for /db/oraclnt/12.2.0/lib/libipc1.so
Reading symbols from /db/oraclnt/12.2.0/lib/libnnz12.so...(no debugging
symbols found)...done.
Loaded symbols for /db/oraclnt/12.2.0/lib/libnnz12.so
Reading symbols from /db/oraclnt/12.2.0/lib/libons.so...(no debugging
symbols found)...done.
Loaded symbols for /db/oraclnt/12.2.0/lib/libons.so
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libaio.so.1...Reading symbols from
/lib64/libaio.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libaio.so.1
Reading symbols from /db/oraclnt/12.2.0/lib/libclntshcore.so.12.1...(no
debugging symbols found)...done.
Loaded symbols for /db/oraclnt/12.2.0/lib/libclntshcore.so.12.1
Reading symbols from /usr/lib64/libnuma.so.1...Reading symbols from
/usr/lib64/libnuma.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libnuma.so.1
Reading symbols from /db/oraclnt/12.2.0/lib/libnque12.so...(no debugging
symbols found)...done.
Loaded symbols for /db/oraclnt/12.2.0/lib/libnque12.so
0x00007f5503e826e0 in __read_nocancel () from /lib64/libpthread.so.0
Missing separate debuginfos, use: debuginfo-install
postgresql11-server-11.2-2PGDG.rhel7.x86_64
(gdb) quit
A debugging session is active.

Inferior 1 [process 16647] will be detached.

Quit anyway? (y or n) y
Detaching from program: /usr/pgsql-11/bin/postgres, process 16647

On Tue, Dec 3, 2019 at 11:55 PM Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> On Tue, Dec 03, 2019 at 11:12:43PM +0800, MOHAN KUMAR DORAIRAJ wrote:
> >Dear Tomas
> >
> >Please find the gdb output and Query. Query uses oracle_fwd to fetch data
> >from Oracle database (f7.l_view_b2b is available in oracle db).
> >
> >CPU and memory consumption is not high in this scenario. Kindly help us.
> >
> >SELECT *
> >FROM f7.l_view_b2b v,
> >g_map p
> >WHERE v.cmer_id =p.pname
> >AND p.e_flag = 'Y'
> >AND NOT EXISTS
> >(SELECT 1
> >FROM ap.clot_bk1
> >WHERE ld = v.ld
> >AND cr_ind = '7'
> >AND eventtype = ''COMPLETE'
> >AND eventtime = v.claim_time
> >)
> >
> >
> >[root(at)serv ~]# gdb postgres 16647
>
> That's not how you attach GDB to a running process. You need to do
>
> gdb -p 16647
>
> >GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-115.el7
> >Copyright (C) 2013 Free Software Foundation, Inc.
> >License GPLv3+: GNU GPL version 3 or later <
> http://gnu.org/licenses/gpl.html
> > ...
> >
> >debugging symbols found)...done.
> >Loaded symbols for /db/oraclnt/12.2.0/lib/libclntshcore.so.12.1
> >Reading symbols from /usr/lib64/libnuma.so.1...Reading symbols from
> >/usr/lib64/libnuma.so.1...(no debugging symbols found)...done.
> >(no debugging symbols found)...done.
> >Loaded symbols for /usr/lib64/libnuma.so.1
> >Reading symbols from /db/oraclnt/12.2.0/lib/libnque12.so...(no debugging
> >symbols found)...done.
> >Loaded symbols for /db/oraclnt/12.2.0/lib/libnque12.so
> >0x00007f5503e826e0 in __read_nocancel () from /lib64/libpthread.so.0
> >Missing separate debuginfos, use: debuginfo-install
> >postgresql11-server-11.2-2PGDG.rhel7.x86_64
> >
>
> This is not a backtrace, this is just the info GDB shows after start.
>
> But __read_nocancel suggests the process might be stuck in an I/O
> request, likely a network call to the Oracle. So maybe it's waiting for
> data from oracle_fdw, which means it can't cancel the query.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>