The following bug has been logged on the website:
Bug reference: 16145 Logged by: MOHAN KUMAR DORAIRAJ Email address: [hidden email] 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 |
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: [hidden email] >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/>... 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 On Tue, Dec 3, 2019 at 10:11 PM Tomas Vondra <[hidden email]> wrote: On Tue, Dec 03, 2019 at 01:18:50PM +0000, PG Bug reporting form 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/>. 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 <[hidden email]> wrote: On Tue, Dec 03, 2019 at 11:12:43PM +0800, MOHAN KUMAR DORAIRAJ 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 |
In reply to this post by Tomas Vondra-4
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 |
David Raymond <[hidden email]> 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 |
In reply to this post by Tomas Vondra-4
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 <[hidden email]> wrote: On Wed, Dec 04, 2019 at 12:26:14AM +0800, MOHAN KUMAR DORAIRAJ 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 () On Wed, Dec 4, 2019 at 2:22 AM Mahendra Singh <[hidden email]> wrote:
|
MOHAN KUMAR DORAIRAJ <[hidden email]> 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.? On Wed, Dec 4, 2019 at 9:10 PM Tom Lane <[hidden email]> wrote: MOHAN KUMAR DORAIRAJ <[hidden email]> writes: |
MOHAN KUMAR DORAIRAJ <[hidden email]> 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 <[hidden email]> 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 |
Free forum by Nabble | Edit this page |