Re: Query, usually running <300ms, sometimes hangs for hours

"Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de> writes:
> I know, ~3,000 elements in the IN clause are quite a lot, but we haven't
> seen any problems with this before and I don't think that this is
> actually causing it - this same type of query has been running unchanged
> for more than six months now. I think that the "kill [pid]" not having
> any effect is quite suspicious. So I tried a backtrace with gdb; I
> haven't got full debugging support in my installation, but I get the
> functions nevertheless, which is better than nothing, I suppose:

> (gdb) bt
> #0 0x00007f58e3394505 in send () from /lib/libc.so.6
> #1 0x0000000000546249 in internal_flush ()
> #2 0x000000000054635d in internal_putbytes ()
> #3 0x00000000005463bc in pq_putmessage ()
> #4 0x00000000005479c4 in pq_endmessage ()
> #5 0x0000000000452eb0 in printtup ()
> #6 0x0000000000526408 in ExecutorRun ()
> #7 0x00000000005bfe50 in PortalRunSelect ()
> #8 0x00000000005c14b9 in PortalRun ()
> #9 0x00000000005be025 in PostgresMain ()
> #10 0x0000000000591ed2 in ServerLoop ()
> #11 0x0000000000592bb4 in PostmasterMain ()
> #12 0x0000000000548268 in main ()

> I haven't got much experience reading stack traces, but I suspect that
> what this means is that PostgreSQL actually has done its job with the
> query and is now hanging in sending the result back to the client.

Yes, that reading is correct: this stack trace shows it's blocked trying
to send query results back to the client. So you need to figure out why
the client is failing to accept data.

regards, tom lane