Re: Query hangs when getting too complex...

Tom Lane wrote:
>
> Paulo Jan <admin@mail.ddnet.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.