Re: pg_dump (COPY) hanging intermittently

On Thu, Jun 27, 2019 at 2:34 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ben Snaidero <bensnaidero(at)geotab(dot)com> writes:
> > I am running into a strange issue with Postgres 10 when using pg_dump
> with
> > the directory format and jobs parameter set it intermittently hangs.
> Seems
> > to occur less frequently the lower I set the jobs parameter but does
> happen
> > eventually even when set to 2 (could not reproduce when jobs=1). I've
> > tested with Postgres 11 and no matter how high I set the jobs parameter
> it
> > never hangs (tested all the way up to jobs=8). Also with Postgres 10 and
> > other formats I don't run into the issue. Here are my server specs and
> > software versions (OS and Postgres)
>
> FWIW, it looks like the hang is on the client side (i.e., pg_dump itself).
> The one active server process is just trying to send COPY data and is
> waiting for the client to take it. So I'm wondering about deadlocks
> inside parallel pg_dump.
>
> This doesn't present any obvious answer, because there is not much
> difference between the parallel pg_dump logic in 10 and 11. But
> maybe you could get stack trace(s) out of the stuck pg_dump job on 10?
> (Keep in mind that unlike the backend, the pg_dump process will have
> multiple threads in this situation.)
>
> Another thing you could do to triangulate is to verify that v11 pg_dump
> is okay working against the v10 server. Unfortunately the other
> combination can't be tested, but this'd be enough to exonerate the v10
> server side.
>
> regards, tom lane
>

Thanks for the suggestion Tom. I've verified that this looks to be a v10
server side issue as using the Postgres11 pg_dump.exe against the v10
server I still encounter the same hanging issue.

Here are the stack traces from each of the threads of the pg_dump.exe.

ntoskrnl.exe!KiSwapContext+0x76
ntoskrnl.exe!KiSwapThread+0x17d
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!KiSchedulerApc+0x231
ntoskrnl.exe!KiDeliverApc+0x22a
ntoskrnl.exe!KiSwapThread+0x364
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!NtWaitForSingleObject+0xf8
ntoskrnl.exe!KiSystemServiceCopyEnd+0x13
ntdll.dll!NtWaitForSingleObject+0x14
mswsock.dll!SockWaitForSingleObject+0x133
mswsock.dll!WSPSelect+0x7e4
WS2_32.dll!select+0x1d3
pg_dump.exe+0x64ae
pg_dump.exe+0x65bd
pg_dump.exe+0x6228
pg_dump.exe+0x509f
pg_dump.exe+0x980b
pg_dump.exe+0x117cc
pg_dump.exe+0x6f3f
pg_dump.exe+0x30c22
pg_dump.exe+0x3cb77
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21

----------------------------------------------------------------------

ntoskrnl.exe!KiSwapContext+0x76
ntoskrnl.exe!KiSwapThread+0x17d
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!KiSchedulerApc+0x231
ntoskrnl.exe!KiDeliverApc+0x22a
ntoskrnl.exe!KiSwapThread+0x364
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!NtWaitForSingleObject+0xf8
ntoskrnl.exe!KiSystemServiceCopyEnd+0x13
ntdll.dll!NtWaitForSingleObject+0x14
mswsock.dll!SockWaitForSingleObject+0x133
mswsock.dll!WSPRecv+0x5d2
WS2_32.dll!recv+0x16c
pg_dump.exe+0x6746
pg_dump.exe+0x61ab
pg_dump.exe+0x5e57
pg_dump.exe+0x6a22
MSVCR120.dll!_callthreadstartex+0x17
MSVCR120.dll!_threadstartex+0x88
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21

----------------------------------------------------------------------

ntoskrnl.exe!KiSwapContext+0x76
ntoskrnl.exe!KiSwapThread+0x17d
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!KiSchedulerApc+0x231
ntoskrnl.exe!KiDeliverApc+0x22a
ntoskrnl.exe!KiSwapThread+0x364
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!NtWaitForSingleObject+0xf8
ntoskrnl.exe!KiSystemServiceCopyEnd+0x13
ntdll.dll!NtWaitForSingleObject+0x14
mswsock.dll!SockWaitForSingleObject+0x133
mswsock.dll!WSPRecv+0x5d2
WS2_32.dll!recv+0x16c
pg_dump.exe+0x6746
pg_dump.exe+0x61ab
pg_dump.exe+0x5e57
pg_dump.exe+0x6a22
MSVCR120.dll!_callthreadstartex+0x17
MSVCR120.dll!_threadstartex+0x88
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21

----------------------------------------------------------------------

ntoskrnl.exe!KiSwapContext+0x76
ntoskrnl.exe!KiSwapThread+0x17d
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!KiSchedulerApc+0x231
ntoskrnl.exe!KiDeliverApc+0x22a
ntoskrnl.exe!KiSwapThread+0x364
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!NtWaitForSingleObject+0xf8
ntoskrnl.exe!KiSystemServiceCopyEnd+0x13
ntdll.dll!NtWaitForSingleObject+0x14
mswsock.dll!SockWaitForSingleObject+0x133
mswsock.dll!WSPSelect+0x7e4
WS2_32.dll!select+0x1d3
LIBPQ.dll!PQenv2encoding+0x1301
LIBPQ.dll!PQenv2encoding+0x11a3
LIBPQ.dll!PQenv2encoding+0xc7a
LIBPQ.dll!PQprintTuples+0x34b0
pg_dump.exe+0x313df
pg_dump.exe+0x98a0
pg_dump.exe+0x11e89
pg_dump.exe+0x6100
pg_dump.exe+0x5e57
pg_dump.exe+0x6a22
MSVCR120.dll!_callthreadstartex+0x17
MSVCR120.dll!_threadstartex+0x88
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21

----------------------------------------------------------------------

ntoskrnl.exe!KiSwapContext+0x76
ntoskrnl.exe!KiSwapThread+0x17d
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!KiSchedulerApc+0x231
ntoskrnl.exe!KiDeliverApc+0x22a
ntoskrnl.exe!KiSwapThread+0x364
ntoskrnl.exe!KiCommitThreadWait+0x14f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!NtWaitForSingleObject+0xf8
ntoskrnl.exe!KiSystemServiceCopyEnd+0x13
ntdll.dll!NtWaitForSingleObject+0x14
mswsock.dll!SockWaitForSingleObject+0x133
mswsock.dll!WSPRecv+0x5d2
WS2_32.dll!recv+0x16c
pg_dump.exe+0x6746
pg_dump.exe+0x61ab
pg_dump.exe+0x5e57
pg_dump.exe+0x6a22
MSVCR120.dll!_callthreadstartex+0x17
MSVCR120.dll!_threadstartex+0x88
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21

Do these stack traces shed help at all?