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