Re: SOCK_wait_for_ready function call caused a query to get stuck

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(at)gmail(dot)com]
Sent: Fri 3/18/2011 8:56 PM
To: tamanna madaan
Cc: pgsql-general(at)postgresql(dot)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(dot)madan(at)globallogic(dot)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