Re: psql query gets stuck indefinitely - Mailing list pgsql-general

Hi Craig

Thanks for your reply . But unfortunately I dont have that process running right now. I have already killed that process . But I have seen this problem sometimes on my setup.
It generally happens when the remote system is going slow for some reason (CPU utilization high etc.)  . But whatever is the reason , I would assume that the query should return with some error or so
in case the system, the query is running on , is rebooted .  But  it doesn't return and remain stuck. Moreover, the same query sometimes hangs even if it is run on local postgres  database so I dont think
network issues have any role in that . Please help.

Thanks....

Regards
Tamanna

On Tue, Nov 29, 2011 at 7:58 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 11/28/2011 05:30 PM, tamanna madaan wrote:

Hi All
I have postgres installed in cluster setup. My system has a script
which executes the below query on remote system in cluster.
psql -t -q -Uslon -h<hostip> -d<dbname> -c"select 1;"
But somehow this query got stuck. It didnt return even after the remote
system( on which this query was supposed to execute) is rebooted . What
could be the reason ??

I relised just after sending my last message:

You should use ps to find out what exactly psql is doing and which system call it's blocked in in the kernel (if it's waiting on a syscall). As you didn't mention your OS I'll assume you're on Linux, where you'd use:

 ps -C psql -o wchan:80=

or

 ps -p 1234 -o wchan:80=

... where "1234" is the pid of the stuck psql process. In a psql waiting for command line input I see it blocked in the kernel routine "n_tty_read" for example.


If you really want to know what it's doing you can also attach gdb and get a backtrace to see what code it's paused in inside psql:

gdb -q -p 1234 <<__END__
bt
q
__END__

If you get a message about "missing debuginfos", lots of lines reading "no debugging symbols found" or lots of lines ending in "?? ()" then you need to install debug symbols. How to do that depends on your OS/distro so I won't go into that; it's documented on the PostgreSQL wiki under "how to get a stack trace" but you probably won't want to bother if this is just for curiosity's sake.

You're looking for output that looks like:

#1  0x000000369d22a131 in rl_getc () from /lib64/libreadline.so.6
#2  0x000000369d22a8e9 in rl_read_key () from /lib64/libreadline.so.6
#3  0x000000369d215b11 in readline_internal_char () from /lib64/libreadline.so.6
#4  0x000000369d216065 in readline () from /lib64/libreadline.so.6

... etc ...


--
Craig Ringer

--
Tamanna Madaan | Associate Consultant | GlobalLogic Inc.
Leaders in Software R&D Services
ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA

Office: +0-120-406-2000 x 2971

www.globallogic.com