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

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