SOCK_wait_for_ready function call caused a query to get stuck

On Fri, Mar 18, 2011 at 4:31 AM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

 I am using postgres-8.4.0 and psqlodbc-08.03.0400-1 and
unixODBC-2.2.14-000.01 driver to connect
to the database. One of the queries I executed from my application have got
stuck for an
indefinite amount of time causing my application to hang. So I cored the
application. The
core file gives a backtrace which shows it got stuck while waiting for a
socket to get
ready as follows :

(gdb) bt
#0  0x00007f1c3e5ed366 in poll () from /lib64/libc.so.6
#1  0x00007f1c3a82d0a5 in SOCK_wait_for_ready (sock=0x7f1be67ff720,
output=0, retry_count=1) at socket.c:531
#2  0x00007f1c3a82d8c3 in SOCK_get_next_byte (self=0x7f1be67ff720, peek=0)
at socket.c:940
#3  0x00007f1c3a82db92 in SOCK_get_id (self=0x7f1be67ff720) at socket.c:696
#4  0x00007f1c3a8090ca in CC_send_query_append (self=0x7f1be68bf430,
query=<value optimized out>, qi=0x0, flag=<value optimized out>, stmt=0x0,
    appendq=<value optimized out>) at connection.c:2498
#5  0x00007f1c3a818ae5 in PGAPI_Transact (henv=0x0, hdbc=0x7f1be68bf430,
fType=0) at execute.c:1143
#6  0x00007f1c3a8424ec in SQLEndTran (HandleType=<value optimized out>,
Handle=0x7f1be68bf430, CompletionType=-1) at odbcapi30.c:178
#7  0x00007f1c3f62fa2b in SQLEndTran (handle_type=<value optimized out>,
handle=0x7f1beff16b90, completion_type=0) at SQLEndTran.c:360
One other thread of the same process was also stuck :

(gdb) bt
#0  0x00007f1c3e5ed366 in poll () from /lib64/libc.so.6
#1  0x00007f1c3a82d0a5 in SOCK_wait_for_ready (sock=0x2bcde60, output=0,
retry_count=1) at socket.c:531
#2  0x00007f1c3a82d8c3 in SOCK_get_next_byte (self=0x2bcde60, peek=0) at
socket.c:940
#3  0x00007f1c3a82db92 in SOCK_get_id (self=0x2bcde60) at socket.c:696
#4  0x00007f1c3a8090ca in CC_send_query_append (self=0x2bd13a0, query=<value
optimized out>, qi=0x0, flag=<value optimized out>, stmt=0x7f1bf766c380,
    appendq=<value optimized out>) at connection.c:2498
#5  0x00007f1c3a836c94 in SC_execute (self=0x7f1bf766c380) at
statement.c:1879
#6  0x00007f1c3a81907e in Exec_with_parameters_resolved
(stmt=0x7f1bf766c380, exec_end=0x7f1c2c59e4c0) at execute.c:386
#7  0x00007f1c3a81a600 in PGAPI_Execute (hstmt=0x7f1bf766c380, flag=<value
optimized out>) at execute.c:1070
#8  0x00007f1c3a83fd82 in SQLExecute (StatementHandle=0x7f1bf766c380) at
odbcapi.c:374
#9  0x00007f1c3f630c77 in SQLExecute (statement_handle=0x7f1be4b069e0) at
SQLExecute.c:283
I had the same issue while using postgres-8.1.2 and was advised to upgrade
postgres.
But upgrading the postgres version didn't resolve the issue  .
There doesn't seem to be any locking issue either .

Can anyone please shed some light on this issue .

In separate session with psql, you query pg_stat_activity and look for
waiting queries? (need to eliminate lock issue). Are you opening
multiple sessions through the odbc driver? Is your application
multi-threaded?

merlin

Thanks for your reply Merlin .

If I am getting you correctly, you mean to say that I should check for waiting queries in pg_stat_activity table
while my application is hung at SOCK_wait_for_ready function call . Right ??

Yes I am opening multiple seesions with ODBC driver and my application is multi threaded .

Tamanna

________________________________

From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Fri 3/18/2011 8:56 PM
To: tamanna madaan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SOCK_wait_for_ready function call caused a query to get stuck

On Fri, Mar 18, 2011 at 4:31 AM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

I am using postgres-8.4.0 and psqlodbc-08.03.0400-1 and
unixODBC-2.2.14-000.01 driver to connect
to the database. One of the queries I executed from my application have got
stuck for an
indefinite amount of time causing my application to hang. So I cored the
application. The
core file gives a backtrace which shows it got stuck while waiting for a
socket to get
ready as follows :

(gdb) bt
#0 0x00007f1c3e5ed366 in poll () from /lib64/libc.so.6
#1 0x00007f1c3a82d0a5 in SOCK_wait_for_ready (sock=0x7f1be67ff720,
output=0, retry_count=1) at socket.c:531
#2 0x00007f1c3a82d8c3 in SOCK_get_next_byte (self=0x7f1be67ff720, peek=0)
at socket.c:940
#3 0x00007f1c3a82db92 in SOCK_get_id (self=0x7f1be67ff720) at socket.c:696
#4 0x00007f1c3a8090ca in CC_send_query_append (self=0x7f1be68bf430,
query=<value optimized out>, qi=0x0, flag=<value optimized out>, stmt=0x0,
appendq=<value optimized out>) at connection.c:2498
#5 0x00007f1c3a818ae5 in PGAPI_Transact (henv=0x0, hdbc=0x7f1be68bf430,
fType=0) at execute.c:1143
#6 0x00007f1c3a8424ec in SQLEndTran (HandleType=<value optimized out>,
Handle=0x7f1be68bf430, CompletionType=-1) at odbcapi30.c:178
#7 0x00007f1c3f62fa2b in SQLEndTran (handle_type=<value optimized out>,
handle=0x7f1beff16b90, completion_type=0) at SQLEndTran.c:360
One other thread of the same process was also stuck :

(gdb) bt
#0 0x00007f1c3e5ed366 in poll () from /lib64/libc.so.6
#1 0x00007f1c3a82d0a5 in SOCK_wait_for_ready (sock=0x2bcde60, output=0,
retry_count=1) at socket.c:531
#2 0x00007f1c3a82d8c3 in SOCK_get_next_byte (self=0x2bcde60, peek=0) at
socket.c:940
#3 0x00007f1c3a82db92 in SOCK_get_id (self=0x2bcde60) at socket.c:696
#4 0x00007f1c3a8090ca in CC_send_query_append (self=0x2bd13a0, query=<value
optimized out>, qi=0x0, flag=<value optimized out>, stmt=0x7f1bf766c380,
appendq=<value optimized out>) at connection.c:2498
#5 0x00007f1c3a836c94 in SC_execute (self=0x7f1bf766c380) at
statement.c:1879
#6 0x00007f1c3a81907e in Exec_with_parameters_resolved
(stmt=0x7f1bf766c380, exec_end=0x7f1c2c59e4c0) at execute.c:386
#7 0x00007f1c3a81a600 in PGAPI_Execute (hstmt=0x7f1bf766c380, flag=<value
optimized out>) at execute.c:1070
#8 0x00007f1c3a83fd82 in SQLExecute (StatementHandle=0x7f1bf766c380) at
odbcapi.c:374
#9 0x00007f1c3f630c77 in SQLExecute (statement_handle=0x7f1be4b069e0) at
SQLExecute.c:283
I had the same issue while using postgres-8.1.2 and was advised to upgrade
postgres.
But upgrading the postgres version didn't resolve the issue .
There doesn't seem to be any locking issue either .

Can anyone please shed some light on this issue .

In separate session with psql, you query pg_stat_activity and look for
waiting queries? (need to eliminate lock issue). Are you opening
multiple sessions through the odbc driver? Is your application
multi-threaded?

merlin

On Fri, Mar 18, 2011 at 11:39 AM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Thanks for your reply Merlin .

If I am getting you correctly, you mean to say that I should check for
waiting queries in pg_stat_activity table
while my application is hung at SOCK_wait_for_ready function call  . Right
??

correct. let's hope the problem is here (look for waiting = true).
Also take note of any sessions idle in transaction if there are any.

Yes I am opening multiple seesions with ODBC driver  and my application is
multi threaded .

The worst and possibly the most likely case is that you have some sort
of threading/synchronization problem in your application or possibly
the odbc driver itself.

merlin