Re: Query hangs when getting too complex...

Tom Lane wrote:
>
> Paulo Jan <admin(at)mail(dot)ddnet(dot)es> writes:
> > I have here a query that hangs everytime I try to execute it, even
> > though IMO it isn't *that* complicated (nor is the database *that* big).
>
> Very bizarre. Can you attach to the backend process with gdb and get a
> backtrace to see where it's hung up?
>

Uh... I don't have much experience using gdb (actually, I just started
learning <g>), but anyway:

1) I started psql and then looked at the PID of the "postgres" backend
process.
2) I typed "gdb postgres (PID)":

Attaching to program: /usr/bin/postgres, Pid 30492
Reading symbols from /usr/lib/libssl.so.0...(no debugging symbols
found)...
done.
Reading symbols from /usr/lib/libcrypto.so.0...(no debugging symbols
found)...
done.
Reading symbols from /usr/lib/libz.so.1...(no debugging symbols
found)...done.
Reading symbols from /lib/libcrypt.so.1...done.
Reading symbols from /lib/libresolv.so.2...done.
Reading symbols from /lib/libnsl.so.1...done.
Reading symbols from /lib/libdl.so.2...done.
Reading symbols from /lib/libm.so.6...done.
Reading symbols from /usr/lib/libreadline.so.3...done.
Reading symbols from /lib/libc.so.6...done.
Reading symbols from /lib/ld-linux.so.2...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /usr/lib/gconv/ISO8859-1.so...done.
---Type <return> to continue, or q <return> to quit---
Reading symbols from /lib/libnss_files.so.2...done.
0x40276ba2 in __libc_recv () from /lib/libc.so.6

3) I typed the query in psql and started it. psql hangs...
4) ...and in the meantime, I type "bt" in the gdb window:

(gdb) bt
#0 0x40276ba2 in __libc_recv () from /lib/libc.so.6
#1 0x80c7c5a in StreamClose ()
#2 0x80c7d57 in pq_getbytes ()
#3 0x80fc54c in HandleFunctionRequest ()
#4 0x80fc5d5 in HandleFunctionRequest ()
#5 0x80fd9de in PostgresMain ()
#6 0x80e8035 in PostmasterMain ()
#7 0x80e7c1c in PostmasterMain ()
#8 0x80e6e77 in PostmasterMain ()
#9 0x80e6826 in PostmasterMain ()
#10 0x80c89fe in main ()
#11 0x401dc9cb in __libc_start_main (main=0x80c8890 <main>, argc=3,
argv=0xbffffbc4, init=0x8065a78 <_init>, fini=0x8145f2c <_fini>,
rtld_fini=0x4000aea0 <_dl_fini>, stack_end=0xbffffbbc)
at ../sysdeps/generic/libc-start.c:92

Then, just in case, I decided to do a backtrace of "psql", so I exited
gdb and did a "gdb psql (PID of psql)", and then a "bt":

Attaching to program: /usr/bin/psql, Pid 30491
Reading symbols from /usr/lib/libpq.so.2...(no debugging symbols
found)...done.
Reading symbols from /usr/lib/libssl.so.0...(no debugging symbols
found)...
done.
Reading symbols from /usr/lib/libcrypto.so.0...(no debugging symbols
found)...
done.
Reading symbols from /usr/lib/libz.so.1...(no debugging symbols
found)...done.
Reading symbols from /lib/libcrypt.so.1...done.
Reading symbols from /lib/libresolv.so.2...done.
Reading symbols from /lib/libnsl.so.1...done.
Reading symbols from /lib/libdl.so.2...done.
Reading symbols from /lib/libm.so.6...done.
Reading symbols from /usr/lib/libreadline.so.3...done.
Reading symbols from /lib/libc.so.6...done.
Reading symbols from /lib/ld-linux.so.2...done.
Reading symbols from /lib/libtermcap.so.2...done.
---Type <return> to continue, or q <return> to quit---
Reading symbols from /lib/libnss_files.so.2...done.
0x4028215e in __select () from /lib/libc.so.6
(gdb) bt
#0 0x4028215e in __select () from /lib/libc.so.6
#1 0x4002487c in ?? () from /usr/lib/libpq.so.2
#2 0x4001c2da in PQgetResult () from /usr/lib/libpq.so.2
#3 0x4001c47f in PQexec () from /usr/lib/libpq.so.2
#4 0x804d79d in strcpy () at ../sysdeps/generic/strcpy.c:30
#5 0x804f041 in strcpy () at ../sysdeps/generic/strcpy.c:30
#6 0x80502b6 in strcpy () at ../sysdeps/generic/strcpy.c:30
#7 0x401ed9cb in __libc_start_main (main=0x804fcc0 <strcpy+22720>,
argc=2,
argv=0xbffffb74, init=0x8049be0 <_init>, fini=0x805797c <_fini>,
rtld_fini=0x4000aea0 <_dl_fini>, stack_end=0xbffffb6c)
at ../sysdeps/generic/libc-start.c:92

Did I do it right? Is there anything informative here?

Paulo Jan.
DDnet.