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

BUG #16145: Not able to terminate active session

From

PG Bug reporting form

Date:

03 December 2019, 16:18:50

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

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

From

Tomas Vondra

Date:

03 December 2019, 17:11:47

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

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

From

MOHAN KUMAR DORAIRAJ

Date:

03 December 2019, 18:12:43

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/>...
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 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

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

From

Tomas Vondra

Date:

03 December 2019, 18:54:58

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

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

From

MOHAN KUMAR DORAIRAJ

Date:

03 December 2019, 19:26:14

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.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

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

From

Tomas Vondra

Date:

03 December 2019, 19:38:18

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/>.
>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

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

From

David Raymond

Date:

03 December 2019, 20:00:42

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"?


> 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.


> 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

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

From

Tom Lane

Date:

03 December 2019, 20:09:00

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

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

From

Mahendra Singh

Date:

03 December 2019, 21:22:39

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:

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/>.
>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

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

From

MOHAN KUMAR DORAIRAJ

Date:

04 December 2019, 06:34:51

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:

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:
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/>.
>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

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

From

Tom Lane

Date:

04 December 2019, 16:10:12

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

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

From

MOHAN KUMAR DORAIRAJ

Date:

04 December 2019, 16:22:03

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:
> (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

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

From

Tom Lane

Date:

04 December 2019, 16:34:34

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

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

From

Tomas Vondra

Date:

04 December 2019, 16:55:36

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

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

From

MOHAN KUMAR DORAIRAJ

Date:

06 December 2019, 06:34:23

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:

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