Re: psql query gets stuck indefinitely

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