Обсуждение: parallel query hangs - impossible to cancel

Hi all,

We've seen several cases of hanging queries lately which were impossible to cancel (even with pg_terminate_backend). The only fix we've found was to restart the whole database or kill -9 the process, as the hanging query was preventing vacuum to clean up properly. Both of these lead to all clients disconnecting, which is quite a painful measure.

Unfortunately it occurs rarely and we haven't found a way to reproduce it in a dev environment. However, I do have a stack trace of when it occurred once.

The stack trace can be found below. In pg_stat_statements the function would show up as wait_event=DynamicSharedMemoryControlLock and waiting for a LWLock. We're running Postgres 11.2. So far I've just disabled parallel plans altogether, which will hopefully prevent this from happening, but this is of course not an ideal solution. 

Any ideas what may cause this?

-Floris

Hi

seems similar to my bugreport 15585 [1] and few others threads.

This bug should be already fixed in stable branch and will be shipped in next minor release (11.3 in may). Thread with
patchwas here: https://www.postgresql.org/message-id/flat/20190207014719.GJ29720%40telsasoft.com
 

[1] https://www.postgresql.org/message-id/flat/15585-324ff6a93a18da46%40postgresql.org

regards, Sergei

Thank you! I indeed saw another thread that looked like it, but I wasn't aware that the problem had been fixed already.
Thisindeed seems similar to my stack trace. 

-Floris

________________________________________
From: Sergei Kornilov <sk@zsrv.org>
Sent: Monday, March 25, 2019 1:57 PM
To: Floris Van Nee; pgsql-bugs@postgresql.org
Subject: Re: parallel query hangs - impossible to cancel [External]

Hi

seems similar to my bugreport 15585 [1] and few others threads.

This bug should be already fixed in stable branch and will be shipped in next minor release (11.3 in may). Thread with
patchwas here: https://www.postgresql.org/message-id/flat/20190207014719.GJ29720%40telsasoft.com 

[1] https://www.postgresql.org/message-id/flat/15585-324ff6a93a18da46%40postgresql.org

regards, Sergei

On Tue, Mar 26, 2019 at 2:17 AM Floris Van Nee <florisvannee@optiver.com> wrote:
> Thank you! I indeed saw another thread that looked like it, but I wasn't aware that the problem had been fixed
already.This indeed seems similar to my stack trace.
 

Hi Floris,

I should probably highlight something that is buried in one of those
long threads Sergei posted: until the fix is released, the simplest
workaround is to disable parallel query with SET
max_parallel_workers_per_gather = 0 if you're affected by the bug.  In
theory you could also make it vanishingly rare by finding a way to
call srandom(getpid()) in every forked process with an extension.
It's a rare timing bug, but some workloads seem to be more prone to
it, so unfortunately it went undetected for a long time.

-- 
Thomas Munro
https://enterprisedb.com