BUG #16145: Not able to terminate active session

On Tue, Dec 03, 2019 at 01:18:50PM +0000, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 16145
Logged by: MOHAN KUMAR DORAIRAJ
Email address: mohankumar.dorairaj@globalfoundries.com
PostgreSQL version: 11.2
Operating system: Red Hat Enterprise Linux Server release 7.6 (Maipo
Description:

Not able to terminate active session even though used below commands

postgres=# SELECT pg_cancel_backend(16647);
pg_cancel_backend
-------------------
t
(1 row)

postgres=# SELECT pg_terminate_backend(16647);
pg_terminate_backend
----------------------
t
(1 row)

postgres=# select pid,application_name FROM pg_stat_activity where
application_name IS NOT NULL AND state = 'active';
pid | application_name
-------+------------------------
16647 | PostgreSQL JDBC Driver
19879 | psql

So what is happening in 16647? What query is it running? Can you attach
gdb to it and show us the backtrace?

It's probably running some CPU-intensive piece of code, not checking the
flags set by signal handlers, or something like that.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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@serv ~]# gdb postgres 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/&gt;...
postgres: No such file or directory.
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

Regards

Mohan

6670 4751

On Tue, Dec 3, 2019 at 10:11 PM Tomas Vondra <tomas.vondra@2ndquadrant.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@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

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/&gt;.
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.vondra@2ndquadrant.com>
wrote:

On Wed, Dec 04, 2019 at 12:26:14AM +0800, MOHAN KUMAR DORAIRAJ wrote:

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/&gt;.
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".
...
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

This is still just the initial info gdb shows after attaching to the
process. You need to do "bt" or "backtrace" before quitting.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

If you'll forgive a random question from an onlooker: Why did the functions return True if the process is still ongoing?

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL
"Each of these functions returns true if successful and false otherwise."

Is the definition of successful "the signal was sent" and not "the signal was actually received and did something"?

David Raymond <David.Raymond@tomtom.com> writes:

Is the definition of successful "the signal was sent" and not "the signal was actually received and did something"?

Yes. There's no way for the signal sender to know whether the receiver
reacted.

regards, tom lane

Hi Mohan,
Please follow below steps to get stack trace(call stack/back trace) of
postgres process.

Step1)
Fire "ps -aef | grep postgres" and identify the process id or PID of client.

Ex:
[mahendra@localhost bin]$ ps -aef | grep postgres
mahendra 104917 1 0 23:40 ? 00:00:00
/home/mahendra/postgres_base_rp/postgres/inst/bin/postgres -D data
mahendra 104920 104917 0 23:40 ? 00:00:00 postgres: checkpointer
mahendra 104921 104917 0 23:40 ? 00:00:00 postgres: background
writer
mahendra 104922 104917 0 23:40 ? 00:00:00 postgres: walwriter
mahendra 104923 104917 0 23:40 ? 00:00:00 postgres: stats collector

mahendra 104924 104917 0 23:40 ? 00:00:00 postgres: logical
replication launcher
mahendra *105137* 6320 0 23:44 pts/1 00:00:00 ./psql postgres
mahendra 105138 104917 0 23:44 ? 00:00:00 postgres: mahendra
postgres [local] idle
mahendra 105165 6859 0 23:44 pts/2 00:00:00 grep --color=auto postgres
[mahendra@localhost bin]$

Here, my session id is 105137 (pid) means I connected as a client from
server with 105137 PID.

Step2)
Now attach your PID using gdb.

Ex:
[mahendra@localhost bin]$ gdb attach 105137
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-110.el7
-------------some more gdb info will be printed

Step3) Now fire "bt" or "backtrace" command.

Note: Don't exit the gdb from step2.

81 T_PSEUDO (SYSCALL_SYMBOL, SYSCALL_NAME, SYSCALL_NARGS)
Missing separate debuginfos, use: debuginfo-install
keyutils-libs-1.5.8-3.el7.x86_64 krb5-libs-1.15.1-19.el7.x86_64
libcom_err-1.42.9-12.el7_5.x86_64 libselinux-2.5-12.el7.x86_64
ncurses-libs-5.9-14.20130511.el7_4.x86_64 openssl-libs-1.0.2k-12.el7.x86_64
pcre-8.32-17.el7.x86_64 readline-6.2-10.el7.x86_64 zlib-1.2.7-17.el7.x86_64
(gdb)
(gdb*) bt*
#0 0x00007ffa46e517e0 in __read_nocancel () at
../sysdeps/unix/syscall-template.S:81
#1 0x00007ffa46c27097 in rl_getc () from /lib64/libreadline.so.6
#2 0x00007ffa46c278e7 in rl_read_key () from /lib64/libreadline.so.6
#3 0x00007ffa46c12b2f in readline_internal_char () from
/lib64/libreadline.so.6
#4 0x00007ffa46c131e5 in readline () from /lib64/libreadline.so.6
#5 0x00000000004381a6 in gets_interactive (prompt=0x6f6a60
<destination.7461> "postgres=# ", query_buf=0x1a37b00) at input.c:92
#6 0x000000000043a9a0 in MainLoop (source=0x7ffa466ed640 <_IO_2_1_stdin_>)
at mainloop.c:169
#7 0x000000000044ac5d in main (argc=2, argv=0x7ffefd61e818) at
startup.c:441
(gdb)

Thanks and Regards
Mahendra Thalor
EnterpriseDB: http://www.enterprisedb.com

On Tue, 3 Dec 2019 at 22:08, Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:

Dear Mahendra & Tomas

Please find the updated output of bdf command. Please let me know if you
need additional log.

(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 ()
#14 0x000000000060c2fa in ExecScan ()
#15 0x000000000061d90d in ExecHashJoin ()
#16 0x00000000006280af in ExecNestLoop ()
#17 0x000000000060401a in standard_ExecutorRun ()
#18 0x000000000074615b in PortalRunSelect ()
#19 0x000000000074751f in PortalRun ()
#20 0x0000000000744e6d in PostgresMain ()
#21 0x00000000004803e9 in ServerLoop ()
#22 0x00000000006d7b99 in PostmasterMain ()
#23 0x000000000048124f in main ()

Regards

Mohan

6670 4751

On Wed, Dec 4, 2019 at 2:22 AM Mahendra Singh <mahi6run@gmail.com> wrote:

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.

regards, tom lane

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@sss.pgh.pa.us> wrote:

MOHAN KUMAR DORAIRAJ <mohankumar.dorairaj@globalfoundries.com> writes:

Is there any other alternative option available.?

Well, why is it that your session is sitting waiting for input from
the Oracle server? That doesn't seem like a condition that ought
to be persistent, most of the time. Perhaps redesigning your
queries against that foreign server would help.

Also, I do recommend asking the Oracle FDW authors about this.
It's possible that adding some CHECK_FOR_INTERRUPT calls in that
FDW would ameliorate things. (I don't know where to file issues
about oracle_fdw, but it's not this list.)

regards, tom lane

On Wed, Dec 04, 2019 at 08:34:34AM -0500, Tom Lane wrote:

MOHAN KUMAR DORAIRAJ <mohankumar.dorairaj@globalfoundries.com> writes:

Is there any other alternative option available.?

Well, why is it that your session is sitting waiting for input from
the Oracle server? That doesn't seem like a condition that ought
to be persistent, most of the time. Perhaps redesigning your
queries against that foreign server would help.

Yeah, having some CHECK_FOR_INTERRUPT calls in the code might help
(there's a single place with this macro, but it only happens after an
error). The question is whether the OCIStmtExecute actually returns from
time to time, of it if gets stuck.

Also, I do recommend asking the Oracle FDW authors about this.
It's possible that adding some CHECK_FOR_INTERRUPT calls in that
FDW would ameliorate things. (I don't know where to file issues
about oracle_fdw, but it's not this list.)

I think the right place to file the issue is here:

https://github.com/laurenz/oracle_fdw

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Dear Team
After terminating the session at oracle database, automatically postgres
session has been terminated.

Regards

Mohan

6670 4751

On Wed, Dec 4, 2019 at 9:55 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote: